Link to home
Start Free TrialLog in
Avatar of MarkMahon
MarkMahon

asked on

Optional Input Parameter in Stored Proc kills performance

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.
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

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?  
Avatar of MarkMahon
MarkMahon

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks dbaSQL, I'll give it a try.

Mark
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
Excellent!  Glad it worked out.
It is interesting that the issue has been around since the 2000 version, but does not appear to be addressed by 2005 or 2008.