主页 / dba / 问题 / 158180
Accepted
Jason
Asked: 2016-12-15 11:46:20 +0800 CST

加入大表的最有效方法

  • 772

我有一个有 20M 行的表，每行有 3 列：time、id和value. 对于每个idand time，都有一个valuefor 状态。我想知道特定timeand的最后一个周期和下一个周期的值id，并使用以下查询来获取这些值：

update a1
set  a1.value_last = b1.value,   
     a1.value_next = c1.value
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.period = b1.period + 1
left join tab1 c1
on a1.id = c1.id
and a1.period = c1.period - 1

似乎查询需要永远并且日志文件增加了 10 GB 以上。我想知道编写此查询的最有效方法是什么？我知道使用索引会加快加入过程，但是如何减少日志记录？

我在 Win10 64 位上使用 SQL Server 2016。

sql-server performance

3 个回答

  1. Best Answer
    update a1
set  a1.value_last = LAG(value, 1,0)  OVER (partition by id ORDER BY period)
  ,  a1.value_next = LEAD(value, 1,0) OVER (partition by id ORDER BY period)
from tab1 a1

    id索引，期间

    或者只是使用视图 - 这可能会让您感到惊讶

    CREATE VIEW tab1LastNext  
AS  
select a1.id, a1.period, a1.value
     , LAG(value, 1,0)  OVER (partition by id ORDER BY period) as value_last
     , LEAD(value, 1,0) OVER (partition by id ORDER BY period) as value_next
from tab1 a1;

    记录
    需要希望值不为空，否则会变得混乱 

    select 1;
while @@rowcount > 0
begin 
  update top (10000) a1
  set a1.value_last = LAG(value, 1,0) OVER (partition by id ORDER BY period)
  from tab1 a1 
  where LAG(value, 1,0) OVER (partition by id ORDER BY period) is not null 
    and LAG(value, 1,0) OVER (partition by id ORDER BY period) != a1.value_last
end 
select 1;
while @@rowcount > 0
begin 
  update top (10000) a1
  set a1.value_next = LEAD(value, 1,0) OVER (partition by id ORDER BY period)
  from tab1 a1 
  where LEAD(value, 1,0) OVER (partition by id ORDER BY period) is not null 
    and LEAD(value, 1,0) OVER (partition by id ORDER BY period) != a1.value_next
end
    • 3

  2. 如果我理解这个问题，您不需要更新任何内容——这只是您用来支持此处未显示的 SELECT 语句的方法。

    在这种情况下，这应该让您无需更新：

    SELECT id, period, value
, LAG([value], 1, NULL) OVER (PARTITION BY id ORDER BY id, [period]) 
    AS value_last
, LEAD([value], 1, NULL) OVER (PARTITION BY id ORDER BY id, [period]) 
    AS value_next
FROM tab1

    此代码适用于 SQL Server 2012（兼容级别 110）及更高版本。

    作为PARTITION BY idLAG 和 LEAD 函数的一部分，请记住分区中的第一行value_last的value_next(因为分区中没有下一行）。如果您想将它们更改为其他值（可能为零），则该值位于LAG([value], 1, NULL)代码部分中 NULL 的位置。

    通过使用这些窗口函数（如@Jorriss 建议的那样），您消除了对 UPDATE 语句以及随之而来的日志记录的需要。

    • 2

  3. 看看 SQL Server 窗口函数，特别是LAG和LEAD. 这些函数允许查询在没有自连接的情况下获取结果集中的数据。这是 StackOverflow 数据转储中的一个示例。

    SELECT p.ID,
       LAG (p.ID, 1) OVER (ORDER BY p.OwnerUserId DESC) AS PreviousID,
       LEAD (p.ID, 1) OVER (ORDER BY p.OwnerUserId DESC) AS NextID
FROM   Posts p
JOIN   Users u ON u.Id = p.OwnerUserId
WHERE  DisplayName = 'CecilPhillip'
AND    p.PostTypeId = 1
AND    p.OwnerUserId = 333082

    LAG (p.ID, 1)将ID在上一行中查找。 LEAD (p.ID, 1)将ID在下一行中查找。

    ID       | PreviousID | NextID
----------------------------------
15231460 |       NULL |   3037486
3037486  |   15231460 |  10018831
10018831 |    3037486 |  11043750
11043750 |   10018831 |   9577679
 9577679 |   11043750 |   9597766
 9597766 |    9577679 |  24660128
24660128 |    9597766 |   5722741
 5722741 |   24660128 |  18713274
18713274 |    5722741 |   3679697
 3679697 |   18713274 |      NULL

    您会注意到，如果没有找到值，它将返回一个NULL.

    • 1

相关问题