14 SQL Query Performance Tuning Tips

Cursors/Loops

Functions in the Where clause

SELECT *
FROM dbo.SomeTable
WHERE CONVERT(DATE, CreatedDate) = '2020-01-10'
SELECT *
FROM dbo.SomeTable
WHERE CreatedDate >= '2020-01-10' AND CreatedDate < '2020-01-11'

Logic in the Where clause

SELECT *
FROM dbo.SomeTable
WHERE
CASE
WHEN Number > 1000 THEN 'High'
ELSE 'Low'
END = Threshold
CREATE TABLE #Temp
(
Id BIGINT,
Number BIGINT,
Threshold VARCHAR(4)
)

INSERT INTO #Temp (Id, Number, Threshold)
SELECT
Id,
Number,
CASE WHEN Number > 1000 THEN 'High' ELSE 'Low' END AS Threshold
FROM dbo.SomeTable

SELECT *
FROM #Temp
WHERE Threshold = 'High'DROP TABLE #Temp

Implicit Data Conversion

SELECT *
FROM dbo.SomeFrom
WHERE Counter = '5'
SELECT *
FROM dbo.SomeTable
WHERE Counter = 5

UPPER and LOWER

SELECT *
FROM dbo.Currency
WHERE UPPER(CurrencySymbol) = 'GBP'
SELECT *
FROM dbo.Currency
WHERE CurrencySymbol = 'GBP'
SELECT *
FROM dbo.Currency
WHERE CurrencySymbol = 'gBP'

Temp Tables vs Table Variables

CREATE TABLE #TempTableName
(
SomeId BIGINT,
SomeOtherData varchar(50)
)
DECLARE @TempTableVariableName TABLE
(
SomeId BIGINT,
SomeOtherData varchar(50)
)

Batching data

DECLARE @Start DATETIME = '2019-01-01'
DECLARE @End DATETIME = '2020-01-01'
DECLARE @Next DATETIME = DATEADD(DAY,1,@Start)

WHILE (@Next <= @End)

BEGIN
INSERT INTO dbo.SomeStagingTable (Id, Value1, Value2)
SELECT Id, CASE WHEN IsFlag = 1 THEN 'Y' ELSE 'N' END, CreatedDate
FROM dbo.SomeTable
WHERE CreatedDate >= @Start AND CreatedDate < @Next

SET @Start = @Next
SET @Next = DATEADD(DAY,1,@Start)
END

SELECT only what you need

Avoid using correlated sub queries

SELECT C.Name,
C.Email,
(SELECT CountryName FROM dbo.Country WHERE CountryId = C.CountryId)
FROM dbo.Customers C
SELECT C.Name,
C.Email,
CN.CountryName
FROM dbo.Customers C
LEFT JOIN dbo.Country CN ON CN.CountryId = C.CountryId

Existence checks

IF (SELECT COUNT(*) FROM dbo.Customers WHERE FirstName = @FirstName AND LastName = @LastName) > 0)
BEGIN
PRINT ‘Customer exists'
END
IF EXISTS (SELECT * FROM dbo.Customers WHERE FirstName = @FirstName AND LastName = @LastName)
BEGIN
PRINT 'Customer exists'
END
IF EXISTS (SELECT TOP 1 1 FROM dbo.Customers WHERE FirstName = @FirstName AND LastName = @LastName)
BEGIN
PRINT 'Customer exists'
END

Analysing Execution Plans

  • Missing indexes — if you query would benefit from an index then SQL will tell you. This can obviously have massive performance gains.
  • Actual vs Estimated rows — The estimated rows come from SQL statistics on the tables you are using. If there is a massive difference here then this is likely to have performance implications as SQL probably isn’t using the correct execution plan. It will be a bit of trial and error to see if your query can be written differently to improve this.
  • Scan vs Seek — SQL will tell you what operation was done to retrieve the information. Generally you want to avoid Full Table Scans like the plague and in some cases Index Scans as these require reading through the whole table or index until it finds the correct value. Ideally you want Index Seeks especially Clustered Index Seeks as these will be the fastest. If your index doesn’t include the columns that you need, a Key Lookup is done to retrieve the data. These can’t aways be avoided but if required you can add an index that uses these columns to make the key lookup go away.

Indexes

Dynamic SQL

Well Formatted Code

Summary

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store