Is this a bug? And does SQL Server 2005 fix it?

Hi all,
In the past, I've tried something like the following in my stored procedures
to allow table filtering:
SELECT * FROM Widgets
WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
AND (@.Color IS NULL OR Widgets.Color = @.Color)
NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
parameters to the stored procedure, allowing me to filter down my Widgets
table quite nicely.
This works great, but apparently hinders SQL Server 2000's ability to index
the table! This can lead to very bad performance when doing these types of
filtered queries.
My questions are: 1) Is this behavior a bug? I'm not sure why adding the IS
NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
the same problem?
Thanks!
George Saliba
Six88 SolutionsThe ISNULL is not the cause, the OR is the bad guy.
Example:
use northwind
go
exec sp_helpindexes 'dbo.orders'
go
set showplan_text on
go
declare @.d datetime
select orderid, orderdate, customerid
from dbo.orders
where orderdate = @.d and @.d is null
select orderid, orderdate, customerid
from dbo.orders
where orderdate = @.d OR @.d is null
go
set showplan_text off
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"George Saliba" wrote:

> Hi all,
> In the past, I've tried something like the following in my stored procedur
es
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
> NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
> parameters to the stored procedure, allowing me to filter down my Widgets
> table quite nicely.
> This works great, but apparently hinders SQL Server 2000's ability to inde
x
> the table! This can lead to very bad performance when doing these types of
> filtered queries.
> My questions are: 1) Is this behavior a bug? I'm not sure why adding the I
S
> NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
> the same problem?
> Thanks!
> George Saliba
> Six88 Solutions|||Ahh, very interesting!
So why does an OR clause cause indexes to be used when one side of the OR
clause contains a scalar (@.Column IS NULL) and the other contains a table
column (Table.Column = @.Column)? It seems like if one side is a scalar it
should still be able to maintain indexes?
I guess that's my real question then. Does an OR clause have to cause SQL
Server to not use indexes, even when one side of the OR clause is purely
scalar?
Thanks!
-George
"Alejandro Mesa" wrote:
> The ISNULL is not the cause, the OR is the bad guy.
> Example:
> use northwind
> go
> exec sp_helpindexes 'dbo.orders'
> go
> set showplan_text on
> go
> declare @.d datetime
> select orderid, orderdate, customerid
> from dbo.orders
> where orderdate = @.d and @.d is null
> select orderid, orderdate, customerid
> from dbo.orders
> where orderdate = @.d OR @.d is null
> go
> set showplan_text off
> go
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "George Saliba" wrote:
>|||SQL Server Transact-SQL WHERE Clause
http://www.sql-server-performance.c...t_sql_where.asp
AMB
"George Saliba" wrote:
> Ahh, very interesting!
> So why does an OR clause cause indexes to be used when one side of the OR
> clause contains a scalar (@.Column IS NULL) and the other contains a table
> column (Table.Column = @.Column)? It seems like if one side is a scalar it
> should still be able to maintain indexes?
> I guess that's my real question then. Does an OR clause have to cause SQL
> Server to not use indexes, even when one side of the OR clause is purely
> scalar?
> Thanks!
> -George
> "Alejandro Mesa" wrote:
>|||> I guess that's my real question then. Does an OR clause have to cause SQL
> Server to not use indexes, even when one side of the OR clause is purely
> scalar?
SQL Server 2005 corrects this to some degree. I took Alejandro's script and
modified it for the new AdventureWorks database:
set nocount on
use AdventureWorks
go
exec sp_helpindex 'Sales.SalesOrderHeader'
go
set showplan_text on
go
declare @.d datetime
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
set @.d = '20040731'
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
go
set showplan_text off
go
All four queries utilize a clustered index scan. The ones with AND perform
an additional filter before the scan. Here is the output, including
sp_helpindex:
index_name index_description index_keys
AK_SalesOrderHeader_rowguid nonclustered, unique located on PRIMARY rowguid
AK_SalesOrderHeader_SalesOrderNumber nonclustered, unique located on PRIMARY
SalesOrderNumber
IX_SalesOrderHeader_CustomerID nonclustered located on PRIMARY CustomerID
IX_SalesOrderHeader_SalesPersonID nonclustered located on PRIMARY
SalesPersonID
PK_SalesOrderHeader_SalesOrderID clustered, unique, primary key located on
PRIMARY SalesOrderID
StmtText
----
---
declare @.d datetime
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
StmtText
----
----
---
|--Filter(WHERE:(STARTUP EXPR([@.d] IS NULL)))
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d]))
StmtText
----
--
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
StmtText
----
----
---
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d] OR [@.d]
IS NULL))
StmtText
----
--
set @.d = '20040731'
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
StmtText
----
----
---
|--Filter(WHERE:(STARTUP EXPR([@.d] IS NULL)))
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d]))
StmtText
----
--
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
StmtText
----
----
---
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d] OR [@.d]
IS NULL))
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Hmm, it's unfortunate that this IS NULL is one of these non-sargable beasts.
It sure would make my life easier if they weren't. :(
On the upside, I realized I can remove the @.Param IS NULL part for columns
that are not nullable (since Column = @.Param where the @.Param is null will
return false always for non-nullable columns), which would leave only the
sargable part of those parts of the WHERE clause, which should increase
performance a bit. :)
Not ideal, but it will do. Thanks for your help!
-George
"Alejandro Mesa" wrote:
> SQL Server Transact-SQL WHERE Clause
> http://www.sql-server-performance.c...t_sql_where.asp
>
> AMB
> "George Saliba" wrote:
>|||"George Saliba" <GeorgeSaliba@.discussions.microsoft.com> wrote in message
news:6592A19D-5F74-44F2-94C9-401301101EA7@.microsoft.com...
> Hi all,
> In the past, I've tried something like the following in my stored
procedures
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
As mentioned the Or is the problem: If the Columns cannot be null you can
use:
WHERE (Widgets.WidgetID = COALESCE(@.WidgetID,Widgets.WidgetID))
AND (Widgets.WidgetTypeID = COALESCE(@.WidgetTypeID,Widgets.WidgetTypeID))
AND (Widgets.Color = COALESCE(@.Color,Widgets.Color))
Good Luck,
Jim|||George,
In some situation, the following syntax improves the performance a lot.
However, you have to make sure that the columns do not contains NULLs,
because this syntax will discard all rows with NULLs in any of the
columns WidgetID, WidgetTypeID, Color.
The query assumes that WidgetID is an int, WidgetTypeID is a smallint
and Color is a char or varchar.
SELECT * FROM Widgets
WHERE WidgetID BETWEEN COALESCE(@.WidgetID, -2147483648)
AND COALESCE(@.WidgetID, 2147483647)
AND WidgetTypeID BETWEEN COALESCE(@.WidgetTypeID, -32768)
AND COALESCE(@.WidgetTypeID, 32767)
AND Color LIKE COALESCE(@.Color,'%')
Hope this helps,
Gert-Jan
George Saliba wrote:
> Hi all,
> In the past, I've tried something like the following in my stored procedur
es
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
> NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
> parameters to the stored procedure, allowing me to filter down my Widgets
> table quite nicely.
> This works great, but apparently hinders SQL Server 2000's ability to inde
x
> the table! This can lead to very bad performance when doing these types of
> filtered queries.
> My questions are: 1) Is this behavior a bug? I'm not sure why adding the I
S
> NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
> the same problem?
> Thanks!
> George Saliba
> Six88 Solutions|||Nice trick with between (I have used the '%' trick for varchar values
before). Probably not a big difference, but you could use 0 instead of the
lower bound if you know the values must all be positive integers.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42601C3E.2AE96DC5@.toomuchspamalready.nl...
> George,
> In some situation, the following syntax improves the performance a lot.
> However, you have to make sure that the columns do not contains NULLs,
> because this syntax will discard all rows with NULLs in any of the
> columns WidgetID, WidgetTypeID, Color.
> The query assumes that WidgetID is an int, WidgetTypeID is a smallint
> and Color is a char or varchar.
> SELECT * FROM Widgets
> WHERE WidgetID BETWEEN COALESCE(@.WidgetID, -2147483648)
> AND COALESCE(@.WidgetID, 2147483647)
> AND WidgetTypeID BETWEEN COALESCE(@.WidgetTypeID, -32768)
> AND COALESCE(@.WidgetTypeID, 32767)
> AND Color LIKE COALESCE(@.Color,'%')
> Hope this helps,
> Gert-Jan
>
> George Saliba wrote: