2 Replies Latest reply on May 17, 2012 11:44 AM by 887796-Oracle

    Performance problem using aggregative rule


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

      <query id="RDBQuery">
           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'

      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?