Compiler Search cost wild char '%%' in a similar article

Question

I have a dynamic stored procedure and need to dynamically add the where expression in the case expression in SQL Server 2008.

My procedure is as follows: -

CREATE PROCEDURE SPGETDATA @STRNAME NVARCHAR(100), @STRCODE NVARCHAR(100) AS BEGIN SELECT myTable.* FROM myTable WHERE IsDELETED = 0 AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '' THEN '%'+ @STRNAME + '%' ELSE '%%' END AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '') THEN '%' + @STRCODE + '%' ELSE '%%' END** END 

The user can select either @strname or @strcode. But not both at the same time.

In this case, one similar operator is fine, but the alternative is always the load on the request, because it will always be like

 @STRNAME like '%%' 

or lower

 @STRCODE like '%%' 

Now, if I use this approach, the compiler will cost some time to search, for example, "%%", is there even nothing suitable or will it bypass it and will not cost anything? I also checked the execution plan, but it does not display anything for a similar sentence.

Therefore, I have to use this in webApps, so sp speed should be considered. And the table has millions of rows.

The execution plan for both options is the same for both. If I use cluase in the query or remove it from the query, it shows - Clustred index sacn 100%.

Please, help.

Answer 1

First, if any of the columns is NULL, then testing col LIKE '%%' not No-Op, but actually equivalent to checking that the column col IS NOT NULL , which may not be the desired effect.

Secondly, if the column is not NULL, therefore it is really No-Op, but this is not a good approach in that SQL Server will not optimize the validation. For more information, see Dynamic Search Conditions in T-SQL .

Most likely, in your case this will not matter much.

Since you always search using the main template for one or the other column, and you do SELECT * , you will probably end up with a full table scan.

In one case, when this could make a difference, there would be a place where you have a narrower index for one or more columns that you can scan, preferring to scan the entire clustered index / table. Even there, however, SQL Server still has to search by the book search method to retrieve * , so it would be possible to evaluate the residual predicate quite cheaply for this particular query.

However, the generated plan would be completely inappropriate for invovcation with a different parameter, so this attempt with respect to the catch request may give you a problem with the sniffing parameter, as shown below.

Test table

 CREATE TABLE myTable ( id int primary key, STRNAME VARCHAR(100) NOT NULL, STRCODE VARCHAR(100) NOT NULL, IsDELETED BIT NOT NULL DEFAULT 0, Filler CHAR(7000) NULL, ) INSERT INTO myTable(id, STRNAME, STRCODE) SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)), ISNULL(name,type), ISNULL(name,type) FROM master..spt_values CREATE INDEX ix ON myTable(STRNAME) 

Call first with a name parameter (scan number 1, logical read 7)

 EXEC sp_executesql N' SELECT myTable.* FROM myTable WHERE IsDELETED = 0 AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN ''%''+ @STRNAME + ''%'' ELSE ''%%'' END AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN ''%'' + @STRCODE + ''%'' ELSE ''%%'' END ', N'@STRNAME NVARCHAR(100), @STRCODE NVARCHAR(100) ', @STRNAME = '(rpc)', @STRCODE='' 

Call using a code parameter that reuses the same plan (scan number 1, logical read 7690)

 EXEC sp_executesql N' SELECT myTable.* FROM myTable WHERE IsDELETED = 0 AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN ''%''+ @STRNAME + ''%'' ELSE ''%%'' END AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN ''%'' + @STRCODE + ''%'' ELSE ''%%'' END ', N'@STRNAME NVARCHAR(100), @STRCODE NVARCHAR(100) ', @STRNAME = '', @STRCODE='(rpc)' 

A call with a code parameter generates a specific plan (scan number 1, logical read 2517)

 EXEC sp_executesql N' SELECT myTable.* FROM myTable WHERE IsDELETED = 0 AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN ''%''+ @STRNAME + ''%'' ELSE ''%%'' END AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN ''%'' + @STRCODE + ''%'' ELSE ''%%'' END OPTION (RECOMPILE) ', N'@STRNAME NVARCHAR(100), @STRCODE NVARCHAR(100) ', @STRNAME = '', @STRCODE='(rpc)' 

Answer 2

You could write something like this:

 IF COALESCE(RTRIM(LTRIM(@STRNAME))), '') <> '' BEGIN SELECT myTable.* FROM myTable WHERE IsDELETED = 0 AND STRNAME LIKE '%'+ @STRNAME + '%' END ELSE -- IF COALESCE(RTRIM(LTRIM(@STRCODE))), '') <> '' BEGIN SELECT myTable.* FROM myTable WHERE IsDELETED = 0 AND STRCODE LIKE '%'+ @STRCODE + '%' END 

In any case, using LIKE does not allow DB to use indexes. This will be your highest cost of execution.

Answer 3

I would create two versions of the query: one with LIKE '%%' , and the other without it, and then compare their execution plans.