[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Optional Input Parameter in Stored Proc kills performance

Posted on 2011-03-09
7
Medium Priority
?
610 Views
Last Modified: 2012-05-11
Hey All -

I have the stored procedure whose performance dies when I use an input parmeter set to NULL:

ALTER PROCEDURE [dbo].[TEST]
   (
	@AcctgPeriod int = NULL,
         @ReturnValue As int OUTPUT,
	@RecordCount As int OUTPUT,
	@ErrMsg AS nVarChar(MAX) OUTPUT
	)
AS
BEGIN

Open in new window


When "@AcctgPeriod int = NULL" is changed to "@AcctgPeriod int = -1" then the thing runs great.

Can't find anything to explain this.

Other informaiton ... using SQL Server 2008 r2.
0
Comment
Question by:MarkMahon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35090894
Kind of hard to say without seeing the rest of the procedure.  Only one thing I can ask, are you setting ANSI NULLS OFF at procedure creation?  
0
 

Author Comment

by:MarkMahon
ID: 35094685
Here is some more code.  I'll just say that I can change the input parameter's value from "Null" to "-1" and the thing works in seconds ... that's why I gave the limited code sample.  

USE [DataWarehouse]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TEST]
   (
	@ReturnValue As int OUTPUT,
	@RecordCount As int OUTPUT,
	@ErrMsg AS nVarChar(MAX) OUTPUT,
	@AcctgPeriod int = NULL
	--@AcctgPeriod int = -1   --works fine!
	--@AcctgPeriod int
	)
AS
BEGIN

	SET @ReturnValue = 0
	SET @ErrMsg = 'No Errors'

	IF (@ReturnValue = 0)
	BEGIN TRY
	    
		--DECLARE @AcctgPeriod int = NULL; when removed from the parameter list, the stored proc executes fine.

		--IF COALESCE(@AcctgPeriod, 0) = 0 -- hangs or takes forever
		--IF @AcctgPeriod = NULL -- evaluates to false and doesn't execute the begin/end
		--IF @AcctgPeriod = -1 -- works fine
		--IF (1=1)
		IF (@AcctgPeriod IS NULL) -- hangs or takes forever
		BEGIN
			SELECT	@AcctgPeriod = CAST(MAX(AcctgPeriod) AS int)
			FROM	ArchRe.dbo.Trandetl;
			SET @AcctgPeriod =	CASE
								WHEN CAST(RIGHT(@AcctgPeriod,2) AS smallint) IN (1,2,3) THEN CAST((CAST(LEFT(@AcctgPeriod,4) AS nvarchar(4)) + '03') AS int)
								WHEN CAST(RIGHT(@AcctgPeriod,2) AS smallint) IN (4,5,6) THEN CAST((CAST(LEFT(@AcctgPeriod,4) AS nvarchar(4)) + '06') AS int)
								WHEN CAST(RIGHT(@AcctgPeriod,2) AS smallint) IN (7,8,9) THEN CAST((CAST(LEFT(@AcctgPeriod,4) AS nvarchar(4)) + '09') AS int)
								ELSE CAST((CAST(LEFT(@AcctgPeriod,4) AS nvarchar(4)) + '12') AS int)
								END;
		END

Open in new window

0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 total points
ID: 35095004
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:MarkMahon
ID: 35095115
Thanks dbaSQL, I'll give it a try.

Mark
0
 

Author Comment

by:MarkMahon
ID: 35095583
dbaSQL -

Gave it a try and it works.  Thanks for the solution.

I'm also thinking of using
@AcctgPeriod int = -1 (and not @AcctgPeriod int = Null)
so that I have minimal changes in the stored proc (i.e., I won't have to change all of the @AcctgPeriod references to AcctgPeriod_i)

Butif "=Null" makes it optional but "= -1" I makes it required then I'll have to go with the solution in your post.

Thanks again, Mark
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35095672
Excellent!  Glad it worked out.
0
 

Author Closing Comment

by:MarkMahon
ID: 35095698
It is interesting that the issue has been around since the 2000 version, but does not appear to be addressed by 2005 or 2008.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question