Variable vs explicit value

Question

Post reply

Variable vs explicit value

Viewing 15 posts - 1 through 15 (of 16 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What are your indexes on these two tables?

MagicTSD._SMDBA_._TELMASTE_ tm

MagicTSD._SMDBA_._TELDETAI_ td

GSquared SSC Guru Points: 260824 More actions · Answer 2

I suspect "parameter sniffing" is your likely culprit. There are good articles on that if you look up that phrase in Google/Live/Yahoo/Dogpile/whatever.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

BJ Hermsen Hall of Fame Points: 3123 More actions · Answer 3

Lynn, unfortunately i have no control over indexes. The showplan is the same for both. there are covering indexes.

GSquared, Thanks. I will look into that.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Robert Hermsen (4/7/2009)
Lynn, unfortunately i have no control over indexes. The showplan is the same for both. there are covering indexes.
GSquared, Thanks. I will look into that.

That's what I was wondering about. Gus is correct, it could be parameter sniffing so that leads to the next question, is this query actually a part of a stored procedure?

BJ Hermsen Hall of Fame Points: 3123 More actions · Answer 5

nope. Just a plain ol' everyday query in SSMS.

Trying to wrap up development of a larger query set to use for SSRS and this is on of the queries that will be part of it. Has not left the SSMS environment.

Paul White SSC Guru Points: 150468 More actions · Answer 6

All the answers are in this Books Online entry:

http://technet.microsoft.com/en-us/library/ms175933(SQL.90).aspx

Cheers,

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

Florian Reischl

SSC-Dedicated

Points: 37301

April 12, 2009 at 2:52 am

#976255

Hi Paul

Thanks for the link, great article!

Greets

Flo

Paul White SSC Guru Points: 150468 More actions · Answer 8

Hey Flo,

Yeah - it's one of those articles I keep going back to: for some reason I can never quite remember all the details verbatim 😉 😀

/Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 9

I know what you mean... 😛

Article is bookmarked! 😎

Greets

Flo

BJ Hermsen Hall of Fame Points: 3123 More actions · Answer 10

BJ Hermsen

Hall of Fame

Points: 3123

April 13, 2009 at 6:47 am

#976524

Thanks for the link. I will dig into that.

Voitek Ten Centuries Points: 1366 More actions · Answer 11

THANK YOU!!! I ran into a similar issue where I created a stored procedure from a well perfroming query (2 seconds), which took 40 seconds as a stored procedure. What happened in the stored proc is I used cast statements on the passed parameter. When I removed them and used the parameters directly, the stored procedure ran at 2 seconds! Woohoo! Thank you!!!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

I will add to the thread that there are MANY cases where you can have widely varying inputs and dynamic sql is by far the best approach to ensure optimal query plans no matter what your input parameter values.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Voitek Ten Centuries Points: 1366 More actions · Answer 13

I prefer to use dynamic SQL, and that has always resulted in good performance. However, in this application I chose to encapsulate the query into a stored procedure for the sake of simplicity on the application end. The query is quite complicated and long, and I thought it would be easier to maintain as an sp.

I still see a performance loss even though I've corrected the constant folding problem.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

Voitek (1/29/2010)
I prefer to use dynamic SQL, and that has always resulted in good performance. However, in this application I chose to encapsulate the query into a stored procedure for the sake of simplicity on the application end. The query is quite complicated and long, and I thought it would be easier to maintain as an sp.
I still see a performance loss even though I've corrected the constant folding problem.

I have refactored many of my clients sprocs (especially things like search ones) with dynamic sql so it can certainly be done inside the sproc. Amazing perf gains too, not just because the optimizer gets explicit values to compare against. Often you can completely eliminate joins in the process as well - at least in search type sprocs.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

epriddy Old Hand Points: 343 More actions · Answer 15

In this case, I typically add "OPTION (recompile)" ... Yes, it forces the server to come up with a new execution plan (adding time) but the new execution plan is based on the variables.

Best of luck!