db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Zonsheine <Dav...@enigma.com>
Subject Interesting query
Date Sun, 19 Jun 2005 13:59:25 GMT
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 <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