February 27, 2008 at 6:38 am
When evaluating the WHERE clause, I find it good practice to include parenthsis around each target of comparison:
WHERE
( @ReorderPoint IS NULL OR ReorderPoint > @ReorderPoint )
and multiple comparisons
WHERE
( @ReorderPoint IS NULL OR ReorderPoint > @ReorderPoint )
and --OR
( @F2 IS NULL OR ReorderPoint > @F2 )
Without parenthsis, erroneous results may occur.
BTW, I really could have used this article a long time ago. I have no doubt that passers by will really appreciate its content. Thank you. 🙂
February 27, 2008 at 6:49 am
This is a reply to Andrew Hancox post #460732...
I have seen this occur. My sproc (and ultimately application) took a long time to process. When I debugged, I was able to trace it down to the sproc that was using the IS NULL pattern in the where clause.
My solution:
I DO NOT HAVE A GOOD ONE - sorry.
I tried reindexing, dropping the table and recreating. Since I did not have time to open the hood on MSSQL, I simply rewrote the query to excluded the use of the IS NULL pattern. It's old-school but I haven't looked back yet.
February 27, 2008 at 7:08 am
One of the downsides to using EXEC over the ISNULL/OR is that the login requires datareader rights in SQL2000. All other data access in the app is through stored procedures so this created a security issue. Does anyone know if using sp_executesql requires the data reader rights?
February 27, 2008 at 7:34 am
Another approach I have been using this year to the issue of dealing with parameters is to use table variables to store the filtered selections. In the absence of a selection for a given parameter, load the whole list of values to the table variable. In the select blocks, you can then join directly to the table variables and require no where clause whatsoever, the joins implement the filters for you. This has been especially helpful in cleaning up report code which previously required long chains of if then else blocks examining whether a parameter contains data or not and then implementing the appropriate where clauses below.
February 27, 2008 at 8:01 am
Both EXEC and sp_Executesql require data reader access. However, the downside of granting access is far outwayed by the potential performance gains.
If you are going to grant access then I would create a view and grant access to the viewrather than the table directly.
February 27, 2008 at 8:16 am
Lorrin,
I would be worried about the impact on memory and performance. I agree that it is a solution that would avoid dynamic sql. However, in reality I would expect that the overhead has the potential to be greater than the ISNULL solution.
The risk with the ISNULL solution is that it will carry out a table scan even if an index seek is the best way to retreive the data. Whereas, with multiple table variables you will access an index per clause. Each clause will in turn access an index to pull back the rows to populate the table. I am assuming that where one clause is not provided then all the values are entered (in which case that is a full index scan).
In essence the overhead of reading the values into the table variables is likely to be greater than a table scan. It is worth noting that it is a relatively low number of records that need to be returned before the optimiser chooses and index scan.
rick
February 27, 2008 at 9:10 am
Rick,
I agree with that statement, so I add a caveat. Having taken into consideration all the implications for performance, I use this method when dealing with small result domains (under 2000 items in a list). There is no best way, just a number of alternatives to consider when faced with this problem.
Thanks for your feedback.
February 27, 2008 at 1:42 pm
Outstanding post. I'm coming from the Oracle world and have been use to 'prepared statements' with java. Dynamic 'where' clauses are great. Thanks for the time on this one.
February 28, 2008 at 12:19 am
The best trick I learned for this involves NO dynamic SQL.
create procedure dbo.someproc
@fname varchar(50),
@lname varchar(50),
@title varchar(50)
as
select fname,lname,title from dbo.people where
fname = case when @fname is null then fname else @fname end)
and lname = case when @lname is null then lname else @lname end)
and title = case when @title is null then title else @title end)
go
Calling this procedure you can include all or none of the filter values and get the result set you want. I think I learned this method here at sqlservercentral.com it is very simple and I use it a lot. I have not benchmarked it but by avoiding dynamic sql I assume its got a headstart over those methods.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
February 28, 2008 at 12:27 am
Just read back again and saw that this method was already mentioned...and its side effects.
Column = ISNULL(@column, column) and those similar has the side effect of blowing the optimiser.
Maybe I should do some performance testing. Hopefully the "case" statement performs a bit better 😉
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
February 28, 2008 at 3:32 am
Good article. A minor correction. In the static queries part the article provides option 1, 2 and 3, showing three different ways of writing a conditional WHERE clause for the "=" operator, as well as for the "<", etc. operators. The author suggests at least that for the "=" operator these three options return the same resultset. For NULLable fields, options 1 and 2 do not return records where ReorderPoint is NULL, option 3 does return records where ReorderPoint is NULL. Hence their outcomes are different.
Suggestion: see also the very understandable SSC article "The Cost of Function Use In A Where Clause", posted on February 28, 2008.
Leendert.
February 28, 2008 at 3:43 am
I just came up with what I reckon is an awesome way of doing this. I build up what the name of the sproc should be (mp_dynamicsproc_param1_param5_order3) check to see if it exists, if it doesn't I build a string of sql to create it - this is no harder than building the dynamic sql to execute straight away - and then run the sproc in before executing it. This means that once I've run a work load through all the sprocs exist, if I want to change them all I just modify the master sproc and then delete all of the ones it's created. This means that all of the sprocs that actually do the heavy lifting are compiled sql with no unused parameters.
I've built this and implemented it and it's looking really good.
It does mean that a sproc with 12 potential parameters and 3 order by options will generate 12285 sprocs if all of the options are hit, but then a db can contain 2,147,483,647 objects – objects being tables, views, stored procedures, extended stored procedures, user-defined functions, triggers, rules, defaults, and constraints, so that's not really a problem.
February 28, 2008 at 4:06 am
Overall I like the article. I also wants tro thank all the buddies for good inputs.
😀
February 28, 2008 at 6:08 am
- Grasshopper -
Are you dropping these objects after you use them? What would be the advantage, security conerns? I like the path your taking can you post a brief example and explain the gain. Thanks.
February 28, 2008 at 6:12 am
Andrew,
I also used your approach - in a project some years ago - to create all possible procedures. If you do this in advance, your users do not need privileges to create procedure. Instead, the end-user will execute a dispatcher procedure which calls another, previously created, procedure based on given input. Maybe, this was also your intention.
I have had good experience, but of course the number of procedures may explode with all options...
Ola
Viewing 15 posts - 16 through 30 (of 106 total)
You must be logged in to reply to this topic. Login to reply