4

I am optimizing a query, where I have to join two tables, let's say Product and TransactionHistory table, and return multiple columns from both table for the last TransactionDate from the TransactionHistory table for each Product in the Product table.

TransactionHistory table has around 13 Million rows and the Product table has almost 2 million rows.

Sounds pretty easy, doesn't it? but for different scenarios different query perform differently.

I am not using my actual table names here, to show the different query syntax I have tried, I am using Adam Machanic's BigProduct and BigSalesHistory Tables.


The query was originally written like....

SELECT p.ProductID
     , p.Name
     , h.Quantity
     , h.TransactionDate
FROM [dbo].[bigProduct] p
INNER JOIN [dbo].[bigTransactionHistory] h ON p.ProductID = h.ProductID
WHERE h.TransactionDate = ( SELECT MAX(TransactionDate)
                            FROM [dbo].[bigTransactionHistory]
                            WHERE ProductID = h.ProductID )
AND p.ProductID > 1317
AND p.ProductID < 1416
GO

Which resulted in some 30 scan counts and almost 300,000 logical reads.


I being a big fan of windowing functions wrote the following queries using ROW_NUMBER(), both backfired badly:

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
INNER JOIN ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID 
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
            ) c
            ON p.ProductID = c.ProductID AND rn = 1
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

AND

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT  h.ProductID
                   , h.Quantity
                   , h.TransactionDate
                   , ROW_NUMBER() OVER (PARTITION BY h.ProductID 
                                            ORDER BY h.TransactionDate DESC) rn
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = ProductID
            ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
  AND rn = 1
GO

The Scan count went into 100s and the logical reads jumped to 700,000s.


Being pretty disappointed by the windowing function's performance I rewrote the query using CROSS APPLY

SELECT p.ProductID
     , p.Name
     , c.*
FROM [dbo].[bigProduct] p
CROSS APPLY ( SELECT TOP 1  h.ProductID
                          , h.Quantity
                          , h.TransactionDate
              FROM [dbo].[bigTransactionHistory] h
              WHERE p.ProductID = h.ProductID
              ORDER BY h.TransactionDate 
             ) c
WHERE p.ProductID > 1317
  AND p.ProductID < 1416
GO

Now this query seems to perform the best out of all the above mentioned but as soon as the OUTER Query's Range for ProductID increases this query goes horribly wrong and starts performing worse than the above two.

Also even though it reduced the logical reads down to 27000 but the CPU time and the elapsed time jumped up by 8 times.


Is there a more efficient way of writing a query like this, keeping in mind that the outer query range for ProductID varies a lot?.

Any suggestion or pointers in the right direction are much appreciated. Thank you.

  • 2
    No matter how fancy you get with syntax, indexes are going to drive performance here, particularly the ordering by TransactionDate. – Erik Darling Feb 4 '17 at 23:38
  • @sp_BlitzErik I was planning to ask this question to you guys on your daily webcast :) thanks for responding here, anyway in your opinion reducing logical reads for increasing CPU time is it a good trade off you reckon ? – M.Ali Feb 4 '17 at 23:45
  • I'm glad you asked it here! This is one of those questions that works way better on here than Office Hours. Generally when you reduce reads, you also reduce CPU. Though I suppose it depends a bit on if the CPU time is parallel or not. – Erik Darling Feb 4 '17 at 23:55
  • @sp_BlitzErik this is what I have always seen and expect to see reduced reads and reduced CPU too, but here I have managed to reduce the logical reads from 700,000 to nearly 27000 but the CPU time has gone up by almost 8 times :S and query also takes longer to finish. I have also managed to get rid of the expensive Sort operator from the execution plan which was spilling over to tempdb but still query is slow"er" and CPU cost is a lot more than before. – M.Ali Feb 4 '17 at 23:59
  • 2
    Can you post the plans and indexes? – Erik Darling Feb 5 '17 at 0:16
2

You need to think about how you would like to solve this if you had a paper-based system.

Like - do you want to use each ProductID one by one and find the newest one? Then your Nested Loop that calls a Seek using TOP 1 on an Index on (ProductID, Transaction Date DESC) will be best.

Do you want to search through all your transactions and group them by ProductID and grab the max? Then that approach could be best.

But I'd suggest to you that what you'd do in real life is to do the second approach on a subset of your data (such as the most recent month), and then do a product-by-product search on the ones that didn't work. Try an outer join onto a subquery that groups by ProductID and filters by TransactionDate to "fairly recent". Then do an Outer Apply to do a Top 1, but include a Predicate in there like where m.ProductID IS NULL, so that it only does those Seeks for products that were missed in the first round. You should see a Filter with a Startup Expression Predicate, and notice that the Index Seek is executed a lot fewer times.

I show this kind of thing at http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/ - look for the plan I show, on the example with two Outer Apply bits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.