July 25, 2007 at 7:18 am
In the following where clause, I'm curious as to how things are done. Does a where clause evaluate things from top to bottom? Will it hit each and every sub-clause? Does it ever stop evaluating for some reason?
This where clause was changed by adding the line with @One_WRID on it. The Function SplitIDs takes a comma delimited string and parses it to return a bunch of valid in_WRIDs. It was performing poorly prior to this on very large databases.
It seems to me that the Function SplitIDs is always going to get called, unless @WRID happens to be NULL. I don't understand why adding the One_WRID line actually speeds things up. I don't know for a fact that it does, though. I haven't tested it; somebody else wrote it.
Thank you for clarification!
em
where
(@ID is NULL or A.in_WRAttachmentID = @ID) and
(@WRID is NULL or A.in_WRID in (Select ID from ISIS.SplitIDs(@WRID))) and
(@One_WRID is NULL or A.in_WRID = @One_WRID) and
(@AssetID is NULL or A.in_AssetID = @AssetID) and
(@SpaceID is NULL or A.in_SpaceID = @SpaceID) and
(@EmployeeID is NULL or A.in_EmployeeID = @EmployeeID) and
(@CalcStatusID is NULL or A.in_CalcStatusID = @CalcStatusID)
July 25, 2007 at 9:21 am
Did you look at the execution plan with and without the @WRID line added?
Otherwise, without knowing how the variables are declared and assigned content, there's not much we can tell you. Also, while this is a forum for SS2k5, you haven't indicated that you are using that version. So if you have looked at the execution plan, or want more information, please give us more details.
July 25, 2007 at 9:47 am
Um... I was hoping for a generic answer. However, here are the two stored procedures, Old and New. I can't get the Estimated Execution Plan to return anything useful, it simply returns T-SQL blocks.
em
Create PROCEDURE Old
(
@ID int = NULL,
@WRID varchar(5000)= NULL,
@AssetID int = NULL,
@SpaceID int = NULL,
@EmployeeID int = NULL,
@CalcStatusID int = NULL,
@RequestNumber varchar(10) = NULL
)
AS
if @RequestNumber is NULL
select
A.in_WRAttachmentID as [ID],
A.tx_UserName as [UserName],
A.dt_LastChanged as [LastChanged],
A.in_WRID as [WRID],
A.in_AssetID as [AssetID],
A.in_SpaceID as [SpaceID],
A.in_EmployeeID as [EmployeeID],
A.in_CalcStatusID as [CalcStatusID],
A.dt_CalcStatusDate as [CalcStatusDate]
from ISIS.TD_WR_ATTACHMENT A
where
(@ID is NULL or A.in_WRAttachmentID = @ID) and
(@WRID is NULL or A.in_WRID in (Select ID from ISIS.SplitIDs(@WRID))) and
(@AssetID is NULL or A.in_AssetID = @AssetID) and
(@SpaceID is NULL or A.in_SpaceID = @SpaceID) and
(@EmployeeID is NULL or A.in_EmployeeID = @EmployeeID) and
(@CalcStatusID is NULL or A.in_CalcStatusID = @CalcStatusID)
else
select
A.in_WRAttachmentID as [ID],
A.tx_UserName as [UserName],
A.dt_LastChanged as [LastChanged],
A.in_WRID as [WRID],
A.in_AssetID as [AssetID],
A.in_SpaceID as [SpaceID],
A.in_EmployeeID as [EmployeeID],
A.in_CalcStatusID as [CalcStatusID],
A.dt_CalcStatusDate as [CalcStatusDate]
from ISIS.TD_WR_ATTACHMENT A join ISIS.TD_WR W on W.in_WRID = A.in_WRID
where
W.tx_RequestNumber = @RequestNumber and
(@ID is NULL or A.in_WRAttachmentID in(@ID)) and
(@WRID is NULL or A.in_WRID in (Select ID from ISIS.SplitIDs(@WRID))) and
(@AssetID is NULL or A.in_AssetID = @AssetID) and
(@SpaceID is NULL or A.in_SpaceID = @SpaceID) and
(@EmployeeID is NULL or A.in_EmployeeID = @EmployeeID) and
(@CalcStatusID is NULL or A.in_CalcStatusID = @CalcStatusID)
and
CREATE PROCEDURE New
(
@ID int = NULL,
@WRID varchar(5000) = NULL,
@AssetID int = NULL,
@SpaceID int = NULL,
@EmployeeID int = NULL,
@CalcStatusID int = NULL,
@RequestNumber varchar(10) = NULL
)
AS
-- If the user passes in only one ID in @WRID,
-- then set it to another variable (@One_WRID),
-- so that we do not have to use the SplitIDs function.
-- The SplitIDs function is VERY time intensive.
-- This is only being done to get a a massive speed improvement on large databases.
DECLARE @One_WRID int
SET @One_WRID = NULL
IF @WRID IS NOT NULL
IF CHARINDEX(',', @WRID) = 0
BEGIN
SET @One_WRID = CAST(@WRID AS INT)
SET @WRID = NULL
END
-- If we do not have to filter on @RequestNumber, select from only one table.
if @RequestNumber is NULL
select
A.in_WRAttachmentID as [ID],
A.tx_UserName as [UserName],
A.dt_LastChanged as [LastChanged],
A.in_WRID as [WRID],
A.in_AssetID as [AssetID],
A.in_SpaceID as [SpaceID],
A.in_EmployeeID as [EmployeeID],
A.in_CalcStatusID as [CalcStatusID],
A.dt_CalcStatusDate as [CalcStatusDate]
from ISIS.TD_WR_ATTACHMENT A
where
(@ID is NULL or A.in_WRAttachmentID = @ID) and
(@WRID is NULL or A.in_WRID in (Select ID from ISIS.SplitIDs(@WRID))) and
(@One_WRID is NULL or A.in_WRID = @One_WRID) and
(@AssetID is NULL or A.in_AssetID = @AssetID) and
(@SpaceID is NULL or A.in_SpaceID = @SpaceID) and
(@EmployeeID is NULL or A.in_EmployeeID = @EmployeeID) and
(@CalcStatusID is NULL or A.in_CalcStatusID = @CalcStatusID)
else
select
A.in_WRAttachmentID as [ID],
A.tx_UserName as [UserName],
A.dt_LastChanged as [LastChanged],
A.in_WRID as [WRID],
A.in_AssetID as [AssetID],
A.in_SpaceID as [SpaceID],
A.in_EmployeeID as [EmployeeID],
A.in_CalcStatusID as [CalcStatusID],
A.dt_CalcStatusDate as [CalcStatusDate]
from ISIS.TD_WR_ATTACHMENT A join ISIS.TD_WR W on W.in_WRID = A.in_WRID
where
W.tx_RequestNumber = @RequestNumber and
(@ID is NULL or A.in_WRAttachmentID in(@ID)) and
(@WRID is NULL or A.in_WRID in (Select ID from ISIS.SplitIDs(@WRID))) and
(@One_WRID is NULL or A.in_WRID = @One_WRID) and
(@AssetID is NULL or A.in_AssetID = @AssetID) and
(@SpaceID is NULL or A.in_SpaceID = @SpaceID) and
(@EmployeeID is NULL or A.in_EmployeeID = @EmployeeID) and
(@CalcStatusID is NULL or A.in_CalcStatusID = @CalcStatusID)
;
July 26, 2007 at 5:37 am
Short answer, in an order determined by the execution plan that the optimiser comes up with. that order may vary with server load, data volumes and other factors.
It's possible that when the function was in the query, it got run multiple times, if say, it was the outer part of a nested loop join
It's very hard to look at anything other than a very simple query and say how it will be executed and in which order the filters will be run.
Couple comments on your query.
The if/else block can result in very unoptimal execution plans, as the optimiser generates a plan based on the params at first execute, and uses that every time after, even if the params would result in the other branch of the statement been executed.
The 'all-in-one' type query ((a = @a or @a is null) and (b = @b-2 or @b-2 is null) and ....) is almost guaranteed a bad plan, as the optimiser can't accurately sniff the constant to null comparison and will either assume that all evaluate to true, or all to false. That cane from one of the SQL dev team at last year's PASS conference.
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
July 27, 2007 at 10:55 am
Passing a column value to a function makes the where clause non-sargable. However, it is still possible it is rarely executed as the optimizer should know enough to test it last. If any of the rest of the "(@arg is null or a.col = @arg)" tests evaluate to false, the rest of the testing for that row can be skipped.
Other good news is that the function call, while not altogether removable, can be improved somewhat:
(@WRID is NULL or CharIndex( ID, @WRID ) > 0)
This should execute a lot better than creating a result table in a function. Possible bad news is that this option may not work depending on the form of your data.
CharIndex( '10', '40,60,70,110,130')
will return 11, a false positive. You'll have to check that.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply