Dynamic Where Clause execution
-
I have always used the following approach in stored procedures when I wanted to create a dynamic where clause. My assumption was that SQL Server used sequential order of operations such that when "@EmployeeID IS NULL" evaulated to TRUE, then "EmployeeID = @EmployeeID" would not be evaluated, thus avoiding an index (or table) scan if I passed in a null parameter to my SP. But I checked the execution plan and it looks like the scan occurs anyway.
Is that correct? Why would they design the optimizer like that? Is there a different way to achieve my intent?
USE Northwind
DECLARE
@EmployeeID INT
SET
@EmployeeID = NULL
SELECT
*
FROM
dbo.Employees
WHERE
@EmployeeID IS NULL OR EmployeeID = @EmployeeID
-
Why would you expact a scan *NOT* to occur, given the scenario you presented ?
When you set @EmployeeID to NULL, you want all records returned, right ?
A scan is the most efficient way to return all records. The query needs to access every page in the table, therefore scanning every page is what is required.
-
Good point. Can you show everyone a way to generate a seek in a similar situation (dynamic search problems).
-
Actually, reconsidering my original thought, why wouldn't the second condition (@EmployeeID = 234, for example) result in an index Seek instead of a Scan?
-
Can't wait to hear this answer...
. -
Because you're still searching for where @EmployeeId IS NULL.
You could say this...
------
IF COALESCE(@EmployeeId,0) = 0 --In case the calling app. is defaulting the variable to zero.
SELECT *
FROM Employees
ELSE
SELECT *
FROM Employees
WHERE EmployeeId = @EmployeeId
------
On a separate note, you shouldn't ever do select *'s. Select only what you need, even if what you need is currently everything as that may not always be the case.
Hope that helps,
Enjoy!
A Brown
Manage all of your backups in one simple job using this script. -
Hi Andy,
What to do in case you've more than 1 parameter and you've to use all of the parameters for dynamic search.
like,
select *
from employees
where (@p1 is null or fld1 = @p1)
AND (@p2 is null or fld2 = @p2)
AND (@p3 is null or fld3 = @p3)
Thanks,
hr_sn
-
Your way is fine, but it will cause a scan...
One way to avoid the scan is to do the following:
IF @p1 is null and @p2 is null and @p3 is null
select *
from employees
ELSE IF @p1 is null and @p2 is null and @p3 is not null
select *
from employees
where fld3 = @p3
ELSE IF @p1 is null and @p2 is not null and @p3 is null
select *
from employees
where fld2 = @p2
ELSE IF @p1 is null and @p2 is not null and @p3 is not null
select *
from employees
where fld2 = @p2
and fld3 = @p3
ELSE IF ... etc., etc.
There's another option that I know of that uses dynamic SQL, but that would force a recompile on every execution.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script. -
I just don't want to use dynamic SQL. I thought there may be some other way out.
Thanks anyway!
-
Well, the IF ... ELSE way I listed above will work without being dynamic. The only issue with it is that you have to explicitly code out each option. While that my be tedious, it will guarantee the most optimized query for each option.
If I had the choice, I'd make sure that I knew what to expect from the calling application every time.
Enjoy!
A Brown
Manage all of your backups in one simple job using this script. -
Thx for the update.
-
Actually the IF ELSE solution can be problematic too unless you cause the proc to recompile on each call. The plan created for one IF clause may not be good for the next IF and thus you get inconsistant performance from the proc. You cannot code a proc to use dynamic where clauses and get performance. Dynamic sql is one way around it but this too will cause a proc recompile and apart from that will require elevated table permissions which could be bad.
The optimal method, sadly, is to create a proc for each variation, you might want to direct them through a central calling proc - that's what I usually do - if performance and scalability isn't an issue then the IF ELSE will work - although you might want to consider adding a with recompile.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply