db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stan Bradbury <bradb...@Mutagen.Net>
Subject Re: How to optimize the query
Date Thu, 28 Oct 2004 15:42:56 GMT
David Zonsheine wrote:

> Hello Derby Users
>  
> We are going to use Derby as an embedded data base for thousands of 
> applications we deliver to the market.
> However, we have a simple database with a not so easy query that takes 
> about 0.016 seconds in Oracle and 2.5 sec in Cloudscape (Derby).
> I posted a question about hints and found out that there are no hints 
> in Derby right now.
> We tried to see the index usage but couldn't.
>  
> How can I optimize a query in Derby? How do you do it? Is there a 
> tool? Is there a command line parameter?
>  
> Please help. We want to use this database.
>  
> *David*
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------
> 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.

Hi David -
Set RUNTIMESTATISTICS and STATISTICS TIMING to obtain information on the 
query plans being selected and index usage.   Look at:
http://publib.boulder.ibm.com/infocenter/cldscp10/index.jsp?topic=/com.ibm.cloudscape.doc/perf37.htm

Even without optimizer hints  there are things that can be done within 
the SQL statement that will change the choices the optimizer makes.  If 
the query contains subqueries, an in-clause, a like-clause or the OR 
predicate it is often possible to flatten the query and greatly improve 
performance.  How the joins are specified can also make a difference.

Mime
View raw message