YAOO ! (yet another optimization observation)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

YAOO ! (yet another optimization observation)

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 16:12:29

Here's another performance observation.

This query runs in 30 seconds, with @CenterID and @BranchID = NULL.

SELECT CAST(UserID AS VARCHAR) AS UserID,
U.Fullname, C.Centername, B.Branchname
FROM dbo.Users U
INNER JOIN dbo.Centers C on C.CenterID = U.CenterID
LEFT OUTER JOIN dbo.Branches B on B.BranchID = U.BranchID
LEFT OUTER JOIN (SELECT * FROM dbo.ISSOs WHERE role='iao' or role='isso') I -- Only ISSOs with valid email are compared
ON I.Area = C.Centername
WHERE I.Area IS NULL
AND (C.CenterID = @CenterID OR @CenterID IS NULL)
AND (B.BranchID = @BranchID OR @BranchID IS NULL)
AND U.ClientID = @ClientID
AND U.Inactive = 1

But I switcheroo the conditions in red like this

SELECT CAST(UserID AS VARCHAR) AS UserID,
U.Fullname, C.Centername, B.Branchname
FROM dbo.Users U
INNER JOIN dbo.Centers C on C.CenterID = U.CenterID
LEFT OUTER JOIN dbo.Branches B on B.BranchID = U.BranchID
LEFT OUTER JOIN (SELECT * FROM dbo.ISSOs WHERE role='iao' or role='isso') I -- Only ISSOs with valid email are compared
ON I.Area = C.Centername
WHERE I.Area IS NULL
AND (@CenterID IS NULL OR C.CenterID = @CenterID)
AND (@BranchID IS NULL OR B.BranchID = @BranchID)
AND U.ClientID = @ClientID
AND U.Inactive = 1

It completes before my keystroke completes (under a sec.) Same result either way.

Looks like the optimizer isn't evaluating the constant elements of the expression at runtime, so it's comparing every CenterID to NULL unnecessarily.

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-30 : 16:34:42

Rather than concluding that SQL Server isn't evaluating constant elements at all in query expressions, your example below shows one case where SQL Server is not rearranging predicates to always take advantange of short circuit evaluation.

BTW, you might find better results by using a correlated WHERE NOT EXISTS() versus a LEFT JOIN where the right side is null.

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 16:42:01

Hi Jonathan,

You're right. It reads better and may execute better (I'll look at the execution plan.)

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 16:54:55

Changed to WHERE NOT EXISTS

It reads better.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources