Início / dba / Perguntas / 158180
Accepted
Jason
Asked: 2016-12-15 11:46:20 +0800 CST

A maneira mais eficiente de juntar mesas enormes

  • 772

Eu tenho uma tabela com 20 milhões de linhas e cada linha tem 3 colunas: time, ide value. Para cada ide time, há um valuepara o status. Quero saber os valores do último e dos próximos períodos para um determinado timee id, e tenho a seguinte consulta para obter os valores:

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

Parece que a consulta demora uma eternidade e o arquivo de log aumentou em mais de 10 GB. Eu estou querendo saber qual é a maneira mais eficiente de escrever esta consulta? Sei que usar o índice acelerará o processo de junção, mas como posso reduzir o registro?

Estou usando o SQL Server 2016 no Win10 64 bits.

sql-server performance

3 respostas

  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

    índice no id, ponto

    ou apenas use uma visualização - isso pode surpreendê-lo

    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;

    para registro
    , é necessário esperar que o valor não seja nulo ou isso ficará confuso 

    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. Se entendi a pergunta, você não precisa atualizar nada - é apenas o meio que você está usando para oferecer suporte a uma instrução SELECT que não é mostrada aqui.

    Nesse caso, isso deve levá-lo até lá sem precisar de uma ATUALIZAÇÃO:

    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

    Este código funcionará no SQL Server 2012 (nível de compatibilidade 110) e superior.

    Com o PARTITION BY idcomo parte das funções LAG e LEAD, lembre-se de que a primeira linha da partição terá NULL para seu value_last(porque não há linha anterior na partição) e a última linha da partição terá NULL para seu value_next( porque não há próxima linha na partição). Se você quiser alterá-los para algum outro valor - talvez zero - esse valor vai onde o NULL está na LAG([value], 1, NULL)parte do código.

    Ao usar essas funções de janela (como @Jorriss sugere), você está eliminando a necessidade de uma instrução UPDATE e o registro que vem com ela.

    • 2

  3. Dê uma olhada em SQL Server Window Functions, especificamente LAGe LEAD. Essas funções permitem que a consulta obtenha dados no conjunto de resultados sem junções automáticas. Aqui está um exemplo do despejo de dados 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)irá procurar o IDna linha anterior. LEAD (p.ID, 1)irá procurar o IDna próxima linha.

    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

    Você notará que, se não houver um valor encontrado, ele retornará um NULL.

    • 1

relate perguntas