• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sql optimizer

 
Sherif Shehab
Ranch Hand
Posts: 485
Android Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
  • 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 ?
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • 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).
 
Sherif Shehab
Ranch Hand
Posts: 485
Android Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My DB is Microsoft SQL Server
 
Jan Cumps
Bartender
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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.
 
Sherif Shehab
Ranch Hand
Posts: 485
Android Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Guys for the advice ..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic