TSQL query slower with variable than hard-coded

Question

TSQL query slower with variable than hard-coded

tindog

SSCarpal Tunnel

Points: 4892
More actions
April 1, 2016 at 5:17 am

#317464

Hello,
I have a query that looks like this, which takes 17 seconds:
DECLARE@DFrom DATE = '2015-01-01',
@DTo DATE = '2015-01-31'
SELECTt.MyID
FROMTDDB.dbo.TableX tx
INNER JOINTDDB.dbo.TableY ty ON ty.MyID = tx.MyID
INNER JOINTDDB.dbo.TableZ tz ON ty.YID = tz.YID
INNER JOINTDDB.dbo.BigTable bt ON bt.ZID = tz.ZID
INNER JOINTDDB.dbo.AccTbl at ON at.AID = bt.AID
WHEREbt.SomeDate BETWEEN @DFrom AND @DTo
ANDat.Acc = 'XKDJR382'
When I use a variable for at.Acc it takes 55 seconds:
DECLARE@DFrom DATE = '2015-01-01',
@DTo DATE = '2015-01-31',
@Acc VARCHAR(20) = 'XKDJR382'
SELECTt.MyID
FROMTDDB.dbo.TableX tx
INNER JOINTDDB.dbo.TableY ty ON ty.MyID = tx.MyID
INNER JOINTDDB.dbo.TableZ tz ON ty.YID = tz.YID
INNER JOINTDDB.dbo.BigTable bt ON bt.ZID = tz.ZID
INNER JOINTDDB.dbo.AccTbl at ON at.AID = bt.AID
WHEREbt.SomeDate BETWEEN @DFrom AND @DTo
ANDat.Acc = @Acc
I've got a temporary workaround for the development phase (putting dbo.AccTbl into a temp table where at.Acc = @Acc and joining on that instead), but does anyone have any ideas why the performance would get so much worse when using a variable?
I thought perhaps the lack of index on at.Acc may be the problem (third party db we cannot alter at the moment) but I wondered if there may be other reasons.
Thanks!
LinkedIn
February 13, 2021 at 9:38 am

This was removed by the editor as SPAM
September 1, 2021 at 6:57 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Does this happen every time, even after you clear the plan cache (don't do that on a production server, though)? Have you compared the execution plans for the two queries?

John

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 2

John Mitchell-245523 (4/1/2016)
Does this happen every time, even after you clear the plan cache (don't do that on a production server, though)? Have you compared the execution plans for the two queries?
John

Yes, I've tried doing it whilst clearing the cache each time (don't worry, only on development).

I've compared the plans and they look to be identical - both using an nonclustered index seek when it gets to the WHERE clause.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

What is the data type of the Acc column? If it's not varchar(20), there may be some conversion going on. Please will you post the execution plans?

John

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 4

It's VARCHAR(20).

I can't without breaking company rules due to the table and field names...I'll find a moment this afternoon to recreate the issue using temporary tables and post the plans from those.

Thankyou

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 5

Putting everything into temp tables made the latter query (55s with the variable) quicker than the former (hardcoded). I'm a bit stumped.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

It's parameter sniffing, or more specifically it's lack of parameter sniffing.

SQL can't tell the value of variables at compile time, and so it will assume a certain row count based only on the average distribution of data in the table. If that estimate is wrong, the query performance suffers.

You can put the query into a stored procedure and use parameters instead of variables, you can use the RECOMPILE hint for the query, you can use OPTION (OPTIMISE FOR), any of them should work.

Recompile means some CPU overhead, stored procedure means more development, optimise for could give you parameter sniffing problems later if the query is run with different values.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 7

Thanks Gail, I'll try it in a stored procedure/with hints on Monday.

Have a great weekend!

Sergiy SSC Guru Points: 110208 More actions · Answer 8

tindog (4/1/2016)
Hello,
I have a query that looks like this, which takes 17 seconds:
DECLARE@DFrom DATE = '2015-01-01',
@DTo DATE = '2015-01-31'
SELECTt.MyID
FROMTDDB.dbo.TableX tx
INNER JOINTDDB.dbo.TableY ty ON ty.MyID = tx.MyID
INNER JOINTDDB.dbo.TableZ tz ON ty.YID = tz.YID
INNER JOINTDDB.dbo.BigTable bt ON bt.ZID = tz.ZID
INNER JOINTDDB.dbo.AccTbl at ON at.AID = bt.AID
WHEREbt.SomeDate BETWEEN @DFrom AND @DTo
ANDat.Acc = 'XKDJR382'
When I use a variable for at.Acc it takes 55 seconds:
DECLARE@DFrom DATE = '2015-01-01',
@DTo DATE = '2015-01-31',
@Acc VARCHAR(20) = 'XKDJR382'
SELECTt.MyID
FROMTDDB.dbo.TableX tx
INNER JOINTDDB.dbo.TableY ty ON ty.MyID = tx.MyID
INNER JOINTDDB.dbo.TableZ tz ON ty.YID = tz.YID
INNER JOINTDDB.dbo.BigTable bt ON bt.ZID = tz.ZID
INNER JOINTDDB.dbo.AccTbl at ON at.AID = bt.AID
WHEREbt.SomeDate BETWEEN @DFrom AND @DTo
ANDat.Acc = @Acc
I've got a temporary workaround for the development phase (putting dbo.AccTbl into a temp table where at.Acc = @Acc and joining on that instead), but does anyone have any ideas why the performance would get so much worse when using a variable?
I thought perhaps the lack of index on at.Acc may be the problem (third party db we cannot alter at the moment) but I wondered if there may be other reasons.
Thanks!

This must work as good as the temp table approach:

DECLARE@DFrom DATE = '2015-01-01',

@DTo DATE = '2015-01-31',

@Acc VARCHAR(20) = 'XKDJR382'

SELECTt.MyID

FROMTDDB.dbo.TableX tx

INNER JOINTDDB.dbo.TableY ty ON ty.MyID = tx.MyID

INNER JOINTDDB.dbo.TableZ tz ON ty.YID = tz.YID

INNER JOINTDDB.dbo.BigTable bt ON bt.ZID = tz.ZID

WHEREbt.SomeDate BETWEEN @DFrom AND @DTo

AND bt.AID = (SELECT TOP 1 at.AID FROM TDDB.dbo.AccTbl at WHERE at.Acc = @Acc)

This asasumes that Acc values in TDDB.dbo.AccTbl are unique.

If it's true you better enforce it with a unique key.

If it's not true use this check instead:

WHEREbt.SomeDate BETWEEN @DFrom AND @DTo

AND bt.AID in (SELECT at.AID FROM TDDB.dbo.AccTbl at WHERE at.Acc = @Acc GROUP BY at.AID)

GROUP BY must be there, otherwise it won't work.

Also.

Which tables contribute to SELECT part of the query?

Any table(s) which fields are not listed in SELECT better be put into WHERE EXISTS check instead of INNER JOIN.

_____________
Code for TallyGenerator

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 9

Appreciate the help Sergiy - however think I ditched/resolved this one 6 months ago!

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

tindog (6/21/2016)
Appreciate the help Sergiy - however think I ditched/resolved this one 6 months ago!

You previous post on this thread was "only" 2 months and 3 weeks ago where you said you were still stumped. 😉

If you did resolve this since then, any chance of you sharing what you did? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 11

tindog (6/21/2016)
Appreciate the help Sergiy - however think I ditched/resolved this one 6 months ago!

Well, there is no any sign of it in the thread... 😉

And since it's a public resource not only you may benefit from a solution posted here.

Sometimes people post comments like "Thanks it was really helpful" 5 or more years after the thread was forgotten.

You never know.:-)

_____________
Code for TallyGenerator