CTS – Your Technology Partner

SQL Server Performance Tips

Written by Dwight McCants on March 14, 2014

Do you ever get tired of waiting for your SQL Server queries to finish executing? Sometimes when dealing with millions of records it seems as if this can take forever. Here’s a chart below with quick tips that will help solve your performance issues when querying a large amount of records using SQL Server.

 

More Efficient                                                                         Less Efficient

IF EXISTS

SELECT COUNT (*) FROM …. WHERE….

·         IF EXISTS(SELECT * FROM dbo.Product WHERE ProductId = 897213)

·         SELECT COUNT(*) FROM dbo.Product WHERE ProductId = 897213

 

EXISTS, NOT EXISTS, IN

NOT IN

·         SELECT * FROM dbo.Product WHERE ProductId IN (1,2,3)

·         SELECT * FROM dbo.Product WHERE ProductId NOT IN (4,5,6)

 

BETWEEN

IN

·         SELECT * FROM dbo.Product WHERE ModelYear BETWEEN 2010 and 2014

·         SELECT * FROM dbo.Product WHERE ModelYear IN (2010, 2011, 2012, 2013, 2014)

 

LIKE

SUBSTRING()

·         SELECT * FROM dbo.Product WHERE NAME LIKE ‘T%’

·         SELECT * FROM dbo.Product WHERE SUBSTRING(NAME,1,1) = ‘T’

The substring function can cause a table scan which will decrease performance.

 

DISTINCT

GROUP BY

·         SELECT DISTINCT ProductId FROM dbo.Product WHERE ProductId BETWEEN 45 AND 55

·         SELECT ProductId FROM dbo.Product WHERE ProductId BETWEEN 45 AND 55 GROUP BY StateId

 

Other performance pitfalls include:

·         Long transactions inside of a stored procedure.

·         Using ORDER BY, GROUP BY, or UNION. A workaround for this is to create an index on the column you wish to sort by.

·         Primary keys that are DATETIME, REAL, or FLOAT data types.

·         Using CHAR instead of VARCHAR on a column that will have drastic differences in data length.

·         SQL Server cursors. They can decrease both performance and scalability.

Comments

comments