November 27, 2012 at 9:34 am
There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.
November 27, 2012 at 11:13 am
pdanes (11/27/2012)
There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.
You're thinking procedurally. SQL Server has to generate an execution plan that can take any value or none for any given parameter. It's not a runtime environment, like .NET or JAVA, it's a "declarative language". So when you say, "The variable can be this or can be that", it has to build a single plan that handles both of those correctly. To the optimizer, "correct" is more important than "efficient", so if you give it a query that can have wildly different "most efficient means to execute", you end up with a plan that has to cover all eventualities, and thus will be less efficient.
There are ways to work around that. Various flavors of parameterized dynamic SQL, nested procs, With Recompile, etc., are all popular and effective methods of essentially generating a different query for different parameter combinations.
I tend to use dynamic SQL for that kind of thing.
DECLARE @sql NVARCHAR(max) = '
SELECT *
FROM dbo.MyTable
WHERE 1 = 1';
IF @Parameter1 IS NOT NULL
SET @sql += '
AND MyColumn = @Parameter1';
EXEC sp_executeSQL @sql, '@Parameter1 INT', @Parameter1;
That kind of thing. Don't actually build the values into the string, pass them in as parameters.
Then, if a parameter value is null or blank, it simply doesn't get added to the Where clause, and you end up with a highly optimizable query, containing only those criteria that actually matter at runtime. No over-broad execution plans because of CASE or OR in Where.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 27, 2012 at 4:09 pm
GSquared (11/27/2012)
pdanes (11/27/2012)
There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.I tend to use dynamic SQL for that kind of thing.
I've been a little paranoid about dynamic SQL since reading all the stories about injection attacks, but this method of building parameters looks safe enough. I'll try that as well - thanks for the sample code.
November 27, 2012 at 4:46 pm
pdanes (11/27/2012)
GSquared (11/27/2012)
pdanes (11/27/2012)
There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.I tend to use dynamic SQL for that kind of thing.
I've been a little paranoid about dynamic SQL since reading all the stories about injection attacks, but this method of building parameters looks safe enough. I'll try that as well - thanks for the sample code.
i found http://www.sommarskog.se/dynamic_sql.html to be very helpful to my understanding of dynamic SQL. After reading that article several times i am confident i can write injection free code.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 28, 2012 at 6:36 am
pdanes (11/27/2012)
GSquared (11/27/2012)
pdanes (11/27/2012)
There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.I tend to use dynamic SQL for that kind of thing.
I've been a little paranoid about dynamic SQL since reading all the stories about injection attacks, but this method of building parameters looks safe enough. I'll try that as well - thanks for the sample code.
Injection-safety is a large part of why MS added sp_executeSQL to SQL Server. Much safer than Exec(), since strings are passed in as values instead of executed directly.
No way to inject with this method.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2012 at 2:52 am
capnhector (11/27/2012)
pdanes (11/27/2012)
GSquared (11/27/2012)
pdanes (11/27/2012)
There doesn't seem to be any clear consensus on all this, so I guess I'll have to build a bunch of test cases and see what comes of it. Still seems to me that the SS engine ought to know that testing a single parameter value is cheaper than looking through a table, and should try that first, but maybe I'm expecting too much of it (for once). Thank you everyone for the input.I tend to use dynamic SQL for that kind of thing.
I've been a little paranoid about dynamic SQL since reading all the stories about injection attacks, but this method of building parameters looks safe enough. I'll try that as well - thanks for the sample code.
i found http://www.sommarskog.se/dynamic_sql.html to be very helpful to my understanding of dynamic SQL. After reading that article several times i am confident i can write injection free code.
Thanks, Captain, that's an excellent article. Bookmarked.
November 30, 2012 at 6:24 am
I'm working on the syntax of dynamic SQL with parameters, but I find myself coming back to the same question that actually started this thread: If I use the predicate 'LIKE' with a parameter that has no wildcard characters, am I unnecessarily slowing the engine down?
If it's makes a signifcant speed difference to distinguish between the two, I will have to write something like (aircode)
If @Param1 contains AnyWildCardCharacter
@dsql = @dsql + 'Field1 Like @Param1'
Else
@dsql = @dsql + 'Field1 = @Param1'
for every passed parameter, after first testing to see if it contains anything at all. This will make the code longer and messier. I tried some tests, but can't tell anything from them. The popup shows the predicate I specify, the plan shows an index scan, but SSMSE includes a suggestion that I create such an index anyway.
Does it make sense to worry about this, or should I just specify all parameters with 'Like' and forget about trying to improve performance here?
November 30, 2012 at 8:31 am
pdanes (11/30/2012)
I'm working on the syntax of dynamic SQL with parameters, but I find myself coming back to the same question that actually started this thread: If I use the predicate 'LIKE' with a parameter that has no wildcard characters, am I unnecessarily slowing the engine down?If it's makes a signifcant speed difference to distinguish between the two, I will have to write something like (aircode)
If @Param1 contains AnyWildCardCharacter
@dsql = @dsql + 'Field1 Like @Param1'
Else
@dsql = @dsql + 'Field1 = @Param1'
for every passed parameter, after first testing to see if it contains anything at all. This will make the code longer and messier. I tried some tests, but can't tell anything from them. The popup shows the predicate I specify, the plan shows an index scan, but SSMSE includes a suggestion that I create such an index anyway.
Does it make sense to worry about this, or should I just specify all parameters with 'Like' and forget about trying to improve performance here?
I'd have to see the specific code as-written, before I could suggest improvements.
Is it slow enough that users have a problem with it? I gather it's infrequently used, so the overall hit on the server should be minimal even if it's pretty slow and expensive. Thus, the only real reason to worry about tuning is does it impact users negatively.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2012 at 9:17 am
GSquared (11/30/2012)
pdanes (11/30/2012)
I'm working on the syntax of dynamic SQL with parameters, but I find myself coming back to the same question that actually started this thread: If I use the predicate 'LIKE' with a parameter that has no wildcard characters, am I unnecessarily slowing the engine down?If it's makes a signifcant speed difference to distinguish between the two, I will have to write something like (aircode)
If @Param1 contains AnyWildCardCharacter
@dsql = @dsql + 'Field1 Like @Param1'
Else
@dsql = @dsql + 'Field1 = @Param1'
for every passed parameter, after first testing to see if it contains anything at all. This will make the code longer and messier. I tried some tests, but can't tell anything from them. The popup shows the predicate I specify, the plan shows an index scan, but SSMSE includes a suggestion that I create such an index anyway.
Does it make sense to worry about this, or should I just specify all parameters with 'Like' and forget about trying to improve performance here?
I'd have to see the specific code as-written, before I could suggest improvements.
That's sensible enough, but I was trying to avoid writing a bunch of bad code. I assume you don't want the entire SP anyway - it's several hundred lines long. But here's a sample of what I have now:
if @Series <> ''
set @WhereClause = @WhereClause + Case When @WhereClause = '' Then '' Else ' AND ' End + 'tSe.Series Like @Series'
if @Stage <> ''
set @WhereClause = @WhereClause + Case When @WhereClause = '' Then '' Else ' AND ' End + 'tSe.Stage Like @Stage'
What I would have to do is modify each such statement to something like this:
if @Stage <> ''
set @WhereClause = @WhereClause + Case When @WhereClause = '' Then '' Else ' AND ' End + 'tSe.Stage ' + Case charindex('%',@Stage) > 0 Or CHARINDEX('_',@Stage) > 0 Then 'Like' Else '=' End + ' @Stage'
And looking at it now, I'm not even sure if that's enough. I might want to check for brackets as well, or maybe only matched brackets, or who knows what, come to think of it. To be completely accurate, I might have to build a miniature regular expression recognition function, to be sure that I captured every possibility. Hmm, this is starting to look like a major pain in the fundament. I think I'll shine it for now, just code it with 'LIKE' and see how it works.
Is it slow enough that users have a problem with it? I gather it's infrequently used, so the overall hit on the server should be minimal even if it's pretty slow and expensive. Thus, the only real reason to worry about tuning is does it impact users negatively.
It was slow enough to be somewhat irritating, but not really a problem, and you're right, it doesn't get used all that much. I'm just kind of a nit-picker. My general attitude is that good enough isn't good enough, but sometimes you have to be realistic about when you're wasting effort that could be better expended elsewhere. This is starting to look like one of those cases.
Thanks for the input. I'm going to continue with the strategy of building special routines for frequently used combinations, leave this as a last-resort fallback and just not worry about the speed and efficiency.
December 3, 2012 at 6:03 am
There's no need to convert Like to = if it doesn't include any wildcards. Just use Like. SQL Server will treat it as an equality test if the input doesn't have any wildcards in it. Let the computer do the work for you.
For a shortcut on the Where clause, try this:
DECLARE @Where NVARCHAR(max) = 'WHERE 1=1'
IF @Parameter > ''
SET @Where += '
AND AppropriateColumn LIKE @Parameter'
IF @Parameter2 > ''
SET @Where += '
AND AppropriateColumn LIKE @Parameter2'
Simplifies the string construction. You can begin each with "AND" by default, because you start the whole thing with "1=1". The query optimizer will ignore the 1=1 part, so it doesn't have any negative impact.
No real difference, just easier to maintain and read.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 3, 2012 at 9:38 am
GSquared (12/3/2012)
There's no need to convert Like to = if it doesn't include any wildcards. Just use Like. SQL Server will treat it as an equality test if the input doesn't have any wildcards in it. Let the computer do the work for you.
BINGO! That's what I was hoping for, and hadn't been able to figure out myself.
For a shortcut on the Where clause, try this:...
That's a good trick. You're right, that will make the code considerably less messy. Another one for the toolbox.
Many thanks, I'm clear on how I want to build this now.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply