SQL performance tuning: variable in where condition cause performance issue

I am seeing a hug performance difference between two quires that are almost identical.

Query 1:

declare @type varchar(10) = ‘OR-‘
select * from client_details t1, Orders t2
where t1.client_code = @type +t2.client_code

Query 2:

select * from client_details t1, Orders t2
where t1.client_code = ‘OR-‘+t2.client_code

client_details has around 400 thousands records, Orders table has around 200 thousands.

Query 1 take long then 5 hours to processing, but Query 2 is finished within few minutes.

I did lots of search and testing, it seems SQL SERVER optimizer issue.

Please the difference of Literal Values and local variable from Kendra Little’s Blog:

https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/

Solution to fix my issue, I created an additional column called type and then use the value from this new column:

select * from client_details t1, Orders t2
where t1.client_code = t2.OrderType +t2.client_code

Leave a Reply

Your email address will not be published. Required fields are marked *