提高SQL查询数十亿行的速度

Question

我在17天前运行了一个存储过程，但仍然没有完成。查询仍然没有完成并不是最优的，但是也不能加速，因为我需要分析所有行的不同组合。我使用的是SQL Server 2012。

这是存储过程的代码

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Calc] 
AS
BEGIN
DECLARE @statA int,@statB int,@statC int,@statD int,@statF int;

DECLARE @statA_Element1 int,@statB_Element1 int,@statC_Element1 int,@statD_Element1 int,@statF_Element1 int,@descriptionElement1 varchar(50);
DECLARE @statA_Element2 int,@statB_Element2 int,@statC_Element2 int,@statD_Element2 int,@statF_Element2 int,@descriptionElement2 varchar(50);
DECLARE @statA_Element3 int,@statB_Element3 int,@statC_Element3 int,@statD_Element3 int,@statF_Element3 int,@descriptionElement3 varchar(50);
DECLARE @statA_Element4 int,@statB_Element4 int,@statC_Element4 int,@statD_Element4 int,@statF_Element4 int,@descriptionElement4 varchar(50);
DECLARE @statA_Element5 int,@statB_Element5 int,@statC_Element5 int,@statD_Element5 int,@statF_Element5 int,@descriptionElement5 varchar(50);
DECLARE @statA_Element6 int,@statB_Element6 int,@statC_Element6 int,@statD_Element6 int,@statF_Element6 int,@descriptionElement6 varchar(50);

DECLARE @statA_Element7 int,@statB_Element7 int,@statC_Element7 int,@statD_Element7 int,@statF_Element7 int,@descriptionElement7 varchar(50);
DECLARE @statA_Element8 int,@statB_Element8 int,@statC_Element8 int,@statD_Element8 int,@statF_Element8 int,@descriptionElement8 varchar(50);
DECLARE @statA_Element9 int,@statB_Element9 int,@statC_Element9 int,@statD_Element9 int,@statF_Element9 int,@descriptionElement9 varchar(50);
DECLARE @statA_Element10 int,@statB_Element10 int,@statC_Element10 int,@statD_Element10 int,@statF_Element10 int,@descriptionElement10 varchar(50);
DECLARE @statA_Element11 int,@statB_Element11 int,@statC_Element11 int,@statD_Element11 int,@statF_Element11 int,@descriptionElement11 varchar(50);
DECLARE @statA_Element12 int,@statB_Element12 int,@statC_Element12 int,@statD_Element12 int,@statF_Element12 int,@descriptionElement12 varchar(50);

DECLARE element_cursor CURSOR FOR 
select  e1.statA,e1.statB,e1.statC,e1.statD,e1.statF,e1.Description,
        e2.statA,e2.statB,e2.statC,e2.statD,e2.statF,e2.Description,
        e3.statA,e3.statB,e3.statC,e3.statD,e3.statF,e3.Description,
        e4.statA,e4.statB,e4.statC,e4.statD,e4.statF,e4.Description,
        e5.statA,e5.statB,e5.statC,e5.statD,e5.statF,e5.Description,
        e6.statA,e6.statB,e6.statC,e6.statD,e6.statF,e6.Description,
        e7.statA,e7.statB,e7.statC,e7.statD,e7.statF,e7.Description,
        e8.statA,e8.statB,e8.statC,e8.statD,e8.statF,e8.Description,
        e9.statA,e9.statB,e9.statC,e9.statD,e9.statF,e9.Description,
        e10.statA,e10.statB,e10.statC,e10.statD,e10.statF,e10.Description,
        e11.statA,e11.statB,e11.statC,e11.statD,e11.statF,e11.Description,
        e12.statA,e12.statB,e12.statC,e12.statD,e12.statF,e12.Description,
                from Element1 e1
              ,Element2 e2
              ,Element3 e3
              ,Element4 e4
              ,Element5 e5
              ,Element6 e6
              ,Element7 e7
              ,Element8 e8
              ,Element9 e9
              ,Element10 e10
              ,Element11 e11
              ,Element12 e12;
truncate table resultado;
OPEN element_cursor

FETCH NEXT FROM element_cursor 
INTO @statA_Element1,@statB_Element1,@statC_Element1,@statD_Element1,@statF_Element1,@descriptionElement1,
     @statA_Element2,@statB_Element2,@statC_Element2,@statD_Element2,@statF_Element2,@descriptionElement2,
     @statA_Element3,@statB_Element3,@statC_Element3,@statD_Element3,@statF_Element3,@descriptionElement3,
     @statA_Element4,@statB_Element4,@statC_Element4,@statD_Element4,@statF_Element4,@descriptionElement4,
     @statA_Element5,@statB_Element5,@statC_Element5,@statD_Element5,@statF_Element5,@descriptionElement5,
     @statA_Element6,@statB_Element6,@statC_Element6,@statD_Element6,@statF_Element6,@descriptionElement6,
     @statA_Element7 ,@statB_Element7 ,@statC_Element7 ,@statD_Element7 ,@statF_Element7 ,@descriptionElement7, 
     @statA_Element8 ,@statB_Element8 ,@statC_Element8 ,@statD_Element8 ,@statF_Element8 ,@descriptionElement8, 
     @statA_Element9 ,@statB_Element9 ,@statC_Element9 ,@statD_Element9 ,@statF_Element9 ,@descriptionElement9, 
     @statA_Element10 ,@statB_Element10 ,@statC_Element10 ,@statD_Element10 ,@statF_Element10 ,@descriptionElement10, 
     @statA_Element11 ,@statB_Element11 ,@statC_Element11 ,@statD_Element11 ,@statF_Element11 ,@descriptionElement11, 
     @statA_Element12 ,@statB_Element12 ,@statC_Element12 ,@statD_Element12 ,@statF_Element12 ,@descriptionElement12 

WHILE @@FETCH_STATUS = 0
BEGIN

    set @statA= @statA_Element1+ @statA_Element2+ @statA_Element3+ @statA_Element4+ @statA_Element5+ @statA_Element6+@statA_Element7+@statA_Element11+@statA_Element8+@statA_Element9+@statA_Element10+@statA_Element12;
    set @statB= @statB_Element1+ @statB_Element2+ @statB_Element3+ @statB_Element4+ @statB_Element5+ @statB_Element6+@statB_Element7+@statB_Element8+@statB_Element9+@statB_Element10+@statB_Element11+@statB_Element12;
    set @statC= @statC_Element1+ @statC_Element2+ @statC_Element3+ @statC_Element4+ @statC_Element5+ @statC_Element6+@statC_Element7+@statC_Element8+@statC_Element9+@statC_Element10+@statC_Element11+@statC_Element12;
    set @statD= @statD_Element1+ @statD_Element2+ @statD_Element3+ @statD_Element4+ @statD_Element5+ @statD_Element6+@statD_Element7+@statD_Element8+@statD_Element9+@statD_Element10+@statD_Element11+@statD_Element12;
    set @statF = @statF_Element1+ @statF_Element2+ @statF_Element3+ @statF_Element4+ @statF_Element5+ @statF_Element6+@statF_Element7+@statF_Element8+@statF_Element9+@statF_Element10+@statF_Element11+@statF_Element12;

    if(@statC>=2000)
    begin
        insert into res values(@statA, @statB, @statC, @statD, @statF, @descriptionElement2, @descriptionElement3, @descriptionElement1, @descriptionElement5, @descriptionElement6, @descriptionElement4,@descriptionElement7,@descriptionElement8,@descriptionElement9,@descriptionElement10,@descriptionElement11,@descriptionElement12  );
    end
        -- Get the next vendor.
    FETCH NEXT FROM element_cursor 
INTO @statA_Element1,@statB_Element1,@statC_Element1,@statD_Element1,@statF_Element1,@descriptionElement1,
     @statA_Element2,@statB_Element2,@statC_Element2,@statD_Element2,@statF_Element2,@descriptionElement2,
     @statA_Element3,@statB_Element3,@statC_Element3,@statD_Element3,@statF_Element3,@descriptionElement3,
     @statA_Element4,@statB_Element4,@statC_Element4,@statD_Element4,@statF_Element4,@descriptionElement4,
     @statA_Element5,@statB_Element5,@statC_Element5,@statD_Element5,@statF_Element5,@descriptionElement5,
     @statA_Element6,@statB_Element6,@statC_Element6,@statD_Element6,@statF_Element6,@descriptionElement6,
     @statA_Element7 ,@statB_Element7 ,@statC_Element7 ,@statD_Element7 ,@statF_Element7 ,@descriptionElement7, 
     @statA_Element8 ,@statB_Element8 ,@statC_Element8 ,@statD_Element8 ,@statF_Element8 ,@descriptionElement8, 
     @statA_Element9 ,@statB_Element9 ,@statC_Element9 ,@statD_Element9 ,@statF_Element9 ,@descriptionElement9, 
     @statA_Element10 ,@statB_Element10 ,@statC_Element10 ,@statD_Element10 ,@statF_Element10 ,@descriptionElement10, 
     @statA_Element11 ,@statB_Element11 ,@statC_Element11 ,@statD_Element11 ,@statF_Element11 ,@descriptionElement11, 
     @statA_Element12 ,@statB_Element12 ,@statC_Element12 ,@statD_Element12 ,@statF_Element12 ,@descriptionElement12
END 
CLOSE element_cursor;
DEALLOCATE element_cursor;
END

几乎每个表都有9-11行。我能做些什么来改进这个查询？非常感谢！

Answer 1

目前还不清楚为什么你需要做笛卡尔产品。但是，为什么要使用游标呢？这将极大地放慢速度。只要像这样做就行了：

select *
into results
from (select (e1.StatA + e1.StatA + . . . ) as StatA,
             (e1.StatB + e1.StatB + . . . ) as StatB,
             (e1.StatC + e1.StatC + . . . ) as StatC,
             (e1.StatD + e1.StatD + . . . ) as StatD,
             (e1.StatF + e1.StatF + . . . ) as StatE,
             e1.description as description1, e2.description as description2, . . .
      from Element1 e1 cross join
           Element2 e2 cross join
           Element3 e3 cross join
           Element4 e4 cross join
           Element5 e5 cross join
           Element6 e6 cross join
           Element7 e7 cross join
           Element8 e8 cross join
           Element9 e9 cross join
           Element10 e10 cross join
           Element11 e11 cross join
           Element12 e12
      ) t
where StatC > 2000;

这应该比游标版本快得多，但我不知道性能是否会那么好。在较小的数据集上尝试它，看看它是否有帮助。通常，当可以使用基于集合的操作时，您希望避免使用游标。

Answer 2

对于初学者，不要在SQL server中执行此操作。它不是为处理这样的查询而设计的。

如果您真的有数十亿行，您可以考虑将其分解为更小的块，或者简单地对数据进行采样。考虑一下您认为您可以从完整的数据集中提取哪些信息，而这些信息是您无法通过采样获得的。

Answer 3

您提出的问题是错误的，所以我们给出的任何答案也将是错误的。

您正在询问如何更有效地使用现有工具(SQL Server)来解决未指明的问题。

“待完成的工作”可能更容易用其他工具或完全不同的设计来解决。

如果您真的-真的需要使用这一卷的笛卡尔产品，您应该考虑使用Lazy Evaluation或按需调用设计模式。如何做到这一点取决于所涉及的客户端语言。

您也许能够使用一个过程和几个序列来创建T-SQL惰性评估，但这只是一种直觉。