Sql optimizer

[S Shehab]

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]

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]

My DB is Microsoft SQL Server

[Jan Cumps]

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]

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]

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]

Thanks Guys for the advice ..