• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL query optimisation

 
Ranch Hand
Posts: 244
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Can you shoot lasers out of your eyes? Don't look at this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic