Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 79632 invoked from network); 19 Jun 2005 14:00:05 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 19 Jun 2005 14:00:05 -0000 Received: (qmail 46906 invoked by uid 500); 19 Jun 2005 14:00:04 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 46856 invoked by uid 500); 19 Jun 2005 14:00:04 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 46841 invoked by uid 99); 19 Jun 2005 14:00:04 -0000 X-ASF-Spam-Status: No, hits=0.8 required=10.0 tests=HTML_20_30,HTML_MESSAGE,MIME_BOUND_NEXTPART,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 19 Jun 2005 06:59:59 -0700 X-ASF-Spam-Status: No, hits=0.8 required=10.0 tests=HTML_20_30,HTML_MESSAGE,MIME_BOUND_NEXTPART,UPPERCASE_25_50 Received-SPF: neutral (asf.osuosl.org: local policy) Received: from [192.115.235.3] (HELO mailgw.enigma.co.il) (192.115.235.3) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 19 Jun 2005 06:59:33 -0700 Received: from tlvmail.enigma.co.il [10.10.0.3] by mailgw.enigma.co.il - SurfControl E-mail Filter (4.7); Sun, 19 Jun 2005 16:59:35 +0300 Received: by tlvmail.enigma.co.il with Internet Mail Service (5.5.2657.72) id ; Sun, 19 Jun 2005 16:59:32 +0300 Message-ID: <58D63E44CFC6D3119C8C009027E02DFD08C64593@tlvmail.enigma.co.il> From: David Zonsheine To: "'derby-dev@db.apache.org'" Date: Sun, 19 Jun 2005 16:59:25 +0300 Subject: Interesting query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="--=_NextPart_ST_16_59_35_Sunday_June_19_2005_23382" X-Mailer: Internet Mail Service (5.5.2657.72) X-SEF-71F8950-145E-45E1-A736-C766A41A3B82: 1 X-Old-Spam-Check-By: apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ----=_NextPart_ST_16_59_35_Sunday_June_19_2005_23382 Content-Type: text/plain 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. ----=_NextPart_ST_16_59_35_Sunday_June_19_2005_23382 Content-Type: text/html Content-Transfer-Encoding: quoted-printable Interesting query

Hello derby-dev,

Thank you for the wonderful work you're do= ing.
We installed Derby on our application to = be used instead of SQL Server and worked very hard to improve the queries.<= /FONT>
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 cou= ldn't.
Please take a look at it and see if you h= ave any idea. You'll help us very very much if you do.
If by any chance you want the DB dump its= elf I'll be very happy to post it.

The query is:

select distinct TN_RT_TOC.TOC_ID, TN_RT_TO= C.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=3D'|| char(35)|| 'xpointer(id(' || char(36) || TN_RT_TOC.NO= DE_LINK || char(36) || ')),storage=3DREPOSITORY)' AS LINK,
'Exact' AS LINKTYPE,
'Document(docID=3D'|| char(35)|| 'xpointer(id(' || char(36) || TN_RT_TOC.NO= DE_LINK || char(36) || ')),storage=3DREPOSITORY)' 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=3D= supplement.TOC_ID
AND TN_RT_TOC.NODE_ID=3Dsupplement.NODE_ID AND supplement.PROPERTY_VALUE IN= ('JOP', 'Bulletin')
left join TN_RT_TOC_PROPERTI as effectivity on TN_RT_TOC.TOC_ID=3Deffectivi= ty.TOC_ID
AND TN_RT_TOC.NODE_ID=3Deffectivity.NODE_ID , TAIL_NUMBERS
where
TN_RT_TOC.TOC_ID =3D 'B767' AND PARENT_NODE_ID =3D 1
AND (effectivity.property_name=3D'EFF' OR effectivity.TOC_ID is null)
AND TAIL_NUMBERS.TAIL_NUMBER=3D'JA601J' AND TAIL_NUMBERS.ATTR_NAME=3D'FLEET= _EFF'
and TAIL_NUMBERS.ATTR_VALUE>=3DCASE when effectivity.property_value is n= ot NULL
then SUBSTR(effectivity.property_value, 1, 3)
ELSE '0' END AND TAIL_NUMBERS.ATTR_VALUE<=3DCASE when effectivity.proper= ty_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 confidenti= al and intended solely for the use of the individual or entity to whom
t= hey are addressed. If you have received this email in error please notify t= he originator of the message.
Scanning of this message is performed by S= urfControl E-mail Filter software in conjunction with virus detection soft= ware.
----=_NextPart_ST_16_59_35_Sunday_June_19_2005_23382--