aspose file tools*
The moose likes JDBC and the fly likes Sql optimizer Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sql optimizer" Watch "Sql optimizer" New topic
Author

Sql optimizer

Sherif Shehab
Ranch Hand

Joined: Mar 05, 2007
Posts: 483

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 ?


Thanks,
Sherif
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42632
    
  65
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).


Ping & DNS - my free Android networking tools app
Sherif Shehab
Ranch Hand

Joined: Mar 05, 2007
Posts: 483

My DB is Microsoft SQL Server
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

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.


OCUP UML fundamental and ITIL foundation
youtube channel
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

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

Joined: Mar 05, 2007
Posts: 483

Thanks Guys for the advice ..
 
jQuery in Action, 2nd edition
 
subject: Sql optimizer