Performance tuning of SQL queries

sree_shanthi · New User Joined: 07 Apr 2007 Posts: 3 Location: Hyderabad

Hi
How the performance tuning can be done to sql queries what are the steps to be followed.

Thanks
Shanthi

dick scherrer · Posted: Sat Apr 14, 2007 1:46 am

Hello,

One place to start tuning a query is to make sure it only returns needed rows and that the item(s) used in the "where" (the predicates) are a key.

If you are joining tables, make sure the join is on a key. In addition to avoiding the dread cartesian product, you also want to avoid selecting specific rows from one table and then matching these agains another entire table.

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Nomally while using curosrs, we do some manipulation for eery fetched row, this may results in much time, thus holding the resources fo a lot of time and this may effects the performance.

In this case we can use the Fast unload and then we can dump the output of the cursor into a flatfile and then we can continue the process or manipulation. This improves the perfomance of the query.

This is the way we follow for a report generation program in our project recently.

I think this may helps you....

If any alternate ways please suggest me.