Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# re: Competition

very interesting...
2/28/2009 7:45 PM | MS SQL FREELANCE DBA
Gravatar

# re: Competition

My first attempt takes 2h 56mins to execute when it fails because tempdb has eaten all my disk space :-)
3/3/2009 9:14 AM | Rolf
Gravatar

# re: Competition

Keep trying!
3/3/2009 9:21 AM | Peso
Gravatar

# re: Competition

Our solutions are very similar! I was happy to get mine to run under 5 seconds so I stopped tunning when I hit something around 3 seconds. If I had spent a few more hours tunning we probably whould have had the exact same execution plan.

Any way, you've got me beat by a solid 1.5 seconds and many thousand reads.
4/1/2009 5:47 PM | Jacob B
Gravatar

# re: Competition

Well, I ran your code on the same computer as the results above and I got 2.6 seconds and 496k reads with Profiler.

Statistics are

Table 'Product'. Scan count 0, logical reads 242634, physical reads 0.
Table 'SalesOrderDetail'. Scan count 31466, logical reads 97140, physical reads 0.
Table 'SalesOrderHeader'. Scan count 76478, logical reads 154558, 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.
4/3/2009 8:12 AM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET