?
Solved

Optional Input Parameter in Stored Proc kills performance

Posted on 2011-03-09
7
Medium Priority
?
602 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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