This discussion is archived
2 Replies Latest reply: May 17, 2012 4:44 AM by 887796 RSS

Performance problem using aggregative rule

887796 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points