Several tips about query performance optimization

The original article is here.

The 4 following tips are particularly helpful and can be immediately put into daily use:

Views and Stored Procedures

We can avoid using heavy duty queries by using stored procedures and views. Stored procedures are commonly used flexible database programming objects. Stored procedures are compiled once, so it will not create the execution plan every time, thus reducing your network traffic. Views give more security. For example, you have a table containing sensitive data; you might wish to hide those columns from certain users, you can use views.

Table Variables

Avoid temporary tables if you don’t need transactions on that table. In that case you can use Table variables. When you create the temporary table, it will create the physical table in the tempdb database. However, if you create a table variable, it only resides in the memory. It will be much faster than temporary tables, and table variables reduce the recompilation of the code.

Notes: a good practice is to use table variable where you need a small table to operate on, loading big table variable into memory simply erodes its performance gain by interfering with other in-memory activities of SQL Server.

Row Count

If you’re in the situation to use the count(*), then use the following query. This is way faster than function count(), especially for huge tables with millions of rows

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND (indid < 2)

Set No Count On

If the No Count is off in your stored procedures, then the number of rows affected value will be returned. It will increase the network traffic. Set No Count should be On in your stored procedures. Hence the number of rows affected will not return.