Speed up SQL query billions of rows

Question

I am running a stored procedure 17 days ago and this still does not finish. The query still not complete is not optimal , but neither how could accelerate as I need to analyze the different combinations of all rows together. I'm using SQL Server 2012.

This is the code of the stored procedure

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

Almost every table has 9-11 rows. What can I do to improve this query? Many thanks!!!

Answer 1

It is unclear why you need to do a cartesian product. But, why are you using a cursor? That will slow things down immensely. Just do something like:

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;

This should be much faster than the cursor version, but I don't know if the performance will be that good. Try it out on a smaller data set and see if it helps. In general, you want to avoid cursors when you can use set-based operations instead.

Answer 2

Your are asking the wrong question, so any answer we will give is also going to be wrong.

You are asking how to more effectively use the tool you have (SQL Server) to solve an unspecified problem.

The "job to be done" may be more easily solved with some other tool or a completely different design.

If you really-really need to use a Cartesian product of this volume, you should look at using Lazy Evaluation or the call-by-need design pattern. How you do this depends on the client language involved.

You MAY be able to create a T-SQL lazy-evaluation with a procedure and a few sequences, but that is just a hunch.

Answer 3

For starters, don't do this within SQL server. It's not designed to handle such queries.

If you truly have billions of rows, you might consider breaking it into smaller chunks, or simply sampling the data. Consider what information you think you can pull out of the full data set that you couldn't get by sampling.