Thinking outside the box

Competition

Adam Machanic has a nice competition going on here http://sqlblog.com/blogs/adam_machanic/archive/2009/02/27/t-sql-challenge-grouped-string-concatenation.aspx until the 16th of March 2009.

Please join in! The prize, for the best submission, is a full MSDN subscription, valued at around $10,000. How's that for inspiration!

My first attempt runs for 1.9 seconds and uses 418k reads.
My second attempt runs for 2.1 seconds and uses 334k reads.

Table 'Product'. Scan count 0, logical reads 158866, physical reads 0.
Table 'SalesOrderDetail'. Scan count 31466, logical reads 97140, physical reads 0.
Table 'SalesOrderHeader'. Scan count 38239, logical reads 77321, physical reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
Table 'Contact'. Scan count 1, logical reads 569, physical reads 0.

SQL Server Execution Times:
 CPU time = 1937 ms, elapsed time = 2107 ms.

Deadline has expired so I now can share my suggestion (second attempt). My first attempt is almost exact the same, but without the derived table sod in the CROSS APPLY q table.

SELECT      c.CustomerID,
            c.FirstName,
            c.LastName,
            c.OrderCount,
            c.TotalDollarAmount,
            c.TotalProductQuantity,
            STUFF(p.n, 1, 1, '') AS OrderNumbers,
            STUFF(q.n, 1, 1, '') AS ProductNames
FROM        (
                SELECT      soh.CustomerID,
                            c.FirstName,
                            c.LastName,
                            COUNT(soh.SalesOrderNumber) AS OrderCount,
                            SUM(soh.SubTotal) AS TotalDollarAmount,
                            SUM(sod.TotalProductQuantity) AS TotalProductQuantity
                FROM        Person.Contact AS c
                INNER JOIN  Sales.SalesOrderHeader AS soh ON soh.ContactID = c.ContactID
                INNER JOIN  (
                                SELECT      SalesOrderID,
                                            SUM(OrderQty) AS TotalProductQuantity
                                FROM        Sales.SalesOrderDetail
                                GROUP BY    SalesOrderID
                            ) AS sod ON sod.SalesOrderID = soh.SalesOrderID
                GROUP BY    soh.CustomerID,
                            c.FirstName,
                            c.LastName
            ) AS c
CROSS APPLY (
                SELECT      ',' + soh.SalesOrderNumber
                FROM        Sales.SalesOrderHeader AS soh
                WHERE       soh.CustomerID = c.CustomerID
                ORDER BY    ',' + soh.SalesOrderNumber
                FOR XML     PATH('')
            ) AS p(n)
CROSS APPLY (
                SELECT      ',' + p.Name
                FROM        (
                                SELECT      sod.ProductID
                                FROM        Sales.SalesOrderHeader AS soh
                                INNER JOIN  Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID
                                WHERE       soh.CustomerID = c.CustomerID
                                GROUP BY    sod.ProductID
                            ) AS sod
                INNER JOIN  Production.Product AS p ON p.ProductID = sod.ProductID
                ORDER BY    ',' + p.Name
                FOR XML     PATH('')
            ) AS q(n)

Print | posted on Saturday, February 28, 2009 4:29 PM | Filed Under [ Optimization ]