THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

SQL Server and Programming Frameworks

This blog has moved! You can find this content at the following new location:

Published Thursday, July 11, 2013 2:28 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



T said:

I'm required to work with a tool that does something similar - before running and SQL or calling an SP, it runs the same code with what it considers to be "dummy parameter values" - e.g. passing "0" to date parameters.

The it calls the code with "real" parameters. As you might imagine, these queries compile with the "dummy" values, and then performance for real execution absolutely tanks. We're forced to work around this, often by adding option recompile to the SPs.

I do hate it when developers think doing stuff like this is "clever".

July 11, 2013 2:42 AM

Greg Low said:

Wow, that's nasty too.

July 11, 2013 7:08 AM

Andrew G said:

Ah, but how about an app (3rd party) where the approach is;

"Stored Procedures are more efficient. ... so ... "

Connect to database

Generate sp with required SQL, no it's not what you're thinking.  It's a parameterised sp.

Execute sp with appropriate parameters

Drop sp

for everything.  Everything that hits the server from the app (it's on it's own box, it's not allowed to play with anyone else.  ever)

Performance is as one might expect, well, probably even worse given that it's doing other mad stuff.  Incidentally, has anyone else ever seen a runaway TempDB log?!  It was a new one on me I can tell you.

Do I win?

July 11, 2013 10:47 AM

Þröstur Jónasson said:

Whenever I find stuff like this I open hoping to cheer me up by readmin epic tales of misinfortunes. Both of these should be submitted there.

July 11, 2013 7:35 PM

Andrew G said:

Personally I just feel like reaching for the heaviest, nastiest Cluebat there is

July 14, 2013 3:48 PM

Emmett C. said:

I actually look forward to cloud based solutions because the metered use of resources will curtail the foolishness.  Those that can think will use resources efficiently while those that can't think will get a huge bill.

July 15, 2013 9:13 PM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement