Tips to improve the performance of SQL Server

Posted by VIJI Friday, July 3, 2009

Try to avoid using SQL Server cursors, whenever possible.

T-SQL cursors - are commonly used in stored procedures, triggers and functions to manipulate data in result set on a row by row basis.

But the cursors generally uses lot of resources which can reduce the performance of sql server applications when compared to select statements.

So it would be better to use alternative ways to read the data(result set), for example

1. using data reader at the client side.

SqlCommand Sqlcmd = new SqlCommand("Select * from view1" ,con);
DataReader dr = Sqlcmd.ExecuteReader();

do while dr.HasRows
{

}

2. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.


If you cannot avoid use cursors, try to use "Forward Only" cursor.

0 Responses to Tips to improve the performance of SQL Server

Post a Comment