SQL Server，对多个表使用UNION ALL，然后分页实现 [英] SQL Server, Using UNION ALL for multiple tables then paging implementation

问题描述

我还需要有关分页和对多个表使用UNION ALL的帮助:

I need help also about paging and using UNION ALL for multiple tables:

使用UNION ALL连接多个表并仅返回特定数量的行时，如何实现优化的分页...

How do i implement an optimized paging when joining multiple tables using UNION ALL and returning only specific number of rows...

declare @startRow int
declare @PageCount int

set @startRow = 0
set @PageCount = 20

set rowcount @PageCount

select Row_Number() OVER(Order by col1) as RowNumber, col1, col2
from
(
    select col1, col2 from table1 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table2 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table3 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table4 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table5 where datetimeCol between (@dateFrom and @dateTo)
) as tmpTable
where RowNumber > @startRow

---

表3、4和&表1& 5的行数(百万行)很大. 2可能只有几千行.

---

table 3, 4, & 5 have huge number of row (millions of rows) where table 1 & 2 may only have few thousand rows.

如果startRow为"0"，则我只希望数据从第1行到第20行(来自表1). 我得到正确的结果，但是在剩余的表上开销很高，而sql server尝试对所有数据进行过滤并对其进行过滤....

If startRow is "0", I only expect data from Row 1 to 20 (from Table1). I'm getting the correct result but has a high overhead on the remaining table while sql server tries to all all the data and filter it....

@dateFrom和@dateTo的间隔时间越长，在尝试从整体结果集中仅检索几行时，我的查询就会明显变慢

the longer the interval of the @dateFrom and @dateTo makes my query significantly slower while trying to retrieve only few rows from the overall result set

请帮助我如何用类似的逻辑实现一个简单但更好的方法. :(

Please help how i can implement a simple but better approach with a similar logic. :(

推荐答案

考虑使用OFFSET FETCH子句(从MSSQL 2012开始工作):

Consider using OFFSET FETCH clause (works starting with MSSQL 2012):

declare @startRow int
declare @PageCount int

set @startRow = 0
set @PageCount = 20


select col1, col2
from
(
    select col1, col2 from table1 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table2 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table3 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table4 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table5 where datetimeCol between (@dateFrom and @dateTo)
) as tmpTable
order by col1
offset @startRow rows
fetch next @PageCount rows only

我还要在这里提及为什么此查询总是要花费O(n * log(n))时间.
要执行此查询，数据库需要:

I also want to mention here, why this query always takes O(n*log(n)) time.
To execute this query, database needs to:

1. 将多个列表合并为一个列表-每个表花费O(n)时间，其中n-表中的总行数；
2. 按col1排序列表-取O(n * log(n))，其中n-是总行数
3. 按排序顺序遍历列表，跳过@startRow行，接下@PageCount行.

如您所见，您需要对所有数据进行合并和排序才能获得预期的结果(数字3).

As you can see, you need to union and sort all data to get the expected result (number 3).

如果此查询的性能仍然很差，并且您想提高性能，请尝试:

If the performance of this query is still poor and you want to increase in, try to:

• 在所有表中基于col1创建聚簇索引
• 在所有表中基于col1创建一个非集群索引，并**在选择列表中包括要输出的所有其他列**.

这篇关于SQL Server，对多个表使用UNION ALL，然后分页实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！