英文:
How to page records, get the total number of records, yet stay performant?
问题
我正在尝试提高一个复杂存储过程的性能。瓶颈在于这个巨大的查询，包含超过30个连接（大多数是左连接），以及WHERE语句中荒谬的条件数量。此外，SELECT部分包含对多个执行重要工作的函数的调用。
伪代码示例：
select fn_DoWork1(caseID, orderID) as cln1,
fn_DoWork2(caseID, orderID) as cln2,
... 其他20列
BalanceDue
from tbl1
left join tbl2 on ...
...
left join tbl30 on ...
where
(tbl10.ArrivalDate between @foo1 and @foo2)
...
(@prmProcessingID = 0 OR tbl10.ProcessingID = @prmProcessingID)
你明白了吧。我将查询拆分成较小的部分，并在途中将数据存入临时表，以便每个连续的查询都可以处理更小的子集。但是，最终仍然匹配了数千条记录，性能并没有像我希望的那样得到改善。
我的下一个想法是一次只返回一页的记录（每页20条记录），因为用户只需要看到这么多。所以我添加了 OFFSET x ROWS 和 FETCH NEXT 20 ROWS ONLY，这解决了性能问题。
然而，现在的问题是，我只返回了20行，但我不知道有多少行满足总体条件。因此，我无法告诉用户有多少页的数据 - 例如，我无法在用户界面上正确地渲染分页器。
有没有更好的方法来解决这个问题？
英文:
I am trying to improve performance of a complex stored procedure. The bottleneck is this giant query with over 30 joins (most of them left) and a ridiculous amount of criteria in the WHERE statement. In addition, the SELECT portion contains calls to multiple functions that do significant work as well.
Pseudo Example:
select fn_DoWork1(caseID, orderID) as cln1,
fn_DoWork2(caseID, orderID) as cln2,
... 20 other columns
BalanceDue
from tbl1
left join tbl2 on ...
...
left join tbl30 on ...
where
(tbl10.ArrivalDate between @foo1 and @foo2)
...
(@prmProcessingID = 0 OR tbl10.ProcessingID = @prmProcessingID)
You get the idea. I broke up the query into smaller portions, depositing data into temp tables along the way, so that each successive query has to work on a smaller subset. But at the end of the day, it still matches thousands of records, so performance didn't improve as much as I hoped.
My next thought was to only return 1 page of records at a time (20 records per page), since that is all the user needs to see. So I added OFFSET x ROWS and FETCH NEXT 20 ROWS ONLY and that fixed the performance problem.
However, the problem now is that I return 20 rows, but I have no idea how many rows match the criteria in total. And thus, I can't tell the user how many pages of data there are - e.g. I can't render the pager on the UI properly.
Is there a better way to approach this problem?
答案1
得分: 1
有没有更好的解决办法？
计算行数需要评估整个数据集上的连接和WHERE子句，因此几乎和运行查询并存储结果一样昂贵。
你的选择是运行完整的查询并缓存结果，或者采用一个不显示总页数的用户体验。
英文:
>Is there a better way to approach this problem?
Counting the rows requires evaluating the JOINs and WHERE clause over the whole dataset, so it is almost expensive as running the query and storing the results.
Your choices are to run the full query and cache the results, or adopt a UX that doesn't display the total number of pages.
答案2
得分: -3
尝试将查询拆分为多个WITH语句，并将函数调用推迟到可能的最后一个查询，以简化查询的结构。从逻辑上讲，它们将与一个大查询相同。
David Browne 是正确的，如果不需要在前端用户界面显示行计数，就不要计算它。
我们按以下顺序执行了所有的WITH语句
WITH customerData AS
(
选择客户数据字段并加入查找数据，其中筛选条件仅限于活动客户
),
purchaseOrders as
(
选择客户数据行中的客户ID和日期范围为A到B的采购订单字段
),
...
然后最终查询会加入正确的WITH语句并调用函数、CASE语句等。
它的好处是你可以逐渐将原始查询重构为一系列更简单的查询。
英文:
Try breaking the query into multiple WITH statements and deferring the function calls to the last possible query as a way to simplify the structure of the query. Logically, they would be same as one big query.
David Browne is correct, if you do not need to display a row count in the front-end UI, don't compute it.
We did all of the WITH statements in this order
WITH customerData AS
(
SELECT customer data fields used and join in lookup data WHERE filter in only active customers
),
purchaseOrders as
(
SELECT purchase order fields where customer Id in CustomerData rows and date range in A to B
),
...
then the final query joins in the right With statements and calls the functions, case statements, etc.
The nice thing about it is you can slowly refactor the original query into a series of simpler queries.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论