Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Mon, 20 Jun 2005 09:17:12 -0700
From: David Van Couvering <David.Vancouvering@Sun.COM>
Subject: Re: Interesting query
In-reply-to: <58D63E44CFC6D3119C8C009027E02DFD08C64593@tlvmail.enigma.co.il>
To: Derby Development <derby-dev@db.apache.org>
Message-id: <42B6EC08.2000300@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6)
 Gecko/20040113
References: <58D63E44CFC6D3119C8C009027E02DFD08C64593@tlvmail.enigma.co.il>

Hi, David.  Others may want more information, but it seems to me we at 
least need the DDL for the tables involved in this query.

Thanks,

David

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.