SQL 存储过程性能 - 巨大的 WHERE 子句
我有一个存储过程，它运行带有巨大 where 子句的查询。 WHERE 子句本身非常简单。看起来
SELECT a, b, c FROM table
WHERE (cond1) OR (cond2) OR (cond3) OR (cond4)
cond1、cond2、cond3 和 cond4 都代表了我们用户的一些需求。
我的问题是关于查询性能：执行 4 个单独的查询（每个查询都具有条件 cond{1..4} 之一）、将结果插入到临时表中，然后最终从该临时表中选择所有内容是否有意义？
我想知道 dbms 是否针对这种情况进行优化。
FWIW，我正在使用 Syabse ASE - TDS 5.5。
谢谢 Harshath
PS：请不要要求我“做我自己的基准测试”。我最终当然会这样做。我真正在寻找指向此类优化内部的链接（如果有）。蒂:)
I have a stored proc that runs a query with a HUGE where clause. In itself, the WHERE clause is very simple. It looks like
SELECT a, b, c FROM table
WHERE (cond1) OR (cond2) OR (cond3) OR (cond4)
where cond1, cond2, cond3 and cond4 all represent some requirement from our users.
My question is regarding the query performance: would it make sense to execute 4 separeate queries (each with one of the conditions cond{1..4}), insert the results into a temporary table, and then finally select everything from that temporary table?
What I'm wondering is, whether dbms' optimize for such situations.
FWIW, i'm using Syabse ASE - TDS 5.5.
Thanks
Harshath
PS: Please don't ask me to "do my own benchmarking". I will of couse be doing that eventually. What i'm really looking for links pointing to the internals of such optimizations, if any. TY :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
分割查询可以更快的原因是可以使用索引来解决较小的单独查询，其中大查询会导致表扫描。
第一个问题是：表有索引吗？如果不是，则始终需要进行表扫描，并且将查询拆分为 N 部分只会导致 N 次表扫描。
如果有索引，您必须测试优化器是否决定使用它们。如果查询优化器已经使用它们，则拆分查询就没有意义。
如果存在索引，并且查询优化器不使用它们，并且您的测试表明使用它们会更快，那么您可以从拆分查询中受益。
The reason split-up queries can be faster is that the smaller separate queries can be resolved using indexes, where the big query results in a table scan.
The first question is: does the table have indexes? If not, a table scan will always be required, and splitting the query into N parts will just cause N table scans.
If there are indexes, you'd have to test if your the optimizer decides to use them. If the query optimizer already uses them, there is no point in splitting the query.
If there are indexes, and the query optimizer does not use them, and your testing shows that it would be faster to use them, you can benefit from splitting the query.
如果将所有内容都包含在单个 WHERE 子句中，至少 DBMS 将有机会对其进行优化。如果您使用单独的查询，那么 DBMS 将无法优化。
此外，在我看来，您正在通过临时表、多个查询以及将所有内容组合在一起来增加大量开销。我无法想象你的第二个选择如何能够更快甚至同样快。
If you include everything in a single WHERE clause, at least the DBMS will have an opportunity to optimize it. If you use separate queries, then the DBMS won't be able to optimize.
Besides, it seems to me you are adding a bunch of overhead with the temporary table, multiple queries, and combining everything together. I can't imagine how your second option could ever be faster or even as fast.
拥有更大的 WHERE 几乎总是比将其分成多个查询然后附加结果更快。
考虑到在多个查询中，您正在为每个查询扫描表 - 这会增加相当大的开销，更不用说可能发生的任何类型的磁盘 IO。最好在内存中有数据时比较需要比较的所有内容，而不是冒着从内存中丢失数据的风险，而不得不将其拉回内存以供下一个查询使用。
Having a larger WHERE will almost always be faster than separating it out into multiple queries and then appending the results.
Consider that in multiple queries are you are scanning the table for each query - which adds considerable overhead, not to mention any kind of disk IO that might happen. It's best to compare all you need to compare while you have the data in memory, than risk losing it from memory only to have to pull it back into memory for the next query.