• Luis Cazares (2/11/2014)


    Have you tested again?

    Luis - I completely flaked out and missed this message. I am truly sorry and feel like a jerk. I fixed the my code and re-tested it. The server I originally tested this on does not exist. On my laptop (4 cpu, 16gb ram, SQL 2014) using the code that I posted above I get the following results against 10M rows:

    crosstab, quarters only

    SQL Server Execution Times:

    CPU time = 3796 ms, elapsed time = 1106 ms.

    pivot, quarters only

    SQL Server Execution Times:

    CPU time = 6344 ms, elapsed time = 1703 ms.

    crosstab office and quarters

    SQL Server Execution Times:

    CPU time = 6297 ms, elapsed time = 1684 ms.

    pivot office and quarters

    SQL Server Execution Times:

    CPU time = 6374 ms, elapsed time = 1751 ms.

    I did 10M because the results are pretty close.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001