November 26, 2008 at 1:16 pm
Hey all,
Can't figure this one out ... I use logic like this quite frequently for "optional parameters". Doing it this time though, results in no return and just never ending execution. I can't even get the execution plan to determine why it's not returning.
WHERE
(
(
(@AcctNum_IN IS NULL OR A.AcctNum_AI = @AcctNum_IN)
)
OR
(
(@DebtorAcct_IN IS NULL OR A.DebtorAcct_AI = @DebtorAcct_IN)
)
OR
(
(@NANNum_IN IS NULL OR A.NAN_Num_AI = @NANNum_IN)
)
OR
(
(@ClientInternalNum_IN IS NULL OR A.ClientInternalNum_AI = @ClientInternalNum_IN)
)
)
Now If I simply change this to:
WHERE A.AcctNum_AI = @AcctNum_IN
It returns immediately.
I can't put my finger on this to as why it won't work. I even tried re-mapping my arguments to local variables within the procedure and still not having any luck.
Thoughts?
Thanks
November 26, 2008 at 1:17 pm
So this is crazy ... I just checked the estimated execution plan, and it looks like it's attempting to bring back ALL the records in the WHERE clause. Granted this isn't always an accurate representation, but it would explain why my return is taking ages ... millions of records involved.
Why oh why is this not working!
November 26, 2008 at 1:48 pm
Adam Bean (11/26/2008)
Why oh why is this not working!
I'll be blunt. That form of query does not and can not perform well. Regardless of indexes, order of bits in the where clause, parameters passed, you are highly likely to get a table scan all the time.
Two reasons for this.
1) The optimiser cannot accurately sniff the results of the variable IS NULL predicates and hence cannot get an accurate estimate of rows affected
2) There is no stable plan for that query.
Your options are pretty much limited to:
1) Live with it
2) Use dynamic SQL.
3) Upgrade to SQL 2008 and use the OPTION (RECOMPILE) hint.
Attached is one of the demos I did for my PASS presentation last week. It runs off the adventureworks DB and shows an example of a query like this and a dynamic SQL form that runs fast, reuses plans well and is not vulnerable to SQL injection.
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
November 26, 2008 at 2:07 pm
It's actually hitting an index seek every time ... and everywhere else I use this type of logic, normally performs like a champ. The piece I left out about this is, that this is SQL2000 and I fear that is my problem.
I finally let a run process (20 minutes later) and looking at the execution plan shows that I am in fact attempting to bring back all the data.
I am going to restore to a 2005 instance and try again to prove out that this is a 2000 problem.
November 26, 2008 at 2:16 pm
Adam Bean (11/26/2008)
I am going to restore to a 2005 instance and try again to prove out that this is a 2000 problem.
Nope. It performs like a dog on SQL 2005 as well. The only place you're going to get consistently good performance with it as written is SQL 2008, if and only if you apply the option(recompile) hint. On all others it's subject to the optimiser's inaability to properly sniff the constant comparisons. The optimiser will guess. Sometimes its right, sometimes it's wrong. When it's right you get a reasonable plan. When it's wrong, the plan is a disaster. The more options there are, the worse things get.
If you've been getting index seeks with this (and it's seeking an appropriate index, which it usually doesn't) then you've been lucky.
Three presenters spoke out about this style of query and how badly it performs at PASS last week. The other two were Itzik Ben-Gan and Kimberly Tripp.
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
November 26, 2008 at 2:25 pm
Yeah I wish I would have been at PASS instead of the DevConnections conference ...
Well, that sucks, but it is what it is. While I'm not a fan of dynamic SQL, it sounds like that is the route to take.
Thanks Gila
November 26, 2008 at 2:31 pm
I'm not a fan of dynamic SQL either, but it's the only practical way to get this working with more than around 3 parameters. With 3 or less, you can do a branching construct to call subprocedures based on the parameters passed.
Are the multiple optional parameters necessary?
Is there any way to change things in the app?
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
November 26, 2008 at 2:33 pm
GilaMonster (11/26/2008)
Are the multiple optional parameters necessary?Is there any way to change things in the app?
Yep ... I guess this is something some BA type people can run from excel which allows them to pick any columns they want, with the optional parameters of allowing them to pick whichever they want (for the where clause).
November 26, 2008 at 3:01 pm
Now, while what Gail has stated is quite true - in this particular case your query will always return ALL rows because that is exactly what you have asked it to do.
Let's take a look at what you have:
WHERE
(
(
(@AcctNum_IN IS NULL OR A.AcctNum_AI = @AcctNum_IN)
)
OR
(
(@DebtorAcct_IN IS NULL OR A.DebtorAcct_AI = @DebtorAcct_IN)
)
OR
(
(@NANNum_IN IS NULL OR A.NAN_Num_AI = @NANNum_IN)
)
OR
(
(@ClientInternalNum_IN IS NULL OR A.ClientInternalNum_AI = @ClientInternalNum_IN)
)
)
Now, let's test what will happen if you pass in @AcctNum_IN and all other parameters are NULL:
First, you are going to check if @AcctNum_IN IS NULL (false) OR A.AcctNum_AI = @AcctNum_IN (assume no match, which gives us false), then we are going to check if @DebtorAcct_IN IS NULL (true) - returns the row. Forget all the other checks - you have just told SQL Server to return all rows.
Now, what you can do - and probably get okay performance is to change the OR's between each column check to AND's as in:
WHERE
(
(
(@AcctNum_IN IS NULL OR A.AcctNum_AI = @AcctNum_IN)
)
AND
(
(@DebtorAcct_IN IS NULL OR A.DebtorAcct_AI = @DebtorAcct_IN)
)
AND
(
(@NANNum_IN IS NULL OR A.NAN_Num_AI = @NANNum_IN)
)
AND
(
(@ClientInternalNum_IN IS NULL OR A.ClientInternalNum_AI = @ClientInternalNum_IN)
)
)
Not guaranteed to perform well - and in fact, probably will not perform as well as dynamic SQL will - but, will definitely perform better than the original 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2008 at 3:39 pm
Well, I ended up going to dynamic SQL and it's working like a champ. It's also caching the plan and re-using properly.
Thanks guys!
November 27, 2008 at 4:06 am
Also worth to read
http://www.sommarskog.se/dyn-search.html
Failing to plan is Planning to fail
December 1, 2008 at 8:01 am
I was having a lot of trouble with this one myself and actually found an answer on Oracle and changed it to SQL Server. I also found that SQL Server will also use the indexes if possible.
WHERE
A.AcctNum_AI = coalesce(@AcctNum_IN,A.AcctNum_AI) OR
A.DebtorAcct_AI = coalesce(@DebtorAcct_IN,A.DebtorAcct_AI) OR
A.NAN_Num_AI = coalesce(@NANNum_IN,A.NAN_Num_AI) OR
A.ClientInternalNum_AI = coalesce(@ClientInternalNum_IN,A.ClientInternalNum_AI)
Hope it works out and check to see if there are indexes and if they are used. Sometimes it doesn't and I can't find a consitent method of testing it but does work.
Also keep in mind with all the OR's that if any column is null all the results will be returned so you might be looking for AND. Most of the time it would be
column = coalesce(@var,column) and
column2 = coalesce(@var2,column2) etc...
December 1, 2008 at 9:03 am
Just bear in mind that there is a subtle difference in behaviour between the coalesce method and the Varible IS NULL OR Variable = Column method. Specifically when the column itself is null.
Example from Adventureworks 2008
DECLARE @CarrierNumber VARCHAR(25)
SET @CarrierNumber = '000A-434D-BC'
SET @CarrierNumber = NULL
SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE ((@CarrierNumber IS NULL) OR (CarrierTrackingNumber = @CarrierNumber))
-- 970536 (in my DB, I've added data)
SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber = coalesce(@CarrierNumber,CarrierTrackingNumber)
-- 487352
Specifically, the coalesce as written above will not return rows where the column value itself is null. To get the same behaviour it needs to be expressed as
((column = coalesce(@var,column)) OR (@var is null and column is null)) and
((column2 = coalesce(@var2,column2)) OR (@var2 is null and column2 is null)) and etc...
or, to use the example from adventureworks
SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber = coalesce(@CarrierNumber,CarrierTrackingNumber) OR (@CarrierNumber IS NULL AND CarrierTrackingNumber IS NULL)
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
December 1, 2008 at 10:07 am
Yes, should have thought about the column value being null. :hehe: From my perspective of searching, at least with our application, we are always looking for a value and most of the fields we are looking for are not null. If data needs to be reviewed for values to be inserted different reports are used.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply