Rax/MySQL vs. Rax/Azure vs. Rax/Redshift

Our SQL-backend family is contantly growing. Rax could already connect to SQLite, MySQL and PostgreSQL databases. Now we have also ported Rax to two major cloud databases: Microsoft Azure and AWS Redshift. The port to Azure gave us some headache due to problems with their ODBC driver for Linux. The port to Redshift was straightforward, as it's using PostgreSQL's syntax and ODBC driver.

Before Rax, it was difficult to compare the performance of various database systems on behavioral data, as each database vendor provides a completely different set of date and time functions, which are essential when analyzing behavioral data. With Rax, we can easily do this comparison, as Rax offers a uniform interface for time-related operations.

For testing, we use a real-life analysis: a computation of the TV reach. Below is a Rax script computing the reach of various TV channels:

// Calculate the reach per channel during a given period
{[|]}: PeriodOfInterest := {[(|)[(@)"2013-04-30",(^)"P4D"]]};
^: TvReachThreshold := (^)"PT5M";

// Map the'TvExposures' table to a Rax set
{[#:RespondentId, |:Timeslot, #:ChannelId]}: 
  TvExposures := 
    import [
      (#)"RespondentId",
      (|)[(@)"StartTimeslot", (@)"EndTimeslot"],
      (#)"TvChannelId"
    ] 
  "TvExposures";

// Map the 'RespondentWeights' table to a Rax set
{[#:RespondentId, #:MediaUniverseId, |:Period, &:Weight]}: 
  RespondentWeights :=
    import [
      (#)"RespondentId", 
      (#)"MediaUniverseId",
      (|)[(@)"CAST(StartDate AS DATETIME)", (@)"CAST(EndDate AS DATETIME)"],
      (&)"Weight"
    ] 
    "RespondentWeights";

// Cut the TvExposures set to the period of interest
TvExposures := TvExposures @& PeriodOfInterest;

// Add correct respondent weights to the TvExposures set
{[#:RespondentId, |:Timeslot, #:ChannelId, &:Weight]} : TvExposuresWeighted :=
  project [.RespondentId#1, .Timeslot, .ChannelId, .Weight]
  (TvExposures @& :[.RespondentId#1 == .RespondentId#2 && .MediaUniverseId == 100]
     RespondentWeights);

// Sum the durations per respondent and channel
{[^:totalDuration, #:RespondentId, #:ChannelId, &:Weight]} : Durations :=
  Gsum [.#1]:[.#2, .#3, .#4]
  project [.Timeslot.absolute, .RespondentId, .ChannelId, .Weight]
  TvExposuresWeighted;

// Filter very light watchers 
Durations := select [.totalDuration >= TvReachThreshold] Durations;

// Compute the total size of the audience
&: TotalWeight :=
  Gsum [.#1]
  Gmean [.Weight]:[.RespondentId]
  (RespondentWeights @& PeriodOfInterest);

// Finally, compute reach per channel
{[&:Reach, &:SampleSize, #:ChannelId]} : ReachPerChannel :=
  project [.#1/TotalWeight, .#1, .#2]
  Gsum [.Weight]:[.ChannelId] Durations;

// And print the results
`print ReachPerChannel;

We've run this script on Rax/MySQL, Rax/PostgreSQL, Rax/Azure and Rax/Redshift. The script runs on all backends essentially without changes. For Postgres-like dialects (PostgreSQL, Redshift), we had to change one of the lines in the import statement (since import contains snippets of SQL):

(|)[(@)"CAST(StartDate AS DATETIME)", (@)"CAST(EndDate AS DATETIME)"]

was changed to:

(|)[(@)"CAST(StartDate AS TIMESTAMP)", (@)"CAST(EndDate AS TIMESTAMP)"]

The data size in this experiment was rather small: the TvExposures table has approximately 400000 rows. The runtimes of the script on various database systems are plotted below (the chart was, naturally, generated by Rax). For Redshift, we tried various cluster configurations.

Somewhat surprisingly, the Azure's SQL Server was actually slower on this query than MySQL on my local machine (Mac mini with 2.3 GHz Intel Core i7 and 8GB of memory). PostgreSQL performs rather badly. It seems to spend a lot of time on the non-equi join between TvExposures and RespondentWeights. Azure and a single-node Redshift configuration perform similarly. Increasing Redshift's cluster size and using larger machines gave a clear performance benefit. However, the largest cluster size that we used on Redshift was 2. Scaling beyond that simply didn't make sense for the data size in this demo.

All in all, it was an interesting experiment. However, it will be even more interesting to compare the performance of this script on real Big Data. You will read about it in one of the following blog posts. Stay put.

And for the curious, this is the query generated from this script by Rax/Azure (don't get scared):

SELECT
  * 
FROM
  ( SELECT
    A.rownum AS rownum,
    (C01) / (25083.4) AS C01,
    C02 AS C02 
  FROM
    ( SELECT
      0 AS rownum,
      SUM(C04) AS C01,
      C03 AS C02 
    FROM
      ( SELECT
        A.rownum AS rownum,
        C01 AS C01,
        C02 AS C02,
        C03 AS C03,
        C04 AS C04 
      FROM
        ( SELECT
          0 AS rownum,
          SUM(C01) AS C01,
          C02 AS C02,
          C03 AS C03,
          C04 AS C04 
        FROM
          ( SELECT
            A.rownum AS rownum,
            C26 AS C01,
            C01 AS C02,
            C27 AS C03,
            C28 AS C04 
          FROM
            ( SELECT
              A.rownum AS rownum,
              C01 AS C01,
              C02 AS C02,
              C03 AS C03,
              C04 AS C04,
              C05 AS C05,
              C06 AS C06,
              C07 AS C07,
              C08 AS C08,
              C09 AS C09,
              C10 AS C10,
              C11 AS C11,
              C12 AS C12,
              C13 AS C13,
              C14 AS C14,
              C15 AS C15,
              C16 AS C16,
              C17 AS C17,
              C18 AS C18,
              C19 AS C19,
              C20 AS C20,
              C21 AS C21,
              C22 AS C22,
              C23 AS C23,
              C24 AS C24,
              C25 AS C25,
              C26 AS C26,
              C27 AS C27,
              C30 AS C28 
            FROM
              ( SELECT
                0 AS rownum,
                A.C01 AS C01,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C02 
                  ELSE B.C03 
                END AS C02,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C03 
                  ELSE B.C04 
                END AS C03,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C04 
                  ELSE B.C05 
                END AS C04,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C05 
                  ELSE B.C06 
                END AS C05,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C06 
                  ELSE B.C07 
                END AS C06,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C07 
                  ELSE B.C08 
                END AS C07,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C08 
                  ELSE B.C09 
                END AS C08,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C09 
                  ELSE B.C10 
                END AS C09,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C10 
                  ELSE B.C11 
                END AS C10,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C11 
                  ELSE B.C12 
                END AS C11,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C12 
                  ELSE B.C13 
                END AS C12,
                CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C13 
                  ELSE B.C14 
                END AS C13,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C14 
                  ELSE B.C15 
                END AS C14,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C15 
                  ELSE B.C16 
                END AS C15,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C16 
                  ELSE B.C17 
                END AS C16,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C17 
                  ELSE B.C18 
                END AS C17,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C18 
                  ELSE B.C19 
                END AS C18,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C19 
                  ELSE B.C20 
                END AS C19,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C20 
                  ELSE B.C21 
                END AS C20,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C21 
                  ELSE B.C22 
                END AS C21,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C22 
                  ELSE B.C23 
                END AS C22,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C23 
                  ELSE B.C24 
                END AS C23,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C24 
                  ELSE B.C25 
                END AS C24,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C25 
                  ELSE B.C26 
                END AS C25,
                CASE 
                  WHEN (A.C14)<=(B.C15) THEN A.C14 
                  ELSE B.C15 
                END - CASE 
                  WHEN (A.C02)>=(B.C03) THEN A.C02 
                  ELSE B.C03 
                END AS C26,
                A.C27 AS C27,
                B.C01 AS C28,
                B.C02 AS C29,
                B.C28 AS C30 
              FROM
                ( SELECT
                  0 AS rownum,
                  A.C01 AS C01,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C02 
                    ELSE B.C01 
                  END AS C02,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C03 
                    ELSE B.C02 
                  END AS C03,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C04 
                    ELSE B.C03 
                  END AS C04,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C05 
                    ELSE B.C04 
                  END AS C05,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C06 
                    ELSE B.C05 
                  END AS C06,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C07 
                    ELSE B.C06 
                  END AS C07,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C08 
                    ELSE B.C07 
                  END AS C08,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C09 
                    ELSE B.C08 
                  END AS C09,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C10 
                    ELSE B.C09 
                  END AS C10,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C11 
                    ELSE B.C10 
                  END AS C11,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C12 
                    ELSE B.C11 
                  END AS C12,
                  CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C13 
                    ELSE B.C12 
                  END AS C13,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C14 
                    ELSE B.C13 
                  END AS C14,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C15 
                    ELSE B.C14 
                  END AS C15,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C16 
                    ELSE B.C15 
                  END AS C16,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C17 
                    ELSE B.C16 
                  END AS C17,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C18 
                    ELSE B.C17 
                  END AS C18,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C19 
                    ELSE B.C18 
                  END AS C19,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C20 
                    ELSE B.C19 
                  END AS C20,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C21 
                    ELSE B.C20 
                  END AS C21,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C22 
                    ELSE B.C21 
                  END AS C22,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C23 
                    ELSE B.C22 
                  END AS C23,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C24 
                    ELSE B.C23 
                  END AS C24,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C25 
                    ELSE B.C24 
                  END AS C25,
                  CASE 
                    WHEN (A.C14)<=(B.C13) THEN A.C14 
                    ELSE B.C13 
                  END - CASE 
                    WHEN (A.C02)>=(B.C01) THEN A.C02 
                    ELSE B.C01 
                  END AS C26,
                  A.C27 AS C27 
                FROM
                  ( SELECT
                    0 AS rownum,
                    RespondentId AS C01,
                    ((DATEPART(DAY,
                    StartTimeslot))+FLOOR((153*((DATEPART(MONTH,
                    StartTimeslot))+12*FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12))-3)+2)/5)+365*((DATEPART(YEAR,
                    StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12)))+FLOOR(((DATEPART(YEAR,
                    StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12)))/4)-FLOOR(((DATEPART(YEAR,
                    StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12)))/100)+FLOOR(((DATEPART(YEAR,
                    StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR,
                    StartTimeslot))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE,
                    StartTimeslot))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND,
                    StartTimeslot))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND,
                    StartTimeslot))/100)/10000)/(24*60*60) AS C02,
                    0 AS C03,
                    0 AS C04,
                    0 AS C05,
                    0 AS C06,
                    0 AS C07,
                    0 AS C08,
                    0 AS C09,
                    0 AS C10,
                    0 AS C11,
                    0 AS C12,
                    0  AS C13,
                    ((DATEPART(DAY,
                    EndTimeslot))+FLOOR((153*((DATEPART(MONTH,
                    EndTimeslot))+12*FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12))-3)+2)/5)+365*((DATEPART(YEAR,
                    EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12)))+FLOOR(((DATEPART(YEAR,
                    EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12)))/4)-FLOOR(((DATEPART(YEAR,
                    EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12)))/100)+FLOOR(((DATEPART(YEAR,
                    EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR,
                    EndTimeslot))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE,
                    EndTimeslot))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND,
                    EndTimeslot))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND,
                    EndTimeslot))/100)/10000)/(24*60*60) AS C14,
                    0 AS C15,
                    0 AS C16,
                    0 AS C17,
                    0 AS C18,
                    0 AS C19,
                    0 AS C20,
                    0 AS C21,
                    0 AS C22,
                    0 AS C23,
                    0 AS C24,
                    0  AS C25,
                    (((DATEPART(DAY,
                    EndTimeslot))+FLOOR((153*((DATEPART(MONTH,
                    EndTimeslot))+12*FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12))-3)+2)/5)+365*((DATEPART(YEAR,
                    EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12)))+FLOOR(((DATEPART(YEAR,
                    EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12)))/4)-FLOOR(((DATEPART(YEAR,
                    EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12)))/100)+FLOOR(((DATEPART(YEAR,
                    EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    EndTimeslot)))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR,
                    EndTimeslot))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE,
                    EndTimeslot))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND,
                    EndTimeslot))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND,
                    EndTimeslot))/100)/10000)/(24*60*60))-(((DATEPART(DAY,
                    StartTimeslot))+FLOOR((153*((DATEPART(MONTH,
                    StartTimeslot))+12*FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12))-3)+2)/5)+365*((DATEPART(YEAR,
                    StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12)))+FLOOR(((DATEPART(YEAR,
                    StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12)))/4)-FLOOR(((DATEPART(YEAR,
                    StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12)))/100)+FLOOR(((DATEPART(YEAR,
                    StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH,
                    StartTimeslot)))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR,
                    StartTimeslot))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE,
                    StartTimeslot))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND,
                    StartTimeslot))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND,
                    StartTimeslot))/100)/10000)/(24*60*60)) AS C26 ,
                    TvChannelId AS C27 
                  FROM
                    TvExposures) AS A 
                INNER JOIN
                  (
                    SELECT
                      1 AS rownum,
                      2122339536000000 AS C01,
                      2013 AS C02,
                      3 AS C03,
                      28 AS C04,
                      0 AS C05,
                      0 AS C06,
                      0 AS C07,
                      0 AS C08,
                      18 AS C09,
                      0 AS C10,
                      2013 AS C11,
                      118 AS C12,
                      2122365456000000 AS C13,
                      2013 AS C14,
                      4 AS C15,
                      28 AS C16,
                      0 AS C17,
                      0 AS C18,
                      0 AS C19,
                      0 AS C20,
                      22 AS C21,
                      2 AS C22,
                      2013 AS C23,
                      148 AS C24,
                      25920000000 AS C25
                  ) AS B 
                    ON A.C14 > B.C01 
                    AND A.C02 < B.C13
                  ) AS A 
              INNER JOIN
                (
                  SELECT
                    0 AS rownum,
                    RespondentId AS C01,
                    MediaUniverseId AS C02,
                    ((DATEPART(DAY,
                    CAST(StartDate AS DATETIME)))+FLOOR((153*((DATEPART(MONTH,
                    CAST(StartDate AS DATETIME)))+12*FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12))-3)+2)/5)+365*((DATEPART(YEAR,
                    CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12)))+FLOOR(((DATEPART(YEAR,
                    CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12)))/4)-FLOOR(((DATEPART(YEAR,
                    CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12)))/100)+FLOOR(((DATEPART(YEAR,
                    CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR,
                    CAST(StartDate AS DATETIME)))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE,
                    CAST(StartDate AS DATETIME)))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND,
                    CAST(StartDate AS DATETIME)))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND,
                    CAST(StartDate AS DATETIME)))/100)/10000)/(24*60*60) AS C03,
                    0 AS C04,
                    0 AS C05,
                    0 AS C06,
                    0 AS C07,
                    0 AS C08,
                    0 AS C09,
                    0 AS C10,
                    0 AS C11,
                    0 AS C12,
                    0 AS C13,
                    0  AS C14,
                    ((DATEPART(DAY,
                    CAST(EndDate AS DATETIME)))+FLOOR((153*((DATEPART(MONTH,
                    CAST(EndDate AS DATETIME)))+12*FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12))-3)+2)/5)+365*((DATEPART(YEAR,
                    CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12)))+FLOOR(((DATEPART(YEAR,
                    CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12)))/4)-FLOOR(((DATEPART(YEAR,
                    CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12)))/100)+FLOOR(((DATEPART(YEAR,
                    CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR,
                    CAST(EndDate AS DATETIME)))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE,
                    CAST(EndDate   AS DATETIME)))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND,
                    CAST(EndDate AS DATETIME)))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND,
                    CAST(EndDate AS DATETIME)))/100)/10000)/(24*60*60) AS C15,
                    0 AS C16,
                    0 AS C17,
                    0 AS C18,
                    0 AS C19,
                    0 AS C20,
                    0 AS C21,
                    0 AS C22,
                    0 AS C23,
                    0 AS C24,
                    0 AS C25,
                    0  AS C26,
                    (((DATEPART(DAY,
                    CAST(EndDate AS DATETIME)))+FLOOR((153*((DATEPART(MONTH,
                    CAST(EndDate AS DATETIME)))+12*FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12))-3)+2)/5)+365*((DATEPART(YEAR,
                    CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12)))+FLOOR(((DATEPART(YEAR,
                    CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12)))/4)-FLOOR(((DATEPART(YEAR,
                    CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12)))/100)+FLOOR(((DATEPART(YEAR,
                    CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(EndDate AS DATETIME))))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR,
                    CAST(EndDate AS DATETIME)))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE,
                    CAST(EndDate AS DATETIME)))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND,
                    CAST(EndDate AS DATETIME)))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND,
                    CAST(EndDate AS DATETIME)))/100)/10000)/(24*60*60))-(((DATEPART(DAY,
                    CAST(StartDate AS DATETIME)))+FLOOR((153*((DATEPART(MONTH,
                    CAST(StartDate AS DATETIME)))+12*FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12))-3)+2)/5)+365*((DATEPART(YEAR,
                    CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12)))+FLOOR(((DATEPART(YEAR,
                    CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12)))/4)-FLOOR(((DATEPART(YEAR,
                    CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12)))/100)+FLOOR(((DATEPART(YEAR,
                    CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH,
                    CAST(StartDate AS DATETIME))))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR,
                    CAST(StartDate AS DATETIME)))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE,
                    CAST(StartDate AS DATETIME)))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND,
                    CAST(StartDate AS DATETIME)))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND,
                    CAST(StartDate AS DATETIME)))/100)/10000)/(24*60*60)) AS C27 ,
                    Weight AS C28 
                  FROM
                    RespondentWeights
                ) AS B 
                  ON A.C14 > B.C03 
                  AND A.C02 < B.C15 
                  AND A.C01 = B.C01 
                  AND B.C02 = 100
                ) AS A 
              ) AS A ) AS A  
        GROUP BY
          C02,
          C03,
          C04) AS A 
        WHERE
          (C01) >= (3000000)
      ) AS A  
      GROUP BY
        C03) AS A ) AS T