Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL query optimisation

 
Saurabh Agrawal
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 206
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic