wood burning stoves*
The moose likes JDBC and the fly likes SQL query optimisation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query optimisation" Watch "SQL query optimisation" New topic
Author

SQL query optimisation

Saurabh Agrawal
Ranch Hand

Joined: Oct 07, 2003
Posts: 244
Hey all,
I have written down some SQL queries and also seen some SQL queries which are used in my current project.

The question is they are not optimised ones and thats why my performance is degreading.So i just wanted to know that are there any toold which are free and check the Queries for optimization.

I can see manually its not best of the queries but at the end of the day if i have 100 SQL queries, i cannot simply check each query.So it would be better if i get some tool which can do query optimization for me.

Any help wopuld be appreciated.
Saurabh


Success is not doing extraordinary things but doing ordinary things extraordinarily well.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Saurabh,
Usually you need to match the tool to the database. And since you didn't mention which database you are using, it's going to be very difficult to tell what tools are available for you.

Good Luck,
Avi.
Saurabh Agrawal
Ranch Hand

Joined: Oct 07, 2003
Posts: 244
Originally posted by Avi Abrami:
Saurabh,
Usually you need to match the tool to the database. And since you didn't mention which database you are using, it's going to be very difficult to tell what tools are available for you.

Good Luck,
Avi.


Hey Avi,
Sorry for incomplete information.I am currently using ORACLE 9i as database.So can you please throw some light on how we can use some tool for SQL Query optimization.

Thanks in advance,
Saurabh
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Saurabh,
Oracle has several tools to help you optimize your queries. Please refer to the Performance Tuning Guide and Reference which is available from:

http://tahiti.oracle.com

Also search the Ask Tom Web site.

Good Luck,
Avi.
vu lee
Ranch Hand

Joined: Apr 19, 2005
Posts: 189
If you don't want to use tool, this is what you may want to do. Log queries that have long response time. eg. those that took over 5 minutes. At the end of the day, look up those long-response queries and ask DBA to help -- principle to responsiblity :-)
Otherwise, break a query to many sub parts and inspecting:
1. whether the right index have been used. Usually, you'll need to insert SQL hint to the query. If the where clause contains other criteria which is not in the index, suggest to build a new index.
2. if your query uses multiple joins. Are they proper joins? Depending on the statistic of the data, determine whether query a single table at a time rather than having joins.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL query optimisation
 
Similar Threads
Miscellaneous JDBC Queries
SQL Query optimizer for DB2
SQL benchmarking with maven
listing hibernate named queries from java
Validating the SQL Query