|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Using a CASE Statement in WHERE Clausemy selects as follows: SELECT ... FROM TABLE WHERE Week IS NOT NULL AND cat LIKE @Product AND RegionID LIKE @Region AND chnlcd LIKE @Channel AND clutrnm LIKE @Market .... With @Product, @Region, @Channel and @Market being text parameters. In many cases, wildcards ('%') are being supplied for these parameters, which is a performance killer. This can be solved using dynamic SQL but that introduces its own problems. Is it possible to use a CASE statement in the WHERE clause to not do the 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a CASE clause? It's either this or dynamic SQL. Any Ideas? -- Larry Menzin American Techsystems Corp. Use NULLs for the "match all" condition. The optimizer should be smart
enough now to see that "x LIKE x" is always TRUE and not generate a mini-parser which is required for wildcards. SELECT ... FROM Foobar WHERE Week IS NOT NULL AND cat LIKE COALESCE( @product, cat) AND region_id LIKE COALESCE(@my_region, region_id) AND chnl_cd LIKE COALESCE(@rhannel, chnl_cd) AND clutr_nm LIKE COALESCE(@market , clutr_nm); But a better question is why are you allowing free-form input for data elements that have encodings? These should all be equality tests, not LIKE predicates. Larry,
Try this .... AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END AND RegionID LIKE CASE WHEN @Region ='%' THEN RegionID ELSE @Product END .... A "col LIKE col" FILTER should evaluate the same as a "col = col" filter ... try that and see if it helps. -- Alex Show quote "Larry Menzin" wrote: > I have a problem I'm trying to solve for a reporting application with one of > my selects as follows: > > SELECT ... > FROM TABLE > WHERE Week IS NOT NULL > AND cat LIKE @Product > AND RegionID LIKE @Region > AND chnlcd LIKE @Channel > AND clutrnm LIKE @Market > ... > With @Product, @Region, @Channel and @Market being text parameters. In many > cases, wildcards ('%') are being supplied for these parameters, which is a > performance killer. This can be solved using dynamic SQL but that introduces > its own problems. > > Is it possible to use a CASE statement in the WHERE clause to not do the > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > CASE clause? > > It's either this or dynamic SQL. Any Ideas? > > > -- > Larry Menzin > American Techsystems Corp. Larry,
Did you check the execution plan? If SQL Server is doing an index seek when you use "column_name like @s" when @s = '%', what else do you expect from SQL Server? Dynamic Search Conditions in T-SQL http://www.sommarskog.se/dyn-search.html AMB Show quote "Larry Menzin" wrote: > Alex, > > Is there any other way to accomplish the same result without having to > resort to dynamic methods? > > -- > Larry Menzin > American Techsystems Corp. > > > "Alex Papadimoulis" wrote: > > > > > Yeah good point. Shoulda tried it first (thought it makes sense that it > > drops it). > > > > Note the different behavior when using an indexed and non indexed conditions > > ... > > > > where > > Address like case when @s = N'%' then Address else @s end > > and Region IS NULL > > > > It applies the filter the same: > > |--Filter(WHERE:(like([Customers].[Address], If ([@s]='%') then > > [Customers].[Address] else Convert([@s]), NULL))) > > |--Filter(WHERE:(like([Customers].[Address], [@s], NULL))) > > > > So, it would seem the CASE statement is completely pointless. > > > > -- Alex > > > > "Alejandro Mesa" wrote: > > > > > Alex, > > > > > > It seems to be better when you use: > > > > > > column_name like @s > > > > > > than when you use: > > > > > > > AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END > > > > > > I think your solution is not considered as a search argument. > > > > > > Example: > > > > > > use northwind > > > go > > > > > > exec sp_helpindex 'dbo.customers' > > > go > > > > > > set showplan_text on > > > go > > > > > > declare @s nvarchar(40) > > > > > > set @s = N'%' > > > > > > select > > > CustomerID, > > > CompanyName, > > > Country, > > > City, > > > Phone > > > from > > > dbo.Customers > > > where > > > CompanyName like case when @s = N'%' then CompanyName else @s end > > > > > > select > > > CustomerID, > > > CompanyName, > > > Country, > > > City, > > > Phone > > > from > > > dbo.Customers > > > where > > > CompanyName like @s > > > go > > > > > > set showplan_text off > > > go > > > > > > > > > AMB > > > > > > "Alex Papadimoulis" wrote: > > > > > > > Larry, > > > > > > > > Try this > > > > ... > > > > AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END > > > > AND RegionID LIKE CASE WHEN @Region ='%' THEN RegionID ELSE @Product END > > > > ... > > > > A "col LIKE col" FILTER should evaluate the same as a "col = col" filter ... > > > > try that and see if it helps. > > > > > > > > -- Alex > > > > > > > > "Larry Menzin" wrote: > > > > > > > > > I have a problem I'm trying to solve for a reporting application with one of > > > > > my selects as follows: > > > > > > > > > > SELECT ... > > > > > FROM TABLE > > > > > WHERE Week IS NOT NULL > > > > > AND cat LIKE @Product > > > > > AND RegionID LIKE @Region > > > > > AND chnlcd LIKE @Channel > > > > > AND clutrnm LIKE @Market > > > > > ... > > > > > With @Product, @Region, @Channel and @Market being text parameters. In many > > > > > cases, wildcards ('%') are being supplied for these parameters, which is a > > > > > performance killer. This can be solved using dynamic SQL but that introduces > > > > > its own problems. > > > > > > > > > > Is it possible to use a CASE statement in the WHERE clause to not do the > > > > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > > > > > CASE clause? > > > > > > > > > > It's either this or dynamic SQL. Any Ideas? > > > > > > > > > > > > > > > -- > > > > > Larry Menzin > > > > > American Techsystems Corp. Yeah good point. Shoulda tried it first (thought it makes sense that it
drops it). Note the different behavior when using an indexed and non indexed conditions .... where Address like case when @s = N'%' then Address else @s end and Region IS NULL It applies the filter the same: |--Filter(WHERE:(like([Customers].[Address], If ([@s]='%') then [Customers].[Address] else Convert([@s]), NULL))) |--Filter(WHERE:(like([Customers].[Address], [@s], NULL))) So, it would seem the CASE statement is completely pointless.-- Alex Show quote "Alejandro Mesa" wrote: > Alex, > > It seems to be better when you use: > > column_name like @s > > than when you use: > > > AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END > > I think your solution is not considered as a search argument. > > Example: > > use northwind > go > > exec sp_helpindex 'dbo.customers' > go > > set showplan_text on > go > > declare @s nvarchar(40) > > set @s = N'%' > > select > CustomerID, > CompanyName, > Country, > City, > Phone > from > dbo.Customers > where > CompanyName like case when @s = N'%' then CompanyName else @s end > > select > CustomerID, > CompanyName, > Country, > City, > Phone > from > dbo.Customers > where > CompanyName like @s > go > > set showplan_text off > go > > > AMB > > "Alex Papadimoulis" wrote: > > > Larry, > > > > Try this > > ... > > AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END > > AND RegionID LIKE CASE WHEN @Region ='%' THEN RegionID ELSE @Product END > > ... > > A "col LIKE col" FILTER should evaluate the same as a "col = col" filter ... > > try that and see if it helps. > > > > -- Alex > > > > "Larry Menzin" wrote: > > > > > I have a problem I'm trying to solve for a reporting application with one of > > > my selects as follows: > > > > > > SELECT ... > > > FROM TABLE > > > WHERE Week IS NOT NULL > > > AND cat LIKE @Product > > > AND RegionID LIKE @Region > > > AND chnlcd LIKE @Channel > > > AND clutrnm LIKE @Market > > > ... > > > With @Product, @Region, @Channel and @Market being text parameters. In many > > > cases, wildcards ('%') are being supplied for these parameters, which is a > > > performance killer. This can be solved using dynamic SQL but that introduces > > > its own problems. > > > > > > Is it possible to use a CASE statement in the WHERE clause to not do the > > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > > > CASE clause? > > > > > > It's either this or dynamic SQL. Any Ideas? > > > > > > > > > -- > > > Larry Menzin > > > American Techsystems Corp. Alex,
It seems to be better when you use: column_name like @s than when you use: > AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END I think your solution is not considered as a search argument.Example: use northwind go exec sp_helpindex 'dbo.customers' go set showplan_text on go declare @s nvarchar(40) set @s = N'%' select CustomerID, CompanyName, Country, City, Phone from dbo.Customers where CompanyName like case when @s = N'%' then CompanyName else @s end select CustomerID, CompanyName, Country, City, Phone from dbo.Customers where CompanyName like @s go set showplan_text off go AMB Show quote "Alex Papadimoulis" wrote: > Larry, > > Try this > ... > AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END > AND RegionID LIKE CASE WHEN @Region ='%' THEN RegionID ELSE @Product END > ... > A "col LIKE col" FILTER should evaluate the same as a "col = col" filter ... > try that and see if it helps. > > -- Alex > > "Larry Menzin" wrote: > > > I have a problem I'm trying to solve for a reporting application with one of > > my selects as follows: > > > > SELECT ... > > FROM TABLE > > WHERE Week IS NOT NULL > > AND cat LIKE @Product > > AND RegionID LIKE @Region > > AND chnlcd LIKE @Channel > > AND clutrnm LIKE @Market > > ... > > With @Product, @Region, @Channel and @Market being text parameters. In many > > cases, wildcards ('%') are being supplied for these parameters, which is a > > performance killer. This can be solved using dynamic SQL but that introduces > > its own problems. > > > > Is it possible to use a CASE statement in the WHERE clause to not do the > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > > CASE clause? > > > > It's either this or dynamic SQL. Any Ideas? > > > > > > -- > > Larry Menzin > > American Techsystems Corp. Alex,
Is there any other way to accomplish the same result without having to resort to dynamic methods? -- Show quoteLarry Menzin American Techsystems Corp. "Alex Papadimoulis" wrote: > > Yeah good point. Shoulda tried it first (thought it makes sense that it > drops it). > > Note the different behavior when using an indexed and non indexed conditions > ... > > where > Address like case when @s = N'%' then Address else @s end > and Region IS NULL > > It applies the filter the same: > |--Filter(WHERE:(like([Customers].[Address], If ([@s]='%') then > [Customers].[Address] else Convert([@s]), NULL))) > |--Filter(WHERE:(like([Customers].[Address], [@s], NULL))) > > So, it would seem the CASE statement is completely pointless. > > -- Alex > > "Alejandro Mesa" wrote: > > > Alex, > > > > It seems to be better when you use: > > > > column_name like @s > > > > than when you use: > > > > > AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END > > > > I think your solution is not considered as a search argument. > > > > Example: > > > > use northwind > > go > > > > exec sp_helpindex 'dbo.customers' > > go > > > > set showplan_text on > > go > > > > declare @s nvarchar(40) > > > > set @s = N'%' > > > > select > > CustomerID, > > CompanyName, > > Country, > > City, > > Phone > > from > > dbo.Customers > > where > > CompanyName like case when @s = N'%' then CompanyName else @s end > > > > select > > CustomerID, > > CompanyName, > > Country, > > City, > > Phone > > from > > dbo.Customers > > where > > CompanyName like @s > > go > > > > set showplan_text off > > go > > > > > > AMB > > > > "Alex Papadimoulis" wrote: > > > > > Larry, > > > > > > Try this > > > ... > > > AND cat LIKE CASE WHEN @Product '%' THEN cat ELSE @Product END > > > AND RegionID LIKE CASE WHEN @Region ='%' THEN RegionID ELSE @Product END > > > ... > > > A "col LIKE col" FILTER should evaluate the same as a "col = col" filter ... > > > try that and see if it helps. > > > > > > -- Alex > > > > > > "Larry Menzin" wrote: > > > > > > > I have a problem I'm trying to solve for a reporting application with one of > > > > my selects as follows: > > > > > > > > SELECT ... > > > > FROM TABLE > > > > WHERE Week IS NOT NULL > > > > AND cat LIKE @Product > > > > AND RegionID LIKE @Region > > > > AND chnlcd LIKE @Channel > > > > AND clutrnm LIKE @Market > > > > ... > > > > With @Product, @Region, @Channel and @Market being text parameters. In many > > > > cases, wildcards ('%') are being supplied for these parameters, which is a > > > > performance killer. This can be solved using dynamic SQL but that introduces > > > > its own problems. > > > > > > > > Is it possible to use a CASE statement in the WHERE clause to not do the > > > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > > > > CASE clause? > > > > > > > > It's either this or dynamic SQL. Any Ideas? > > > > > > > > > > > > -- > > > > Larry Menzin > > > > American Techsystems Corp. Larry,
At the beginning of your stored Proc, why not just modify the values passed in if they start with a % -to strip off the % - (I'd add a note in the UI to users about this of course...) If Left(@Product, 1) = '%' Set @Product = Substring(@Product , 2, Len(@Product) - 1) If Left(@Region , 1) = '%' Set @Region = Substring(@Region , 2, Len(@Region ) - 1) etc... Show quote "Larry Menzin" wrote: > I have a problem I'm trying to solve for a reporting application with one of > my selects as follows: > > SELECT ... > FROM TABLE > WHERE Week IS NOT NULL > AND cat LIKE @Product > AND RegionID LIKE @Region > AND chnlcd LIKE @Channel > AND clutrnm LIKE @Market > ... > With @Product, @Region, @Channel and @Market being text parameters. In many > cases, wildcards ('%') are being supplied for these parameters, which is a > performance killer. This can be solved using dynamic SQL but that introduces > its own problems. > > Is it possible to use a CASE statement in the WHERE clause to not do the > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > CASE clause? > > It's either this or dynamic SQL. Any Ideas? > > > -- > Larry Menzin > American Techsystems Corp. CBretana,
My problem is that my client's front end passes in '%' which means all cases. When this happens I want to remove the WHERE clause for that parameter since it is not needed and wildcards kill performance. I still cannot find a method to do this that doesn't use dynamic SQL, which has a lot of drawbacks for this project. -- Show quoteLarry Menzin American Techsystems Corp. "CBretana" wrote: > Larry, > > At the beginning of your stored Proc, why not just modify the values passed > in if they start with a % -to strip off the % - (I'd add a note in the UI to > users about this of course...) > > If Left(@Product, 1) = '%' > Set @Product = > Substring(@Product , 2, Len(@Product) - 1) > If Left(@Region , 1) = '%' > Set @Region = > Substring(@Region , 2, Len(@Region ) - 1) > etc... > > > "Larry Menzin" wrote: > > > I have a problem I'm trying to solve for a reporting application with one of > > my selects as follows: > > > > SELECT ... > > FROM TABLE > > WHERE Week IS NOT NULL > > AND cat LIKE @Product > > AND RegionID LIKE @Region > > AND chnlcd LIKE @Channel > > AND clutrnm LIKE @Market > > ... > > With @Product, @Region, @Channel and @Market being text parameters. In many > > cases, wildcards ('%') are being supplied for these parameters, which is a > > performance killer. This can be solved using dynamic SQL but that introduces > > its own problems. > > > > Is it possible to use a CASE statement in the WHERE clause to not do the > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > > CASE clause? > > > > It's either this or dynamic SQL. Any Ideas? > > > > > > -- > > Larry Menzin > > American Techsystems Corp. Larry, by modifying the parameter to remove the leading percent sign, you
will no longer be incurring the performance hit... The performace hit occurs only because the Query Optimizer cannot use an index on the applicable field when the Comparsion operator Starts with a wildcard... Show quote "Larry Menzin" wrote: > CBretana, > > My problem is that my client's front end passes in '%' which means all > cases. When this happens I want to remove the WHERE clause for that parameter > since it is not needed and wildcards kill performance. > > I still cannot find a method to do this that doesn't use dynamic SQL, which > has a lot of drawbacks for this project. > > > -- > Larry Menzin > American Techsystems Corp. > > > "CBretana" wrote: > > > Larry, > > > > At the beginning of your stored Proc, why not just modify the values passed > > in if they start with a % -to strip off the % - (I'd add a note in the UI to > > users about this of course...) > > > > If Left(@Product, 1) = '%' > > Set @Product = > > Substring(@Product , 2, Len(@Product) - 1) > > If Left(@Region , 1) = '%' > > Set @Region = > > Substring(@Region , 2, Len(@Region ) - 1) > > etc... > > > > > > "Larry Menzin" wrote: > > > > > I have a problem I'm trying to solve for a reporting application with one of > > > my selects as follows: > > > > > > SELECT ... > > > FROM TABLE > > > WHERE Week IS NOT NULL > > > AND cat LIKE @Product > > > AND RegionID LIKE @Region > > > AND chnlcd LIKE @Channel > > > AND clutrnm LIKE @Market > > > ... > > > With @Product, @Region, @Channel and @Market being text parameters. In many > > > cases, wildcards ('%') are being supplied for these parameters, which is a > > > performance killer. This can be solved using dynamic SQL but that introduces > > > its own problems. > > > > > > Is it possible to use a CASE statement in the WHERE clause to not do the > > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > > > CASE clause? > > > > > > It's either this or dynamic SQL. Any Ideas? > > > > > > > > > -- > > > Larry Menzin > > > American Techsystems Corp. Let me state the problem again. When the front end wants to see all records,
it sends only a '%' value, not a leading % value in a string. If I strip out the '%' value, all that's left is a zero length string. Where does that help? -- Show quoteLarry Menzin American Techsystems Corp. "CBretana" wrote: > Larry, by modifying the parameter to remove the leading percent sign, you > will no longer be incurring the performance hit... The performace hit occurs > only because the Query Optimizer cannot use an index on the applicable field > when the Comparsion operator Starts with a wildcard... > > "Larry Menzin" wrote: > > > CBretana, > > > > My problem is that my client's front end passes in '%' which means all > > cases. When this happens I want to remove the WHERE clause for that parameter > > since it is not needed and wildcards kill performance. > > > > I still cannot find a method to do this that doesn't use dynamic SQL, which > > has a lot of drawbacks for this project. > > > > > > -- > > Larry Menzin > > American Techsystems Corp. > > > > > > "CBretana" wrote: > > > > > Larry, > > > > > > At the beginning of your stored Proc, why not just modify the values passed > > > in if they start with a % -to strip off the % - (I'd add a note in the UI to > > > users about this of course...) > > > > > > If Left(@Product, 1) = '%' > > > Set @Product = > > > Substring(@Product , 2, Len(@Product) - 1) > > > If Left(@Region , 1) = '%' > > > Set @Region = > > > Substring(@Region , 2, Len(@Region ) - 1) > > > etc... > > > > > > > > > "Larry Menzin" wrote: > > > > > > > I have a problem I'm trying to solve for a reporting application with one of > > > > my selects as follows: > > > > > > > > SELECT ... > > > > FROM TABLE > > > > WHERE Week IS NOT NULL > > > > AND cat LIKE @Product > > > > AND RegionID LIKE @Region > > > > AND chnlcd LIKE @Channel > > > > AND clutrnm LIKE @Market > > > > ... > > > > With @Product, @Region, @Channel and @Market being text parameters. In many > > > > cases, wildcards ('%') are being supplied for these parameters, which is a > > > > performance killer. This can be solved using dynamic SQL but that introduces > > > > its own problems. > > > > > > > > Is it possible to use a CASE statement in the WHERE clause to not do the > > > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > > > > CASE clause? > > > > > > > > It's either this or dynamic SQL. Any Ideas? > > > > > > > > > > > > -- > > > > Larry Menzin > > > > American Techsystems Corp. Oh, Got it, Sorry... Id the ONLY time you get a leading %, the whole
parameter is = '%' then just test for that in a parentheses Or'd with the complete predicate... Where (@Product = '%' Or cat like @Product) And (@Region= ''%' Or RegionID like @Region) And (@Channel = ''%' Or chnlcd like @Channel) And (@Market= ''%' Or clutrnm like @Market) Or if the UI can also send the parameter with just the first character as %, as '%ester'(like I mistakendly assumed), then If you still Strip out the leading %, which will leave an empty string when that's all there is... Modify your Where Clause the same way, put Where (@Product = '' Or cat like @Product) And (@Region= '' Or RegionID like @Region) And (@Channel = '' Or chnlcd like @Channel) And (@Market= '' Or clutrnm like @Market) Show quote "Larry Menzin" wrote: > Let me state the problem again. When the front end wants to see all records, > it sends only a '%' value, not a leading % value in a string. If I strip out > the '%' value, all that's left is a zero length string. Where does that help? > > > -- > Larry Menzin > American Techsystems Corp. > > > "CBretana" wrote: > > > Larry, by modifying the parameter to remove the leading percent sign, you > > will no longer be incurring the performance hit... The performace hit occurs > > only because the Query Optimizer cannot use an index on the applicable field > > when the Comparsion operator Starts with a wildcard... > > > > "Larry Menzin" wrote: > > > > > CBretana, > > > > > > My problem is that my client's front end passes in '%' which means all > > > cases. When this happens I want to remove the WHERE clause for that parameter > > > since it is not needed and wildcards kill performance. > > > > > > I still cannot find a method to do this that doesn't use dynamic SQL, which > > > has a lot of drawbacks for this project. > > > > > > > > > -- > > > Larry Menzin > > > American Techsystems Corp. > > > > > > > > > "CBretana" wrote: > > > > > > > Larry, > > > > > > > > At the beginning of your stored Proc, why not just modify the values passed > > > > in if they start with a % -to strip off the % - (I'd add a note in the UI to > > > > users about this of course...) > > > > > > > > If Left(@Product, 1) = '%' > > > > Set @Product = > > > > Substring(@Product , 2, Len(@Product) - 1) > > > > If Left(@Region , 1) = '%' > > > > Set @Region = > > > > Substring(@Region , 2, Len(@Region ) - 1) > > > > etc... > > > > > > > > > > > > "Larry Menzin" wrote: > > > > > > > > > I have a problem I'm trying to solve for a reporting application with one of > > > > > my selects as follows: > > > > > > > > > > SELECT ... > > > > > FROM TABLE > > > > > WHERE Week IS NOT NULL > > > > > AND cat LIKE @Product > > > > > AND RegionID LIKE @Region > > > > > AND chnlcd LIKE @Channel > > > > > AND clutrnm LIKE @Market > > > > > ... > > > > > With @Product, @Region, @Channel and @Market being text parameters. In many > > > > > cases, wildcards ('%') are being supplied for these parameters, which is a > > > > > performance killer. This can be solved using dynamic SQL but that introduces > > > > > its own problems. > > > > > > > > > > Is it possible to use a CASE statement in the WHERE clause to not do the > > > > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it through a > > > > > CASE clause? > > > > > > > > > > It's either this or dynamic SQL. Any Ideas? > > > > > > > > > > > > > > > -- > > > > > Larry Menzin > > > > > American Techsystems Corp. I have run into a variant of this problem as well. Beyond the ideas mentioned,
you might also use the Profiler to analyze common combinations of search parameters and fork the code to specialized versions of the query with the last fork including all parameters. For example, suppose you find that 50% of the queries pass a regionId and a category only. Then you might do something like: If @Channel = '%' And @Market = '%' And @Region <> '%' And @Product <> '%' ...query designed around these two parameters... Else ..the full selection statement with all Like parameters Granted, you'll probably want to set the With Recompile option as each set of parameters may produce a vastly different execution plan. However, you should get somewhat improved overall performance this way. Thomas Show quote "Larry Menzin" <LarryMen***@discussions.microsoft.com> wrote in message news:DFB75FD5-2DFB-4DFD-8165-13CB863538CF@microsoft.com... > Let me state the problem again. When the front end wants to see all records, > it sends only a '%' value, not a leading % value in a string. If I strip out > the '%' value, all that's left is a zero length string. Where does that help? > > > -- > Larry Menzin > American Techsystems Corp. > > > "CBretana" wrote: > >> Larry, by modifying the parameter to remove the leading percent sign, you >> will no longer be incurring the performance hit... The performace hit occurs >> only because the Query Optimizer cannot use an index on the applicable field >> when the Comparsion operator Starts with a wildcard... >> >> "Larry Menzin" wrote: >> >> > CBretana, >> > >> > My problem is that my client's front end passes in '%' which means all >> > cases. When this happens I want to remove the WHERE clause for that >> > parameter >> > since it is not needed and wildcards kill performance. >> > >> > I still cannot find a method to do this that doesn't use dynamic SQL, which >> > has a lot of drawbacks for this project. >> > >> > >> > -- >> > Larry Menzin >> > American Techsystems Corp. >> > >> > >> > "CBretana" wrote: >> > >> > > Larry, >> > > >> > > At the beginning of your stored Proc, why not just modify the values >> > > passed >> > > in if they start with a % -to strip off the % - (I'd add a note in the >> > > UI to >> > > users about this of course...) >> > > >> > > If Left(@Product, 1) = '%' >> > > Set @Product = >> > > Substring(@Product , 2, Len(@Product) - 1) >> > > If Left(@Region , 1) = '%' >> > > Set @Region = >> > > Substring(@Region , 2, Len(@Region ) - 1) >> > > etc... >> > > >> > > >> > > "Larry Menzin" wrote: >> > > >> > > > I have a problem I'm trying to solve for a reporting application with >> > > > one of >> > > > my selects as follows: >> > > > >> > > > SELECT ... >> > > > FROM TABLE >> > > > WHERE Week IS NOT NULL >> > > > AND cat LIKE @Product >> > > > AND RegionID LIKE @Region >> > > > AND chnlcd LIKE @Channel >> > > > AND clutrnm LIKE @Market >> > > > ... >> > > > With @Product, @Region, @Channel and @Market being text parameters. In >> > > > many >> > > > cases, wildcards ('%') are being supplied for these parameters, which >> > > > is a >> > > > performance killer. This can be solved using dynamic SQL but that >> > > > introduces >> > > > its own problems. >> > > > >> > > > Is it possible to use a CASE statement in the WHERE clause to not do >> > > > the >> > > > 'cat LIKE @Product' construct when @Product = '%', i.e. to skip it >> > > > through a >> > > > CASE clause? >> > > > >> > > > It's either this or dynamic SQL. Any Ideas? >> > > > >> > > > >> > > > -- >> > > > Larry Menzin >> > > > American Techsystems Corp. On Tue, 17 May 2005 07:44:08 -0700, Larry Menzin wrote:
(snip) >It's either this or dynamic SQL. Any Ideas? Hi Larry,Some other options are here: http://www.sommarskog.se/dyn-search.html Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics