Solved

Optional Input Parameter in Stored Proc kills performance

Posted on 2011-03-09
7
548 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
  • 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 500 total points
ID: 35095004
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now