We have a need for aggregative queryin our application. This is the query:
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?