Sql server Performance Techniques

1.               Avoid * in SELECT statement

Practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.

1.   -- Avoid

2.  SELECT * FROM tblName

3.  --Best practice

4.  SELECT col1,col2,col3 FROM tblName

2.     Use EXISTS instead of IN

Practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.

1.   -- Avoid 

2.  SELECT Name,Price FROM tblProduct 

3.  where ProductID IN (Select distinct ProductID from tblOrder)

4.  --Best practice 

5.  SELECT Name,Price FROM tblProduct 

6.  where ProductID EXISTS (Select distinct ProductID from tblOrder)

3.    Avoid Having Clause

Practice to avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don't use HAVING clause for any other purpose.

4.               Create Clustered and Non-Clustered Indexes

Practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.

5.               Keep clustered index small

Practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly. Please refer the article Effective Clustered Indexes

6.               Avoid Cursors

Practice to avoid cursor since cursor are very slow in performance. Always try to use SQL Server cursor alternative. Please refer the article Cursor Alternative.

7.    Use UNION ALL inplace of UNION

Practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

8.               Use Schema name before SQL objects name

Practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.

1.   --Here dbo is schema name

2.  SELECT col1,col2 from dbo.tblName

3.  -- Avoid

4.  SELECT col1,col2 from tblName 

9.    Avoid unnecessary columns in the SELECT list and unnecessary tables in join conditions

·         Selecting unnecessary columns in a Select query adds overhead to the actual query, specially if the unnecessary columns are of LOB types.

·         Including unnecessary tables in join conditions forces the database engine to retrieve and fetch unnecessary data and increases the query execution time.

10.Try not to use COUNT(*) to obtain the record count in a table

·         To get the total row count in a table, we usually use the following Select statement:

 Collapse | Copy Code

SELECT COUNT(*) FROM dbo.orders

This query will perform a full table scan to get the row count.

·         The following query would not require a full table scan. (Please note that this might not give you 100% perfect results always, but this is handy only if you don't need a perfect count.)

 Collapse | Copy Code

SELECT rows FROM sysindexes 

WHERE id = OBJECT_ID('dbo.Orders') AND indid < 2

10.                       With (NOLOCK)