db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mustafa sener <msen...@yahoo.com>
Subject Re: Query performance of joining table and a view
Date Tue, 01 Aug 2006 09:01:48 GMT
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.
Mime
View raw message