Faster query with Parameters but slow with Laterals

Question

Post reply

Faster query with Parameters but slow with Laterals

Mahesh Bote

SSCrazy Eights

Points: 9107
More actions
July 3, 2019 at 3:04 pm

#3659812

Hi,
Recently while working on one of the query tuning, I experienced different behavior. with Hard coded values, query runs tremendously slow, but with parameters same query runs very quickly.
can someone guide, what would be the issue?

Thanks,
MH-09-AM-8694

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

Could be the statistics need updating and the plan it uses with the parameters is a better plan.

You should look at the execution plan of both queries.

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

Thanks Jonathan for the suggestion. I had a look at the Statistics and found they are very much updated with current date. I found difference in Actual Execution plan when ran query with Literals and Parameters. Query with Parameters run faster but not with Literals. Any idea why it is so?

This reply was modified 4 years, 9 months ago by Mahesh Bote.

MH-09-AM-8694

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

Do you mean literals not laterals?

The query with parameters will have a compiled plan that it reuses, so it looks like this plan is a better one than the one it works out when you run a query with literal (hardcoded) values.

If the query runs with different literals each time it will have to recompile the plan. If the query is complicated then recompiling the plan can take a long time.

My guess is that it's just not compiling the to the best plan. You could try flushing the cache (don't do this in a production environment):

DBCC FREEPROCCACHE

Then maybe the query with parameters will also run slowly, in which case you need to get the right statistics on your tables.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 4

Thanks Jonathan. query we have will run always with same set of parameters and this query is in PROD. so I can't run

DBCC FREEPROCACHE

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Mahesh Bote wrote:

Thanks Jonathan. query we have will run always with same set of parameters and this query is in PROD. so I can't run DBCC FREEPROCACHE

You could, however, lookup how to clear the proccache for just that one query. 😉

--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)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 6

Another possibility is that the SQL is written in a sub-optimal way. Rewriting the query might allow SQL Server to chose a better execution plan more easily. If you want to paste the query in we can all have a look and see if there is anything way it could be rewritten to perform better.

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 7

I wish I could, but those are nested views. Anyway I will try to re-write the views. Thanks for all your inputs

MH-09-AM-8694

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 8

sure Jeff, I will 😉

This reply was modified 4 years, 8 months ago by Mahesh Bote.

MH-09-AM-8694