June 2, 2009 at 3:19 am
This can be write like this and avoid CASE statements from WHERE clause..
CREATE PROC test
@Stamp datetime,
@FirstName varchar(25) = NULL,
@Surname varchar(25) = NULL,
@Age int = NULL,
@Address1 varchar(40) = NULL
AS
BEGIN
DECLARE @Today BIT
SELECT @Today = CASE Datediff(day,@Stamp,getdate()) WHEN 0 THEN 1 ELSE 0 END
IF @Today = 1
BEGIN
SELECT FirstName, SureName, Age, Address1
FROM DAILY
WHERE FirstName = isnull(@FirstName, FirstName)
AND Surname = isnull(@Surname ,Surname )
AND Age = isnull(@Age, Age )
AND Address1 = isnull(@Address1,Address1)
END
ELSE
BEGIN
SELECT FirstName, SureName, Age, Address1
FROM HISTORICAL
WHERE FirstName = isnull(@FirstName,FirstName)
AND Surname = isnull(@Surname,Surname)
AND Age = isnull(@Age,Age)
AND Address1 = isnull(@Address1 ,Address1)
June 2, 2009 at 3:39 am
rafidheenm (6/2/2009)
This can be write like this and avoid CASE statements from WHERE clause..
Yes, but it doesn't change the execution characteristics. Just like the case statement version, that will typically run with a clustered index/table scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 4:01 am
TheSQLGuru (6/1/2009)
use adventureworks
go
drop proc test
go
create proc test (@productid int = null, @quantity int = null)
as
set nocount on
select *
from [Production].[TransactionHistory]
where (@productid is null or @productid = ProductID) --indexed
and (@quantity is null or @quantity = Quantity) --not indexed
go
dbcc freeproccache
go
--Gail, here is proof of an index seek for this query style
exec test @productid = 790--index seek, bookmark lookup (163 IO), 2 rows
--not pretty here
exec test @productid = 784--index seek, bookmark lookup (2395 IO), 746 rows
--UGLY
exec test ----index seek, bookmark lookup (340486 IO!!!)
What version of SQL are you running that on? I get clustered index scans for all of those (parallel scans, to be precise). (see attached)
If I include the maxdop of 1 then I still get clustered index scans. Might be because I loaded more data into AW for a presentation last year
SQL 2008 RTM.
Can you attach your exec plan please? I'd like to take a look at it, see if I can figure why I'm not getting the same plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 4:18 am
FWIW , i also get an index scan , i guess this conversation just goes to prove the sensitivity of this sort of query.
Microsoft SQL Server 2005 - 9.00.4207.00 (Intel X86)
Dec 17 2008 14:34:38
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
June 2, 2009 at 6:30 am
Looks like performance could suffer depending on the indexing available and the parameters available. Therefore, I would not use the technique unless the parameters simply to 'refine' a result set obtained after initial indexed selection logic in the WHERE clause.
June 2, 2009 at 6:46 am
Gail, I ran that test set on SQL 2005 SP2 (can't install SP3 due to missing install code for SP2 apparently). I noticed that I have this index on the referenced table. It is certainly possible I added this in manually to the base database for some demonstration purpose and never deleted it! 🙂
USE [AdventureWorks]
GO
/****** Object: Index [IX_TransactionHistory_ProductID] Script Date: 06/02/2009 07:41:13 ******/
CREATE NONCLUSTERED INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory]
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
With a clean proc cache I get that seek plan every time IF it is the first execution of the sproc.
I attached the plan for your review. I suppose it would be a bummer if this is a query regression from 2005 to 2008. Oh, just in case they changed the number of rows, I have 113443 total rows in my table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 2, 2009 at 6:56 am
I have a lot, lot more rows - 962662. Not due to MS. I padded out my copy of AW last year for my PASS presentation. Query time differences weren't that visible on smaller row sets and I wanted time differences that were noticable.
I would imagine it's row count. The lookups will be just too expensive on the larger row set, even in the first execution.
I'll see if I can find the original download of AW and recreate the DB.
Index on ProductID is there.
p.s. No seek in that plan. There's an index scan. Those I've often seen in these kind of queries. I'm wondering if it's possible to have an index seek, seeing that the seek predicate will not remain the same between executions with different parameters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 6:56 am
I dont think you attached the right plan, that one has an index scan
June 2, 2009 at 6:59 am
rafidheenm
notice in my post I said "I used to" as in
"I used to use lots of CASE statements and IF branches to handle queries that needed to be dynamic but build on the server side using multiple proc params. For example something like this:"
SQL mentioned and ammended by yourself
before changing to the dynamic version because of all the issues mentioned by Gila, SQLGuru etc
June 2, 2009 at 7:06 am
Dave Ballantyne (6/2/2009)
I dont think you attached the right plan, that one has an index scan
DOH!!! Good catch Dave. My brain sure did see seek last night when I did this. My apologies all!! 🙁
I tried this and it didn't get a seek either. Clearly I may be misremembering getting a seek plan on some variation of this in the past.
use adventureworks
go
drop proc test2
go
create proc test2 (@productid int = null, @quantity int = null)
as
set nocount on
declare @prodid int, @quant int
select @prodid = @productid, @quant = @quantity
select *
from [Production].[TransactionHistory]
where (@prodid is null or @prodid = ProductID) --indexed
and (@quant is null or @quant = Quantity) --not indexed
go
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 2, 2009 at 7:50 am
Ive Accumlated all the methods i can think of into one proc,
drop proc test2
go
create proc test2 (@productid int = null, @quantity int = null)
as
set nocount on
select TransactionId
from [Production].[TransactionHistory]
where (@productid is null or @productid = ProductID)
select TransactionId
from [Production].[TransactionHistory]
where (@productid is null or productid = @ProductID)
select TransactionId
from [Production].[TransactionHistory]
where productid = coalesce(@ProductID,ProductId)
select TransactionId
from [Production].[TransactionHistory]
where productid = CASE WHEN @ProductId IS NULL THEN ProductId ELSE @ProductId END
declare @MinInt integer
Declare @MaxInt integer
Select @MinInt = min(productid) ,
@MaxInt = max(productid)
from [Production].[TransactionHistory]
select *
from [Production].[TransactionHistory]
where productid BETWEEN coalesce(@productid, @MinInt) AND
coalesce(@productid, @MaxInt)
select TransactionId
from [Production].[TransactionHistory]
where productid BETWEEN coalesce(@productid, @MinInt) AND
coalesce(@productid, @MaxInt)
select *
from [Production].[TransactionHistory]
where productid BETWEEN coalesce(@productid, @MinInt) AND
coalesce(@productid, @MaxInt)
select TransactionId
from [Production].[TransactionHistory]
where productid BETWEEN isnull(@productid, @MinInt) AND
isnull(@productid, @MaxInt)
select *
from [Production].[TransactionHistory]
where productid BETWEEN isnull(@productid, @MinInt) AND
isnull(@productid, @MaxInt)
go
dbcc freeproccache
go
exec test2 @productid = 790
The results from the last 4 are quite interesting 😉
June 2, 2009 at 9:17 am
I suppose I may be confused, but I prefer to write this type of query with isnull...
where isnull(@Gender,Gender) = Gender
and isnull(@age+1,age)<=age
gives the same results as the nested OR statement (I'm not a fan of the @age+1, but the original query returns an empty set when asking for age=30 even though there is a record with an age of 30).
Is this really inefficient on large tables?
June 2, 2009 at 9:28 am
self.soul.friend (6/2/2009)
Is this really inefficient on large tables?
Yes.
It does not (and cannot) use indexes properly. Table scans are not much fun on million+ row tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 9:38 am
Interesting. You also get a seek if you simply hard code values for coalesce (and I presume isnull) such as this example. It avoids having to hit the table for the pair of MAXs:
select TransactionID
from [Production].[TransactionHistory]
where ProductID BETWEEN coalesce(@ProductID, 0) AND coalesce(@ProductID, 99999999) --should use actual limits of INT here!
You don't get the seek/bookmark lookup if you do select * with that query. Still a CI scan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 2, 2009 at 9:49 am
TheSQLGuru (6/2/2009)
Interesting. You also get a seek if you simply hard code values for coalesce (and I presume isnull) such as this example. It avoids having to hit the table for the pair of MAXs:
Yup, though as soon as you add a second condition, it goes back to a clustered index scan (at least for me). What do you get here? Index scan/Bookmark Lookup?
ALTER PROCEDURE Test2 ( @ProdID int = null, @Qty int = null)
AS
select TransactionID
from [Production].[TransactionHistory]
where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here!
AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)
GO
Exec Test2 @prodID = 790
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 46 through 60 (of 83 total)
You must be logged in to reply to this topic. Login to reply