2 Replies Latest reply: May 17, 2012 6:44 AM by 887796-Oracle RSS

    Performance problem using aggregative rule

    887796-Oracle
      Hi,

      We have a need for aggregative queryin our application. This is the query:

      <query id="RDBQuery">
      <![CDATA[
           select max(EventPriority) as EventPriority,
                 xmlelement(NAME "ItemIDs",XMLATTRIBUTES("id" as "compositeKey"), xmlagg(XMLELEMENT(NAME "Item", XMLATTRIBUTES(ItemID as "id")))) as ItemIDs,
                 xmlelement(NAME "ParentEventIDs",XMLATTRIBUTES("key" as "compositeKey"), xmlagg(XMLELEMENT(NAME "Item", XMLATTRIBUTES(EventId as "key")))) as ParentEventIDs,
                 max (PrimaryMimeType) as PrimaryMimeType,
                 max (SecondaryMimeType) as SecondaryMimeType,
                 max (EventType) as EventType                           
           from rdbDistinctView [Rows 5 Slide 5]
           where PrimaryMimeType = 'text'
                and SecondaryMimeType = 'csv'
                and EventType = 'Insert'
      ]]>
      </query>

      But there is a performance isuue if we use large number in the aggregative expression. If we use [Rows 500 Slide 500] it can take the query more then 1 minute to complete. In that time the application is not responding - meaning that there is no output from the EPN until the above query output the event.

      In other words - all other queries (which are not aggregative) work fine as long as the above query has not reached 500 rows, but when 500 rows reached everything pauses until the event of the above query is output.

      If we use [Rows 5 Slide 5] then there is no problem.

      Any suggestions how to resolve this problem? Tune the CEP? Maybe use another approach?

      Thanks