• 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 optimizer

 
Ranch Hand
Posts: 493
Android Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Guys ,

I need a sql optimizer to optimize my SQL queries (most of these queries are Select statements) because some of them takes alot of time to be executed , so any advice regarding this ?
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Most DBs come with tools that show the query plan; e.g., Postgres has EXPLAIN. Check the docs of whichever DB you're using for what it offers. It might just be a missing index (or several).
 
S Shehab
Ranch Hand
Posts: 493
Android Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My DB is Microsoft SQL Server
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Sherif Shehab wrote:My DB is Microsoft SQL Server

Can you use the SQL Query Analyzer?

SQL Query Analyzer is an interactive, graphical tool that enables a database administrator or developer to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance.

 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The best SQL optimizers are humans. If possible, talk to a DBA who can help you work out the issue. In general, database optimization tools may only help you find the troublesome queries, but rarely how to fix them. The vast majority of the time this involves adding an index to a particular column (Hash or Btree)

I recommend find a process that's taking a long time. Then isolate the exact query that's taking a long time. Then start stripping away conditions and joins of the query until you find the one condition or one join that's slowing it down. That is often where you need to insert an index. Resolving some slow queries could involve refactoring your database schema or even adding denormalized (see: materialized views) data. There's no magic tool that's going to solve it for you, you just need to be a detective.

As a side note, I'm not against query optimization tools, in some cases they can find things you'd never think of. I just feel the best query optimizers are humans, since 99 times out of a 100 a single index will resolve the slowdown.
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Scott is so right. The tools will not optimize your query, but they will facilitate the process.
The tool will tell you if your query is using a particular index, or if it's doing a full table scan.
 
S Shehab
Ranch Hand
Posts: 493
Android Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Guys for the advice ..
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic