• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query Optimization

 
Raghuveer Rawat
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi Everyone
I generally use Oracle and SQL Server database. can anyone tell me how we can do query Optimization for these databases.
regards
raghuveer
SCJP
 
Andy Bowes
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Only way to do the query optimisation is to populate the database with representative data (i.e a large number of records which are similar to those that you expect to see in production) and then examine the execution plan that is generated by the database server when you execute the SQL statement. Using the execution plan identify any horrors such as a Table Scan through large tables and then look at either adding additional indexes, hints or rewriting the SQL until the plan looks reasonable.
You will need to find information from each DB vendor to really go about analysing the execution plan and optimising the performance of the SQL.
Generally I only do this when a performance issue has actually been identified or I am writing a complex query that performs joins across a number of sizeable tables.
HTH
 
Raghuveer Rawat
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi andy
Thanx for reply. Is there any other shortcut approach.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Raghuveer,
Is there any other shortcut approach.

You mean something like this:

Seriously, though, if you are using Oracle (8i or above), then you can configure the Cost Based Optimizer (CBO), and the query rewrite option.
However, I don't think I could explain it in a forum posting as well as the Oracle documentation does, or the multitude of available books and Web resources on the subject.
There was a recent discussion in the comp.databases.oracle.* newsgroups entitled intro to tuning that may be helpful.
Good Luck,
Avi.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic