You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-user@db.apache.org by mustafa sener <ms...@yahoo.com> on 2006/08/01 08:24:19 UTC

Re: Query performance of joining table and a view

Hi,
Thanx for the answers. I am guess the slowness is because of my query but when I run same query on any other database they perform much better than derby. I tried indexing but it didnot work. I will try to use analyzing properties. I hope I can find the problem.

Mustafa

 		
---------------------------------
Yahoo! Music Unlimited - Access over 1 million songs.Try it free.

Re: Query performance of joining table and a view

Posted by mustafa sener <ms...@yahoo.com>.

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.