• Adam Machanic (12/29/2010)


    Have you compared the performance of doing a median calculation w/compression against performing the same calculation using Joe Celko's ROW_NUMBER technique?

    http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx

    Compression is far from cheap, and I suspect that the ROW_NUMBER solution will scale much better. But that's just a suspicion and I don't have time to test myself at the moment.

    Hi Adam. Thanks for mentioning that article as I had not seen it. I did just test this against the AdventureWorks2008 DB using SQL Server 2008 SP2 using the Celko method, Ben-Gan method, and my CLR UDA. The CLR method has the same number of reads as the Celko method but has slightly higher CPU and Elapsed times as compared to both Celko and Ben-Gan methods.

    So yes, the pure T-SQL methods do appear to be slightly more efficient. However, for the record I did not say that compression was cheap. In fact, I did say that it would be slightly less efficient to do that but that it might prove to be a worthwhile trade-off depending on the circumstances.

    My testing is as follows (the first two queries are copied directly from your blog that you noted above):

    ------------------------------------------------------------------------

    DBCC DROPCLEANBUFFERS

    DBCC FREEPROCCACHE

    SET STATISTICS IO ON

    SET STATISTICS TIME ON

    SELECT

    CustomerId,

    AVG(TotalDue)

    FROM

    (

    SELECT

    CustomerId,

    TotalDue,

    ROW_NUMBER() OVER (

    PARTITION BY CustomerId

    ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc,

    ROW_NUMBER() OVER (

    PARTITION BY CustomerId

    ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc

    FROM Sales.SalesOrderHeader SOH

    ) x

    WHERE

    RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)

    GROUP BY CustomerId

    ORDER BY CustomerId;

    SET STATISTICS IO OFF

    SET STATISTICS TIME OFF

    -- logical reads 686

    -- CPU time = 47 ms, elapsed time = 1087 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)

    -- CPU time = 62 ms, elapsed time = 235 ms. (when cached)

    ------------------------------------------------------------------------

    DBCC DROPCLEANBUFFERS

    DBCC FREEPROCCACHE

    SET STATISTICS IO ON

    SET STATISTICS TIME ON

    SELECT

    CustomerId,

    AVG(TotalDue)

    FROM

    (

    SELECT

    CustomerId,

    TotalDue,

    ROW_NUMBER() OVER (

    PARTITION BY CustomerId

    ORDER BY TotalDue) AS RowNum,

    COUNT(*) OVER (

    PARTITION BY CustomerId) AS RowCnt

    FROM Sales.SalesOrderHeader

    ) x

    WHERE

    RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)

    GROUP BY CustomerId

    ORDER BY CustomerId;

    SET STATISTICS IO OFF

    SET STATISTICS TIME OFF

    -- logical reads 686 + 139407 for Worktable = 140093 total

    -- CPU time = 344 ms, elapsed time = 1085 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)

    -- CPU time = 328 ms, elapsed time = 374 ms. (when cached)

    ------------------------------------------------------------------------

    DBCC DROPCLEANBUFFERS

    DBCC FREEPROCCACHE

    SET STATISTICS IO ON

    SET STATISTICS TIME ON

    SELECTCustomerId,

    SQL#.SQL#.Agg_Median(TotalDue)

    FROM Sales.SalesOrderHeader SOH

    GROUP BY CustomerId

    ORDER BY CustomerId;

    SET STATISTICS IO OFF

    SET STATISTICS TIME OFF

    -- logical reads 686

    -- CPU time = 1812 ms, elapsed time = 2707 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)

    -- CPU time = 1906 ms, elapsed time = 1948 ms. (when cached)

    ------------------------------------------------------------------------

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR