Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
kundan sinha
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4008
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
kundan sinha
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic