April 18, 2013 at 3:47 am
Knut Boehnert (4/18/2013)
Sure enough, in this simple example dynamic SQL is better because of the evaluation of @Param1, @Param2 and @Param3 - I fully agree with this.However the SQL in John's case is more elaborate.
So [challenge on] - how would you do a better dynamic SQL for John's case, Carlo? Because you say "dynamic SQL will run better" I am very interested in how dynamic SQL can achieve this in John's scenario.
Just a few pointers for no more than 15 minutes thinking are fine. Just something thought provoking to find maybe a better solution.
Actually John's query is much simpler to translate into dynamic SQL because is structured so that only one of the optional conditions is ever valid.
Carlo has made a mistake in translating my query (or not provided the full detail of how to translate it correctly).
For example, with my query: if @Param1 is null, @Param2 is not null, @Param3 is not null, you have to be careful to ensure your dynamic sql doesn't produce one of the following incorrect translations:
WHERE
(COL2 = @Param2)
(COL3 = @Param3)
or
WHERE
AND (COL2 = @Param2)
AND (COL3 = @Param3)
In answer to your question about how dynamic would could run better in John's scenario, it boils down to whether the "trick" to implement a "variable" WHERE clause also manages to trick the optimiser into not using appropriate indexes.
As per my previous comment, I abandoned my "trick" because the optimiser couldn't properly resolve it.
In theory the optimiser should be able to recognise that when @Param1 is null, then (@Param1 is null OR COL1 = @Param1) is equivalent to (TRUE or COL1 = @Param1).
Since TRUE or anything always evaluates to TRUE, it should be unnecessary to read COL1.
Unfortunately the optimiser failed to recognise this optimisation (with/without recompile).
This was many years ago, so the optimiser may have improved, which is why I asked if John had tested index selection.
The point is, the clever structure of the WHERE clause might make the optimiser's index selection inefficient. Over small result sets this won't be much of an issue. But can have a terrible impact on performance with large tables.
EDIT: It boils down to the difference between what is theoretically possible, and what the optimiser can actually handle.
April 18, 2013 at 3:59 am
Cadavre (4/18/2013)
Excellent link.
And nice to know that SQL 2008 introduced a feature to assist with this problem.
April 18, 2013 at 4:06 am
I have been using this technique for many years. It's much easier to maintain than dynamic SQL. I have also used it for dynamic GROUP BY and ORDER BY. I stopped using dynamic SQL a long time ago (in SQL 2000) since I could not find anything to check the statement syntax before execution.
April 18, 2013 at 4:06 am
I'm more than a little surprised that this technique is being put about as being new or clever. Surely its the neatest and most logical way to deal with the problem in hand. I've been using this technique for years and I'd hardly call myself a SQL expert. Maybe I'm smarter than I thought.
April 18, 2013 at 4:11 am
Jeff Burton (4/18/2013)
I have been using this technique for many years. It's much easier to maintain than dynamic SQL. I have also used it for dynamic GROUP BY and ORDER BY. I stopped using dynamic SQL a long time ago (in SQL 2000) since I could not find anything to check the statement syntax before execution.
You can easily check the statement. During development, before you have your EXEC(@SQL) command, you run a SELECT @sql command and run the resultant code. This tells you whether it is going to work properly or not.
But I agree that dynamic SQL can be messy and hard to read, especially if you're looking at someone else's code.
April 18, 2013 at 4:16 am
How does this approach perform when competing against something like...
where
d1.WeekDay = coalesce(@Weekday, d1.WeekDay)
does your method produce a better query plan? Does your version make a index seek on Weekday and not a scan?
April 18, 2013 at 4:16 am
How does this approach perform when competing against something like...
where
d1.WeekDay = coalesce(@Weekday, d1.WeekDay)
does your method produce a better query plan? Does your version make a index seek on Weekday and not a scan?
April 18, 2013 at 4:16 am
How does this approach perform when competing against something like...
where
d1.WeekDay = coalesce(@Weekday, d1.WeekDay)
does your method produce a better query plan? Does your version make a index seek on Weekday and not a scan?
April 18, 2013 at 5:06 am
Great article - though I did find myself wading through the rather large example. Have written this much smaller example to demonstrate the principle without deep diving into the scenario (may be easier for people to get to grips with the technique and then move on from there!).
DECLARE @info TABLE (id uniqueidentifier, name nvarchar(50), created datetime, hidden bit, special int)
INSERT INTO @info VALUES (NEWID(), '1', '2012-08-18 11:50:50.167', 1, 31337)
INSERT INTO @info VALUES (NEWID(), '2', '2013-01-11 11:50:50.167', 0, null)
INSERT INTO @info VALUES (NEWID(), '3', '2011-04-18 11:50:50.167', 1, null)
INSERT INTO @info VALUES (NEWID(), '4', '2013-03-12 11:50:50.167', 1, null)
INSERT INTO @info VALUES (NEWID(), '5', '2012-05-18 11:50:50.167', 1, null)
INSERT INTO @info VALUES (NEWID(), '6', '2012-04-18 11:50:50.167', 0, null)
INSERT INTO @info VALUES (NEWID(), '7', '2010-02-11 11:50:50.167', 1, null)
INSERT INTO @info VALUES (NEWID(), '8', '2013-04-10 11:50:50.167', 1, null)
INSERT INTO @info VALUES (NEWID(), '9', '2013-01-18 11:50:50.167', 0, 999)
DECLARE @pUseCondition int
DECLARE @startDate datetime
DECLARE @endDate datetime
SET @pUseCondition = 3
SET @startDate = '2012-01-01'
SET @endDate = '2012-12-31'
SELECT *
FROM @info i
WHERE(0=@pUseCondition
OR (1=@pUseCondition AND i.special IS NULL)
OR (2=@pUseCondition AND i.special IS NOT NULL)
OR (3=@pUseCondition AND (i.created >= @startDate AND i.created <= @endDate))
)
April 18, 2013 at 5:33 am
Knut Boehnert (4/18/2013)
Carlo Romagnano (4/18/2013)
Knut Boehnert (4/18/2013)
Carlo Romagnano (4/18/2013)
Old stuff!Dynamic sql will run better.
Sorry, all you are saying is that a red sky is always better than a green sky. But not why.
Everyone knows that a sky that is red is not that good (unless it is sun set or sun rise) and a green sky needs always taken with a precaution.
Dynamic SQL has its own share of problems and is generally for more advanced developers - so if a rookie needs to maintain it then all bets are off if it works afterwards.
Take this example:
[sql]
select colA, colB from mytab
WHERE
--If a param is not null, then it will be used as a filter
(@Param1 IS NULL OR COL1 = @Param1)
AND (@Param2 IS NULL OR COL2 = @Param2)
AND (@Param3 IS NULL OR COL3 = @Param3)
--etc.
[/sql]
In this case the optimizer should read COL1, COL2, COL3 also if @Parm1, @Parm2, @Parm3 IS NULL.
So, you force a useless read.
Instead,
[sql]
declare @sql varchar(8000) = 'select colA, colB from mytab'
if @Parm1 IS NOT NULL
SET @sql = ' WHERE COL1 = ' + @Param1
exec sp_executesql @sql
[/sql]
In this case, the optimizer read only COL1.
Sure enough, in this simple example dynamic SQL is better because of the evaluation of @Param1, @Param2 and @Param3 - I fully agree with this.
However the SQL in John's case is more elaborate.
So [challenge on] - how would you do a better dynamic SQL for John's case, Carlo? Because you say "dynamic SQL will run better" I am very interested in how dynamic SQL can achieve this in John's scenario.
Just a few pointers for no more than 15 minutes thinking are fine. Just something thought provoking to find maybe a better solution.
Yes, Knut, I find this approach to be more easily maintained than dynamic SQL. Someone flamed that I was claiming this to be new - it isn't, but I have never found it posted anywhere, and those who are trying to maintain klugey dynamic SQL procs may find it valuable.
I have tested it against large tables (1 billion plus rows) and performance is much better than with dynamic SQL.
April 18, 2013 at 5:46 am
where (0=@pUseCondition
or (1=@pUseCondition and d1.[WeekDay]='mon')
or (2=@pUseCondition and d1.[WeekDay]='tue')
Surely it would be more readable to have
where ('none'=@days
or ('mon'=@days and d1.[WeekDay]='mon')
or ('tues'=@days and d1.[WeekDay]='tue')
Why use integers for something like that? If you were going to use integers, the only reason would be to do bitmask operations, so you can define one int as containing tuesday and thursday for instance.
April 18, 2013 at 6:03 am
Here's another way:
This is the approach I always use
The coalesce function allows the first non-null value so if we don't add a value it won't filter.
Like so:
Where col1 = coalesce (@conditional1, col1)
and col2=coalesce (@conditional2, col2)
Sorry I see someone else mentioned this earlier.
April 18, 2013 at 6:28 am
Hi, I was thinking about the class schedule part of this post. Classes could be on any arbitrary set of days of the week. It occurred to me that the @Days table could have a column that is a binary place (1, 2, 4, 8, etc.) assigned for each day. Then each class could have a value assigned to it that is the sum of all the binary place values for its days of the week. Easier to show an example based on the @Days table in the post:
/*************************/
declare @Days table (DayNum int, DayAbbr varchar(3), DayBinVal int)
insert into @Days select 1,'Sun',1
union all select 2,'Mon',2
union all select 3,'Tue',4
union all select 4,'Wed',8
union all select 5,'Thu',16
union all select 6,'Fri',32
union all select 7,'Sat',64
--This would probably actually come from a Class table, rather than a local proc variable
declare @pUseConditionMWF INT = 42 -- Sum of @Days.DayBinVal for Mon, Wed, Fri (2 + 8 + 32)
--Then the select just uses the bitwise & to get the days that match
select * from @Days
where @pUseConditionMWF & DayBinVal > 0
/*********************/
I know this leaves out a bunch of what the post was getting at, but thought I'd pass this along in case it's useful.
April 18, 2013 at 6:44 am
Thanks for posting this, I'm pretty sure you've resolved a long standing issue I've had.. For those of us who use Stored Procedures exclusively, this is a great way to get around allowing a complete WHERE clause to be passed in as a single parameter. (<-- big security issue).
April 18, 2013 at 7:20 am
SCGJohn (4/18/2013)
Thanks for posting this, I'm pretty sure you've resolved a long standing issue I've had.. For those of us who use Stored Procedures exclusively, this is a great way to get around allowing a complete WHERE clause to be passed in as a single parameter. (<-- big security issue).
Then you too could benefit from reading Gail's post about this topic. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
Yes the dynamic sql approach is a little more difficult to maintain but the performance difference is well worth it in many scenarios.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 79 total)
You must be logged in to reply to this topic. Login to reply