aspose file tools*
The moose likes JDBC and the fly likes can i still optimize this query, Any real why running slow Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "can i still optimize this query, Any real why running slow" Watch "can i still optimize this query, Any real why running slow" New topic
Author

can i still optimize this query, Any real why running slow

kundan sinha
Greenhorn

Joined: Aug 01, 2005
Posts: 15
Dear Friends,

I have written a query which takes around 2 minutes to execute. Is it possible to cut the time in execution. Any trick


with regards
kundan

[ December 05, 2005: Message edited by: kundan sinha ]
[ December 05, 2005: Message edited by: kundan sinha ]
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Two things that come to mind without reading your query too deeply:

1) Avoid nested joins whenever possible such as "SELECT * FROM (nested join)". There's often no need for them and they *SERIOUSLY* limit a database query optimizer's ability to detect faster paths. In fact, most query optimizes will ignore nested joins completely.

According to your code, you have at least 4. Instead use an inner join.

2) Try putting the system through a profile or tuner that can detect places to build indices on. The most important part in database optimize is the tuning, not the query, although as previously discussed inner joins are preferable to nested.

You can also try adding the indices yourself, where you think they are needed. For example, you select 'max' alot in your code, therefore a tree index on this column would likely improve performance since the maximum would then always be available right away.
[ December 05, 2005: Message edited by: Scott Selikoff ]

My Blog: Down Home Country Coding with Scott Selikoff
kundan sinha
Greenhorn

Joined: Aug 01, 2005
Posts: 15
Hi scott,

thanks for your good suggestions on my query. I am not having two options either to break it up in java or do inner joins as you recomended.

Secondly I want to know any profiler tool which i can use to check my query.

with regards
Kundan
Loren Rosen
Ranch Hand

Joined: Feb 12, 2003
Posts: 156
The usual approach is to use your DBMS' "explain" facility. This will tell you what algorithm(s) the database will use to execute the query.
 
 
subject: can i still optimize this query, Any real why running slow