最佳搜索查询 [英] Optimal search queries

问题描述

继我的上一个问题 Sql Server 查询性能之后，发现我的方法在搜索查询中允许可选参数是次优的，有没有人有关于如何解决这个问题的指南?

Following on from my last question Sql Server query performance, and discovering that my method of allowing optional parameters in a search query is sub optimal, does anyone have guidelines on how to approach this?

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

For example, say I have an application table, a customer table and a contact details table, and I want to create an SP which allows searching on some, none or all of surname, homephone, mobile and app ID, I may use something like the following:

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 个这样使用的标准

The schema used above isn't real, and I wouldn't be using select * in a real world scenario, it is the construction of the where clause I am interested in. Is there a better approach, either dynamic sql or an alternative which can achieve the same result, without the need for many nested conditionals. Some SPs may have 10 - 15 criteria used in this way

推荐答案

对此没有一刀切"的查询方法，执行此操作的方式对性能有微妙的影响.如果您想不仅仅是让查询返回正确的答案，无论它有多慢，请查看这篇文章:Erland Sommarskog 在 T-SQL 中的动态搜索条件.它涵盖了每种方法，并非常详细地给出了每种方法的优点和缺点.

There is no "one size fits all" query approach for this, there are subtle performance implications in how you do this. If you would like to go beyond just making the query return the proper answer, no matter how slow it is, look at this article: Dynamic Search Conditions in T-SQL by Erland Sommarskog. It covers every method and gives PROs and Cons of each method in great detail.

如果您可以确定搜索列的最小和最大可能范围，并且搜索列不是 NULL，那么您可以做得比 (@Search IS NULL OR Col=@Search), 查看上述链接文章的这一部分.但是，您应该阅读整篇文章，根据您的情况有很多变化，您确实需要学习多种方法以及何时使用它们.

If you can determine a min and a max possible range for your search column, and the search column is NOT NULL, then you can do better than the (@Search IS NULL OR Col=@Search), see this area of the above linked article. However you should read the entire article, there are so many variations that depend on your situation, you really need to learn multiple approaches and when to use them.

另请参阅最近的另一个答案:SQL Server 2008 - 条件查询

Also see this other recent answer: SQL Server 2008 - Conditional Query

这篇关于最佳搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！