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:

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.

View raw message