Hi again,
I think i found the problem. When i changed query from:
SELECT TABLE_EVENT.ISROOT, TABLE_EVENT.EVENTSTATE, TABLE_EVENT.CLEAREDAT,
TABLE_EVENT.NOTIFIEDAT, TABLE_EVENT.DURATION, TABLE_EVENT.ELEMENTCLASS,
TABLE_EVENT.CLASS, TABLE_EVENT.ELEMENTINSTANCE, TABLE_EVENT.INSTANCE,
VIEW_OCCURRENCE_PRIORITY.ATTRIBUTEINTVALUE, TABLE_EVENT.OCCURRENCEIDENTIFIER
FROM TABLE_EVENT TABLE_EVENT INNER JOIN VIEW_OCCURRENCE_PRIORITY VIEW_OCCURRENCE_PRIORITY
ON
((TABLE_EVENT.OCCURRENCEIDENTIFIER=VIEW_OCCURRENCE_PRIORITY.OCCURRENCEIDENTIFIER) AND
(TABLE_EVENT.INSERTDTS=VIEW_OCCURRENCE_PRIORITY.INSERTDTS)) AND
(TABLE_EVENT.UPDATEDTS=VIEW_OCCURRENCE_PRIORITY.UPDATEDTS)
WHERE TABLE_EVENT.ISROOT=1 AND (TABLE_EVENT.EVENTSTATE=0 AND
(TABLE_EVENT.CLEAREDAT>={ts '1987-05-22 18:14:39'} AND
TABLE_EVENT.CLEAREDAT < {ts '2006-07-30 18:14:40'}) OR TABLE_EVENT.EVENTSTATE=1)
to query:
LE_EVENT.CLEAREDAT,
TABLE_EVENT.NOTIFIEDAT, TABLE_EVENT.DURATION, TABLE_EVENT.ELEMENTCLASS,
TABLE_EVENT.CLASS, TABLE_EVENT.ELEMENTINSTANCE, TABLE_EVENT.INSTANCE,
VIEW_OCCURRENCE_PRIORITY.ATTRIBUTEINTVALUE, TABLE_EVENT.OCCURRENCEIDENTIFIER
FROM TABLE_EVENT TABLE_EVENT, VIEW_OCCURRENCE_PRIORITY WHERE
((TABLE_EVENT.OCCURRENCEIDENTIFIER=VIEW_OCCURRENCE_PRIORITY.OCCURRENCEIDENTIFIER) AND
(TABLE_EVENT.INSERTDTS=VIEW_OCCURRENCE_PRIORITY.INSERTDTS)) AND
(TABLE_EVENT.UPDATEDTS=VIEW_OCCURRENCE_PRIORITY.UPDATEDTS)
AND TABLE_EVENT.ISROOT=1 AND (TABLE_EVENT.EVENTSTATE=0 AND
(TABLE_EVENT.CLEAREDAT>={ts '1987-05-22 18:14:39'} AND
TABLE_EVENT.CLEAREDAT < {ts '2006-07-30 18:14:40'}) OR TABLE_EVENT.EVENTSTATE=1)
It worked. I dont know why inner-join degraded performance that much but after I removed inner-join,
query returned results in 3 sec instead of 25 minutes.
Mustafa
---------------------------------
See the all-new, redesigned Yahoo.com. Check it out.
|