Performance Tuning Tip#1

Tip: Never use functions/Calculations/Formula on the left side of Where clause or in Join Predicate.

Demo:

I am using Production.TransactionHistory Table in AdventureWorks2017 database. You can download the database here in OLTP downloads.

Let’s execute Below two queries which looks almost same


--Query 1
SELECT TransactionID
	,ProductID
	,TransactionType
	,Quantity
	,ActualCost
FROM Production.TransactionHistory
WHERE ActualCost / 2 > 50.00

--Query 2
SELECT TransactionID
	,ProductID
	,TransactionType
	,Quantity
	,ActualCost
FROM Production.TransactionHistory
WHERE ActualCost > 50.00 * 2 

Query#1 has condition AS WHERE ActualCost / 2 > 50.00

Query#2 has condition AS WHERE ActualCost > 50.00 * 2

Logically, both are same and returns the same result, but Execution wise, they are completely different and these queries have a huge difference in terms of performance.

Let’s create the Index first


USE [AdventureWorks2017]
GO

CREATE NONCLUSTERED INDEX [IX_ActualCost] ON Production.[TransactionHistory] ([ActualCost]) INCLUDE (
	TransactionID
	,[ProductID]
	,[TransactionDate]
	,[TransactionType]
	,[Quantity]
	)
GO

Now Execute the Query and check the Execution plan

As you can see, First query which is using formula at the left side of where clause has used Index scan and has a higher cost (82%) while the second query which is not doing any calculation at the left side of where clause has used Index seek and has much lower cost(18%) .

The reason of Index scan in first execution plan is Index has the data of ActualCost but it does not have any data of ActualCost/2 so it has to check the value of ActualCost/2 to satisfy the condition.

Follow me:

Blog: http://Sqldepository.com

YouTube : Sql depository

Facebook: https://www.facebook.com/sqldepository/

Google+ :https://plus.google.com/u/0/107093915534620159617

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

2 thoughts on “Performance Tuning Tip#1

  1. This was a good lesson and bought attention to small mistake.

    Please post a video about interview questions being asked as well.

Leave a Reply

Your email address will not be published.