Search content within the blog

Friday, April 17, 2009

Few SqlServer Optimization tips

Restrict Query Result for performance optimization
Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic.

Restrict columns in the SQL query
Never try to return all the columns of the table when you require only a few of them. This helps in reducing network traffic.

Use stored procedure instead of heavy duty queries
Stored procedure exists as compiled objects on disk. Moreover, SQL server also keeps optimized execution plan for stored procedure. This really saves time and speeds up execution.

Avoid SQL cursor for optimal performance
You should avoid using SQL cursor since it has adverse effect on SQL server’s performance. It fetches the records row by row which results in repeated network round trips.

Use constraints instead of trigger
Trigger runs every time data gets updated even when it is not required. This adds overhead on the system.

Use table variable instead of temporary tables.
When you create a temporary table (#TABLE) it physically creates the table in tempdb. It is an added overhead to create table. A table variable doesn’t need to be created in the database, it can stay in memory and go out of scope immediately after the batch ends, just like regular variables.

Avoid using Having clause
Try to use where as far as possible instead of Having clause.

Include SET NOCOUNT ON in the stored procedure.
This prevents stored procedure to send messages indicating number of rows affected thus saves network traffic.

No comments:

Post a Comment