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.
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.
Joined: Oct 07, 2003
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.
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.