Database Challenges

We tested the solutions that passed the Logic Testing with a very large set of data. Fabien, a very active supporter of TSQL Challenges has came up with a script that generates 1 million rows.

The Load Testing involves running each solutions three times with a large data set and collecting the performance statistics. Because of the complex nature of this challenge, it was taking hours and hours to run the tests with 1 million rows. So we decided to use a subset of the data and ended up testing the solutions with only 100,000 rows.

Here is the data used for the load testing of TSQL Challenge #13

-- Topic    : TSQL Challenge #13 - Create a one million rows table to test the query
-- Date     : 04 Sep 2009
-- Author   : Fabien "Waldar" Contaminard
-- Location : Paris
-- Email    : http://scr.im/waldar


-- Create Table
CREATE TABLE dbo.TC13
(
    InvID       INT IDENTITY ,
    BatchNumber INT          ,
    InvoiceNumber INT          ,
    VisitDate   SMALLDATETIME,
    Amount      MONEY
);

-- Variables
DECLARE @MAXROWSTABLE AS INT ;
SET @MAXROWSTABLE = 1e6;

-- Query
WITH Fill(Id, Nb, Tot, CT)

-- Creating the Batches with the number of rows they will hold
AS
(

-- I'm using Timestamp milliseconds to create random numbers
SELECT
    1,
    FLOOR(DATEPART(ms, CURRENT_TIMESTAMP)/10)+1,
-- I'm using ms divided by ten +1 for the number of batch rows to be somewhere between 1 and 100
    FLOOR(DATEPART(ms, CURRENT_TIMESTAMP)/10)+1,
    CURRENT_TIMESTAMP
UNION ALL
SELECT
    Id + 1,
    FLOOR(DATEPART(ms, CT + Tot*24.0000/60/60/1000)/10),
    FLOOR(DATEPART(ms, CT + Tot*24.0000/60/60/1000)/10) + Tot,
    CURRENT_TIMESTAMP + Tot*24.0000/60/60/1000
FROM Fill
WHERE
    Tot < @MAXROWSTABLE
),
     Fill2(Id1, Id2, Nb, Tot1, Tot2, CT)
-- Creating the Invoices with the number of rows they will hold
AS
(
SELECT
    Id, 1,
-- I'm using ms divided by one hundred +1 for the number of invoice rows to be somewhere between 1 and 10
    FLOOR(DATEPART(ms, CT)/100)+1,
-- This case is for the final batch who can be beyond the expected number of rows
    CASE WHEN Tot > @MAXROWSTABLE THEN Nb - Tot + @MAXROWSTABLE ELSE Nb END,
    FLOOR(DATEPART(ms, CT)/100)+1,
    CT
FROM Fill
UNION ALL
SELECT
    F2.Id1, 
    F2.Id2 + 1,
    FLOOR(DATEPART(ms, F2.CT + F2.Tot2*24.0000/60/60/58)/100)+1,
    F2.Tot1,
    FLOOR(DATEPART(ms, F2.CT + F2.Tot2*24.0000/60/60/58)/100)+1 + F2.Tot2,
    F2.CT + F2.Tot2*24.0000/60/60/58
FROM
    Fill AS F1
    INNER JOIN Fill2 AS F2
      ON F2.Id1 = F1.Id
WHERE
    F2.Tot2 < F2.Tot1
),
     Fact(Id)
-- I'm using a ten rows table to generate the table according the the invoice rows
AS
(
SELECT 1
UNION ALL
SELECT Id + 1 FROM Fact 
WHERE Id < 10
)
INSERT INTO TC13 (BatchNumber, InvoiceNumber, VisitDate, Amount)
SELECT TOP 100000 /*Reduced the number of rows to 100,000*/
    1000000 + F2.Id1 AS BatchNumber,
    DENSE_RANK() OVER(ORDER BY F2.Id1 + F2.Id2 / 100.0 ASC) AS InvoiceNumber,
    CONVERT(smalldatetime, '20090101', 112) + FA.Id - 1 AS VisitDate,
    1 AS Amount
FROM
    Fill2 AS F2
    INNER JOIN Fact AS FA
-- This case is for the final invoice of each batch who can be beyond the expected number of rows
      ON FA.Id <= CASE WHEN F2.Tot2 > F2.Tot1 THEN F2.Nb + F2.Tot1 - F2.Tot2 Else F2.Nb END
ORDER BY
    F2.Id1 ASC,
    F2.Id2 ASC,
    FA.Id ASC

-- For 1000000 rows there's a lot of recursion
OPTION (MAXRECURSION 32000);

-- Adding PK and Index, fasten my solution query by 20%
ALTER TABLE dbo.TC13
ADD CONSTRAINT PK_TC13 PRIMARY KEY (InvID);

CREATE INDEX I_TC13_Batch_Invoice
ON dbo.TC13 (BatchNumber, InvoiceNumber);

You can find the results of the "Load Testing" here.