db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Van Couvering <David.Vancouver...@Sun.COM>
Subject Re: Interesting query
Date Tue, 21 Jun 2005 16:42:29 GMT
Great, David -- the SQL script you sent is the "DDL" -- Data Definition 
Language.  It's the SQL used to create your tables.  Sorry for using lingo.

SQL query optimization is not my forte -- I'll let someone else who can 
likely help you better decide whether your insert scripts are needed, 
and what else they might need from you.

David

David Zonsheine wrote:

> David Hi,
> 
> I am sending the create script I used. I am not sure what a DDL is. Please
> tell me so I can send it.
> If you need the inserts script I'll send it too.
> 
> Thank you very much,
> David 
> 
> -----Original Message-----
> From: David Van Couvering [mailto:David.Vancouvering@Sun.COM] 
> Sent: Monday, June 20, 2005 7:17 PM
> To: Derby Development
> Subject: Re: Interesting query
> 
> 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.
> 
> 
> --------------------------------------------------------------------------------------------------------------------------------------------------------------
> 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