在like子句中搜索野生字符'%%'的编译器成本 [英] Compiler Cost of searching wild char '%%' in like clause

问题描述

我有一个动态存储过程，必须在SQL Server 2008中的case语句中动态添加where子句.

我的程序如下:-

I have a dynamic stored procedure and have to add where clause dynamically in case statement in SQL Server 2008.

My procedure is as below: -

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

用户可以选择@strname或@strcode.但不是一次都做.

在这种情况下，可以使用一个类似的语句，但是替代方案始终是查询的负担，因为它始终是

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

In that case one like statement is ok but the alternative is always a burden over query because it will always be as

@STRNAME like '%%'

或如下

or as below

@STRCODE like '%%'

现在，如果我使用这种方法，即使没有匹配的内容，编译器也将花费一些时间来搜索"%%"，否则它将绕过它并且不花费任何费用.我也检查了执行计划，但like子句什么也不显示.
因此，我必须在webApps中使用此功能，因此必须考虑sp的速度.该表有数百万行.

有谁能帮忙吗?

Now if i use this approach, will compiler will cost some time to search like ''%%'' even there is nothing to match or it will bypass it and cost nothing. I checked the execution plan also but it displays nothing for the like clause.
Hence i have to use this in webApps so speed of the sp has to consider. And the table has millions of rows.

Any one who can help please?
Please help?

推荐答案

如何将AND替换为OR?

从:

How about replacing AND with OR?

FROM:

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**

收件人:

TO:

WHERE
    IsDELETED = 0
    AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '' THEN
    '%'+ @STRNAME + '%' ELSE '%%' END
    OR STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '') THEN
    '%' + @STRCODE + '%'  ELSE '%%' END**

问候，
爱德华(Eduard)

Regards,
Eduard

这是一篇关于构建具有多个参数的搜索"类型过程的非常好的文章

http://www.sommarskog.se/dyn-search-2005.html [ ^ ]

我通常按​​照search_orders_1(动态SQL)
构建搜索
例如

http://www.sommarskog.se/dynsearch/search_orders_1.sp [

This is a really nice article on building ''Search'' type procedures where you have multiple parameters

http://www.sommarskog.se/dyn-search-2005.html[^]

I normally build my searches along the line of search_orders_1 (dynamic SQL)

e.g.

http://www.sommarskog.se/dynsearch/search_orders_1.sp[^]

Personally, I find this gives the best performance and maintainability - but there are various options that are documented there.

这篇关于在like子句中搜索野生字符'%%'的编译器成本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！