It is more likely that the problem can be reproduced with data, so
it is more likely the issue can be addressed if you provide the
insert scripts.
Francois Orsini wrote:
Hi David,
I know you work hard on trying to optimize the query but would you
have an output of the execution plan and runtime statistics for this
particular one?
Let me know if you need the information on how to dump these - this
way we can look at where it is spending most of the time - if the
index is being used or not (I see only 1 in the DDL script) - also,
how big the table is?
Thanks,
Francois Orsini
On 6/21/05, David Zonsheine <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
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.
