CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduresparametersnull
maplemale
maplemale

Reputation: 2036

SQL Server 2008 Stored proc - Optimizer thinks my parameter is nullable

Optimizer seems to be getting confused about the null-ability of a varchar parameter and I'm not sure I understand why. I'm using SQL Server 2008 btw. All columns being queried are indexed. The TDate column is a clustered, partitioned index. The FooValue column is indexed, non-nullable column.

Example:

CREATE PROCEDURE dbo.MyExample_sp @SDate DATETIME, @EDate DATETIME, @FooValue VARCHAR(50)
AS
SET NOCOUNT ON

--To avoid parameter spoofing / sniffing
DECLARE @sDate1 DATETIME, @eDate1 DATETIME
SET @sDate1 = @sDate
SET @eDate1 = @eDate

SELECT
    fd.Col1,
    fd.Col2,
    fd.TDate,
    fl.FooValue,
    fd.AccountNum
FROM dbo.FooData fd
INNER JOIN dbo.FooLookup fl
    ON fl.FL_ID = fd.FL_ID
WHERE fd.TDate >= @sDate1
    AND fd.TDate < @eDate1
    AND fl.FooValue = @FooValue

Running this as a query works as expected. All indexes are seeks, no spoofing etc. Running this by executing the sproc takes 20 times longer - same query - same parameters. However, if I make the following change (very last line) everything works again.

CREATE PROCEDURE dbo.MyExample_sp @SDate DATETIME, @EDate DATETIME, @FooValue VARCHAR(50)
AS
SET NOCOUNT ON

--To avoid parameter spoofing / sniffing
DECLARE @sDate1, @eDate1
SET @sDate1 = @sDate
SET @eDate1 = @eDate

SELECT
    fd.Col1,
    fd.Col2,
    fd.TDate,
    fl.FooValue,
    fd.AccountNum
FROM dbo.FooData fd
INNER JOIN dbo.FooLookup fl
    ON fl.FL_ID = fd.FL_ID
WHERE fd.TDate >= @sDate1
    AND fd.TDate < @eDate1
    AND fl.FooValue = ISNULL(@FooValue, 'testthis')

It's like the optimizer is getting confused about whether the parameter is nullable or not? Also, adding a default value to the parameter doesn't make any difference. It still takes forever for the sproc to run unless I use = isnull(@parameter, 'some constant')

I'm happy I figured this out. But, I'd like to understand why this is happening and if there was a more elegant way to resolve the issue.

Upvotes: 3

Views: 314

Answers (1)

Mike Suarez
Mike Suarez

Reputation: 162

Re: Nullable variables

There is no concept of nullable for variables in T-SQL, the way that you can define a variable as nullable in c# using the ?. If you have a parameter in a stored procedure, the end user can pass whatever he or she wants into the stored procedure, be it a real value or a null.

Re: the query plan

The query plan that will get cached is the query plan that gets generated upon the first time you call this stored procedure.. so if you passed in a null for @FooValue the very first time you ran it, then it will be optimized for @FooValue = null.

There is an OPTIMIZE FOR hint that you can use to optimize the query for some other value:

Or you can use WITH RECOMPILE, which will force the query plan to get regenerated on every run of the stored procedure.

Obviously there are trade-offs when using these types of hints, so make sure you understand them before using them.

Upvotes: 2

Related Questions