代码之家 › 专栏 › 技术社区 › Macros

最佳搜索查询

tsql sql-server

Macros · 技术社区 · 15 年前

从上一个问题开始 Sql Server query performance 并且发现我在搜索查询中允许可选参数的方法是次优的,有人对如何处理这个问题有指导意见吗?

例如,假设我有一个应用程序表、一个客户表和一个联系人详细信息表,并且我想创建一个允许搜索某些姓氏、无姓氏或全部姓氏、家庭电话、手机和应用程序ID的SP,我可以使用如下内容:

select *
from application a inner join customer c on a.customerid = a.id
    left join contact hp on (c.id = hp.customerid and hp.contacttype = 'homephone')
    left join contact mob on (c.id = mob.customerid and mob.contacttype = 'mobile')
where (a.ID = @ID or @ID is null)
    and (c.Surname = @Surname or @Surname is null)
    and (HP.phonenumber = @Homphone or @Homephone is null)
    and (MOB.phonenumber = @Mobile or @Mobile is null)

上面使用的模式不是真实的,我不会在真实场景中使用select*,它是我感兴趣的where子句的构造。是否有更好的方法,无论是动态SQL,还是可以实现相同结果的替代方法,而不需要许多嵌套条件。一些SP可能会有10-15个标准用于这种方式。

3 回复 | 直到 15 年前

Community CDub 8 年前

对于这种情况,没有“一刀切”的查询方法,在如何做到这一点上会有细微的性能影响。如果您不只是想让查询返回正确的答案,不管速度有多慢,请看本文: Dynamic Search Conditions in T-SQL by Erland Sommarskog . 它涵盖了每一种方法,并给出了每一种方法的优缺点。

如果可以确定搜索列的最小和最大可能范围,并且搜索列不为空,则可以做得比(@search为空或col=@search)更好, see this area of the above linked article . 然而,您应该阅读整篇文章,根据您的情况,有很多变化,您真的需要学习多种方法以及何时使用它们。

另请参阅其他最近的答案: SQL Server 2008 - Conditional Query

TomTom 15 年前

好的,我们开始吧

选项(重新编译)

是必须的,否则将重用第一个查询计划,无论参数如何匹配。抱歉,没有真正的方法能做得更好。

除此之外-不,对不起。动态SQL可以变得更高效(通过避免IS空选项),但如果不可能的话,您基本上已经确定了它。

对于动态SQL,如果homephone变量为空,则基本上没有针对hp.phonenumber的aline;)

Quassnoi 15 年前

在您的例子中,不同的查询将使用不同的索引。

您应该定义一组要使用的索引,并为每个集合编写一个单独的查询,替换 OR 在索引字段上 UNION ALL :

SELECT  *
FROM    tables
WHERE   A = @ID
        AND (c.Surname = @Surname or @Surname IS NULL)
        AND (HP.phonenumber = @Homphone or @Homephone IS NULL)
        AND (MOB.phonenumber = @Mobile or @Mobile IS NULL)
UNION ALL
SELECT  *
FROM    tables
WHERE   @ID IS NULL
        AND c.Surname = @Surname
        AND (HP.phonenumber = @Homphone or @Homephone IS NULL)
        AND (MOB.phonenumber = @Mobile or @Mobile IS NULL)
UNION ALL
SELECT  *
FROM    tables
WHERE   @ID IS NULL
        AND @Surname IS NULL
        AND (HP.phonenumber = @Homphone or @Homephone IS NULL)
        AND (MOB.phonenumber = @Mobile or @Mobile IS NULL)