Perfomance Tunning (Update Statement)

How to increase the perfomance for an update statement. Suppose we have a table Tab1 which is having more than 5 crores of records. The user want to update a single column in the table Tab1 using update statement. Now the update statement will take few hours to update the column for all the records.
Hints to improve the performance of the update statement

• Check the corresponding table is having any triggers, if triggers are found then disable the triggers while updating.
• Check Is there any constraints for all the columns in the table, if so then make the constraints disable while updating the columns.
• Check is there any Clustered or Non Clustered Index for the table, if so then drop the indexes while updating and recreate the indexes once you completes the update.

I hope the above hints will helps you to improve the perfomance of an update statement.

Covering Index

Covering Index
Everyone is familiar with Clustered and non clustered index. In SQL Server 2005 there is an additional keyword provided for the non clustered index called Covering Index. Covering Index is an Index which is only applicable for non clustered Index, which will cover all the columns (columns mentioned in the query eg., Select col1,col2... from T1) in the Index itself. Actually it will improve the perfomance of the query by keeping all the columns in the Index lookup itself.

In the absence of the Covering Index, the Index will have only the Index column (only one column col1). Inorder to fetch other columns again it will use the Index column col1 lookup. This lookup search step (for other columns) you can avoid when you start using the covering Index.

I hope its helps..

Thiyagarajan

Index Seek Vs Index Scan

Index Seek Vs Index Scan
Many developers exactly knows the different between Index seek and Index scan. But few developers may be in the confusion to find the difference between Index Seek and Index scan.

• When the table is created with the Index then the query will not do the table scan. It will go for Index scan or Index seek it depends.
• For example, the table T1 have an index called Ix1. It is having more than 5 Cr of records.
• When the developer fires an query with a where condition on the Index column. Then the Query will for Index seek or Index Scan.
• Index Seek or Index Scan is purely depends on the density of the rows which the query returns.
• If the query returns less rows eg., only 5 to 10 rows then Index Seek will happen
• If the query return more than 10000 rows then Index Scan will happen

I hope you will get some idea about the difference between Index seek and Index Scan