db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sunitha Kambhampati <ksunitha...@gmail.com>
Subject Re: Interesting query
Date Sun, 19 Jun 2005 20:58:40 GMT
Great to see you use Derby!

If possible, can you also post the DB dump or maybe atleast the schema 
of tables/indexes used in this query .

Thanks,
Sunitha.
David Zonsheine wrote:

> Hello derby-dev,
>
> Thank you for the wonderful work you're doing.
> We installed Derby on our application to be used instead of SQL Server 
> and worked very hard to improve the queries.
> The process results were quite good even though we should improve them 
> more.
>
> Now, we have 1 long and complicated query that take 0.04 seconds on 
> SQL Server and about 25 seconds on Derby.
> We worked very hard to improve it but couldn't.
> Please take a look at it and see if you have any idea. You'll help us 
> very very much if you do.
> If by any chance you want the DB dump itself I'll be very happy to 
> post it.
>
> The query is:
>
> select distinct TN_RT_TOC.TOC_ID, TN_RT_TOC.NODE_ID, 
> TN_RT_TOC.CHILD_INDEX,TN_RT_TOC.PARENT_NODE_ID,
> TN_RT_TOC.NODE_NAME AS TITLE, TN_RT_TOC.NODE_TYPE AS CLASS,
> 'Document(docID='|| char(35)|| 'xpointer(id(' || char(36) || 
> TN_RT_TOC.NODE_LINK || char(36) || ')),storage=REPOSITORY)' AS LINK,
> 'Exact' AS LINKTYPE,
> 'Document(docID='|| char(35)|| 'xpointer(id(' || char(36) || 
> TN_RT_TOC.NODE_LINK || char(36) || ')),storage=REPOSITORY)' AS SOURCE_ID,
> TN_RT_TOC.NODE_TYPE AS TYPE, TN_RT_TOC.NODE_ID AS PERSISTENTID,
> supplement.PROPERTY_VALUE AS NODE_CLASS
> FROM
> TN_RT_TOC LEFT JOIN TN_RT_TOC_PROPERTI as supplement ON 
> TN_RT_TOC.TOC_ID=supplement.TOC_ID
> AND TN_RT_TOC.NODE_ID=supplement.NODE_ID AND supplement.PROPERTY_VALUE 
> IN ('JOP', 'Bulletin')
> left join TN_RT_TOC_PROPERTI as effectivity on 
> TN_RT_TOC.TOC_ID=effectivity.TOC_ID
> AND TN_RT_TOC.NODE_ID=effectivity.NODE_ID , TAIL_NUMBERS
> where
> TN_RT_TOC.TOC_ID = 'B767' AND PARENT_NODE_ID = 1
> AND (effectivity.property_name='EFF' OR effectivity.TOC_ID is null)
> AND TAIL_NUMBERS.TAIL_NUMBER='JA601J' AND 
> TAIL_NUMBERS.ATTR_NAME='FLEET_EFF'
> and TAIL_NUMBERS.ATTR_VALUE>=CASE when effectivity.property_value is 
> not NULL
> then SUBSTR(effectivity.property_value, 1, 3)
> ELSE '0' END AND TAIL_NUMBERS.ATTR_VALUE<=CASE when 
> effectivity.property_value is not NULL
> then SUBSTR(effectivity.property_value, 4, 3) ELSE '999' END
> order by TN_RT_TOC.CHILD_INDEX
> Thank you very much!
> David Zonsheine
> Projects Development Manager
> _www.enigma.com_
> Cell: 972-54-6658784.
>
>
>
>
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------
> This email and any files transmitted with it are confidential and 
> intended solely for the use of the individual or entity to whom
> they are addressed. If you have received this email in error please 
> notify the originator of the message.
> Scanning of this message is performed by SurfControl E-mail Filter 
> software in conjunction with virus detection software.



Mime
View raw message