2

I remember reading a while back that randomly SQL Server can slow down and / or take a stupidly long time to execute a stored procedure when it is written like:

CREATE PROCEDURE spMyExampleProc
(
   @myParameterINT
)
AS
BEGIN

   SELECT something FROM myTable WHERE myColumn = @myParameter

END

The way to fix this error is to do this:

CREATE PROCEDURE spMyExampleProc
(
   @myParameterINT
)
AS
BEGIN
   DECLARE @newParameter INT
   SET @newParameter = @myParameter

   SELECT something FROM myTable WHERE myColumn = @newParameter 
END

Now my question is firstly is it bad practice to follow the second example for all my stored procedures? This seems like a bug that could be easily prevented with little work, but would there be any drawbacks to doing this and if so why?

When I read about this the problem was that the same proc would take varying times to execute depending on the value in the parameter, if anyone can tell me what this problem is called / why it occurs I would be really grateful, I cant seem to find the link to the post anywhere and it seems like a problem that could occur for our company.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Purplegoldfish
  • 5,268
  • 9
  • 39
  • 59
  • I've never heard of an issue, but if it were it may be due to the precompilation of the stored procedure. Maybe using the parameter in a query prevents the stored procedure from being optimised. – Russell Dec 13 '11 at 10:14
  • @Russell I've the same opinion. – aF. Dec 13 '11 at 10:16
  • @Russell it wasnt an issue I had ever heard of too, I do think it was something due to the precompilation but I cant remember exact details. Im just asking now because Im moving a lot of queries into stored procs and it seems something that could be easily prevented with minimal work even if it is a rare bug – Purplegoldfish Dec 13 '11 at 10:17
  • 2
    It's quite well known: "parameter sniffing". Even MSDN whitepapers: http://technet.microsoft.com/en-us/library/cc966425.aspx#XSLTsection133121120120 – gbn Dec 13 '11 at 10:19
  • It's duplicate [Parameter Sniffing (or Spoofing) in SQL Server](http://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server), read [I Smell a Parameter!](http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx). – Michał Powaga Dec 13 '11 at 10:19
  • @Michał Powaga: I'm inclined to disagree because this presents the question differently – gbn Dec 13 '11 at 10:21
  • @gbn, you are right, problem is the same but different suggested solution, too hurry. – Michał Powaga Dec 13 '11 at 10:27
  • @MichałPowaga the question may be similar but as I didnt know what the problem was called I couldnt find any posts about it, also im more interested in preventing the issue occuring instead of finding a solution when it does. – Purplegoldfish Dec 13 '11 at 10:28

1 Answers1

5

You could always use the this masking pattern but it isn't always needed. For example, a simple select by unique key, with no child tables or other filters should behave as expected every time.

Since SQL Server 2008, you can also use the OPTIMISE FOR UNKNOWN (SO). Also see Alternative to using local variables in a where clause and Experience with when to use OPTIMIZE FOR UNKNOWN

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676