SQL Server 查询优化
我想在带有许多过滤器的存储过程中编写查询，但我想避免动态 SQL。
假设我的参数可以为空（@filter1、@filter2、@filter3...）。我可能解决此问题的一种方法是：
SELECT col1, col2, col3
FROM table
WHERE col1 = ISNULL(@filter1, col1)
AND col2 = ISNULL(@filter2, col2)
AND col3 = ISNULL(@filter3, col3)
如果结果不为空，则将通过适当的过滤器进行过滤。问题是： 1）这是一个好的做法吗？ 2）优化器会优化 col1 = col1 吗？这会影响查询性能吗？
I want to write a query in a stored proc with many filters but I want to avoid dynamic SQL.
Say my parameters are nullable (@filter1, @filter2, @filter3...). One way I might solve this is:
SELECT col1, col2, col3
FROM table
WHERE col1 = ISNULL(@filter1, col1)
AND col2 = ISNULL(@filter2, col2)
AND col3 = ISNULL(@filter3, col3)
The result of this would filter by the appropriate filters if not null. The question is:
1) Is this good a practice?
2) Will the optimizer optimize the col1 = col1 out or will this affect query performance?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
关于优化条件：您必须意识到，编译后的计划必须满足任何变量值。因此，当生成计划时，SQL Server必须创建一个访问计划，该计划在 @filter1 为 NULL 时有效，并且在 @filter1 不为 NULL 时有效。结果几乎总是扫描。
Tom H. 链接的文章对此进行了详细介绍。
About optimizing the conditions: what you must realize is that a compiled plan has to satisfy any variable value. So when the plan is generated, SQL Server must create an access plan that works when @filter1 is NULL and also works when @filter1 is not NULL. The result is almost always a scan.
The articles linked by Tom H. go into this in much detail.
ISNULL可能会损害索引的使用，所以我不会说它是理想的，但如果您需要上述功能，我不确定是否有解决方法。您能否查看您的执行计划，看看您期望使用的索引是否正在被使用？
ISNULLcan hurt index usage so I wouldn't say it's ideal but if you need the functionality described above i'm not sure there is a way around it.Can you look at your execution plan to see if the index you would expect to be used are being used?
是的，这是一个很好的做法。
有些 RDBMS 会对其进行优化，有些则不会。如果您将其称为准备好的声明，则没有人会这样做。
不要过早优化；对于大多数事情来说，成本差异可以忽略不计，或者如果不是的话，可以通过适当的指数来忽略不计。
集中精力编写能够清楚表达您正在做的事情的代码。在我看来，这个成语是清晰、简洁的。
Yes, it's a good practice.
Some RDBMSes will optimize it out, some won't. None will if you're calling it as a prepared statement.
Don't prematurely optimize; odds are, for most things, the difference in costs will be negligible, or if not, can be made negligible with appropriate indices.
Concentrate on writing code that clearly expresses what you're doing. In my opinion, this idiom is clear and concise.
如果您希望该表增长到任何实质性大小，那么这不是一个好主意，因为查询优化器不会缓存执行计划，并且优化器很难处理此类情况，因为它无法轻松处理在编译时告诉执行路径是什么。
您最好只在客户端生成一个查询，并在 where 子句中使用正确的过滤器，而不是尝试编写一个包罗万象的查询。
If you expect this table to grow to any substantial size this is not a good idea as the query optimizer will not cache the execution plan and the optimizer sucks at dealing with situations like this as it can't easily tell at compile time what the execution path will be.
You would be much better off just generating a query on the client side with the correct filters in the where clause instead of trying to write a single catch-all query.
根据我的经验（通过在大型表上运行一些基准测试）以下：
比： 快得多
In my experience (by running some benchmarks on large tables) the following:
is much faster than: