Rewrite query

• Jonathan AC Roberts

  SSCoach

  Points: 18361

  More actions

  September 17, 2018 at 12:48 pm

  #2006022

  Jason A. Long - Monday, September 17, 2018 11:54 AM

  It's only the fastest because it was the only horse in the race...
  Lets give it a little competition...

  Start by adding a POC index...
  --POC index...
CREATE NONCLUSTERED INDEX ix_SellerProductsSnapshots_POC
    ON dbo.SellerProductsSnapshots (
        IdSeller ASC,
        Date DESC
        )
    INCLUDE (
        ProductsCount,
        AvgPrice
        );


  Note: setting SSMS to "Discard results after execution" to prevent display rendering from impacting results..

  SET NOCOUNT ON;
GO

  GO
-- DBCC FLUSHPROCINDB(16) WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
--EXEC master.dbo.sp_DBCC_DropCleanBuffers_FreeProcCache;
PRINT(CONCAT(CHAR(13), CHAR(10), N'   wait a moment...', CHAR(13), CHAR(10)));
WAITFOR DELAY '00:00:01';
GO
-- SET STATISTICS XML ON;
GO
DECLARE @_clock_start DATETIME2(7) = SYSDATETIME(), @_test_name NVARCHAR(70) = N' ';
PRINT(CONCAT(N'â•”', REPLICATE(N'â•', 148), N'â•—
â•‘', LEFT(CONCAT(N'    Start Time: ', @_clock_start, N'   Test Name: ', ISNULL(NULLIF(@_test_name, N' '), N'Test Query 1'),
REPLICATE(N' ', 100)), 148), N'â•‘', CHAR(13), CHAR(10), N'â•š', REPLICATE(N'â•', 148), N'â•')); DECLARE @_data_dump_int INT; SET STATISTICS IO ON;
-- ____________________________________________________________________________________________________________________________________________
-- ⇩⇩⇩⇩⇩⇩ place tsql here ⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩
;WITH CTE AS
(
SELECT DISTINCT
  sps.IdSeller
FROM SellerProductsSnapshots sps
 WHERE EXISTS(SELECT *
   FROM #SellersTable st
   WHERE st.Id = sps.IdSeller)
)
SELECT sps.IdSeller,
 sps2.ProductsCount,
 sps2.AvgPrice
FROM CTE sps
CROSS APPLY(SELECT TOP(1)
   sps2.ProductsCount,
   sps2.AvgPrice,
   sps2.Date
  FROM SellerProductsSnapshots sps2
  WHERE sps2.IdSeller = sps.IdSeller
  ORDER BY sps2.Date DESC) AS sps2;
-- ⇧⇧⇧⇧⇧⇧ place tsql here ⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧
-- ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
DECLARE @_clock_stop DATETIME2(7) = SYSDATETIME(); SET STATISTICS IO OFF; PRINT(CONCAT(N'â•”', REPLICATE(N'â•', 148), N'â•—
â•‘', LEFT(STUFF(CONCAT(N'    Finish Time: ', @_clock_stop, N'   Duration: ', DATEDIFF(mcs, @_clock_start, @_clock_stop)
/ 1000000.0, N' secs.  ', DATEDIFF(mcs, @_clock_start, @_clock_stop) / 1000.0, N' ms.', REPLICATE(N' ', 100)), 76, 3, N''), 148), N'â•‘
â•š', REPLICATE(N'â•', 148), N'â•'));
GO
-- SET STATISTICS XML OFF;
GO
-- DBCC FLUSHPROCINDB(16) WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
--EXEC master.dbo.sp_DBCC_DropCleanBuffers_FreeProcCache;
PRINT(CONCAT(CHAR(13), CHAR(10), N'   wait a moment...', CHAR(13), CHAR(10)));
WAITFOR DELAY '00:00:01';
GO
-- SET STATISTICS XML ON;
GO
DECLARE @_clock_start DATETIME2(7) = SYSDATETIME(), @_test_name NVARCHAR(70) = N' ';
PRINT(CONCAT(N'â•”', REPLICATE(N'â•', 148), N'â•—                              
â•‘', LEFT(CONCAT(N'    Start Time: ', @_clock_start, N'   Test Name: ', ISNULL(NULLIF(@_test_name, N' '), N'Test Query 2'),
REPLICATE(N' ', 100)), 148), N'â•‘', CHAR(13), CHAR(10), N'â•š', REPLICATE(N'â•', 148), N'â•')); DECLARE @_data_dump_int INT; SET STATISTICS IO ON;    
-- ____________________________________________________________________________________________________________________________________________
-- ⇩⇩⇩⇩⇩⇩ place tsql here ⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩
WITH
    cte_AddRN AS (
        SELECT
            sps.IdSeller,
            sps.ProductsCount,
            sps.AvgPrice,
            sps.Date,
            rn= ROW_NUMBER() OVER (PARTITION BY sps.IdSeller ORDER BY sps.Date DESC)
        FROM
            dbo.SellerProductsSnapshots sps
        )
SELECT
    IdSeller = st.Id, arn.ProductsCount, arn.AvgPrice
FROM
    #SellersTable st
    INNER MERGE JOIN cte_AddRN arn    -- (since both tables are order on Id (IdSeller a MERGE join is appropriate...
        ON    st.Id = arn.IdSeller
WHERE
    arn.rn = 1;
-- ⇧⇧⇧⇧⇧⇧ place tsql here ⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧
-- ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
DECLARE @_clock_stop DATETIME2(7) = SYSDATETIME(); SET STATISTICS IO OFF; PRINT(CONCAT(N'â•”', REPLICATE(N'â•', 148), N'â•—
â•‘', LEFT(STUFF(CONCAT(N'    Finish Time: ', @_clock_stop, N'   Duration: ', DATEDIFF(mcs, @_clock_start, @_clock_stop)
/ 1000000.0, N' secs.  ', DATEDIFF(mcs, @_clock_start, @_clock_stop) / 1000.0, N' ms.', REPLICATE(N' ', 100)), 76, 3, N''), 148), N'â•‘
â•š', REPLICATE(N'â•', 148), N'â•'));
GO
-- SET STATISTICS XML OFF;
GO
-- DBCC FLUSHPROCINDB(16) WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
--EXEC master.dbo.sp_DBCC_DropCleanBuffers_FreeProcCache;
PRINT(CONCAT(CHAR(13), CHAR(10), N'   wait a moment...', CHAR(13), CHAR(10)));
WAITFOR DELAY '00:00:01';
GO
-- SET STATISTICS XML ON;
GO
DECLARE @_clock_start DATETIME2(7) = SYSDATETIME(), @_test_name NVARCHAR(70) = N' ';
PRINT(CONCAT(N'â•”', REPLICATE(N'â•', 148), N'â•—                              
â•‘', LEFT(CONCAT(N'    Start Time: ', @_clock_start, N'   Test Name: ', ISNULL(NULLIF(@_test_name, N' '), N'Test Query 3'),
REPLICATE(N' ', 100)), 148), N'â•‘', CHAR(13), CHAR(10), N'â•š', REPLICATE(N'â•', 148), N'â•')); DECLARE @_data_dump_int INT; SET STATISTICS IO ON;    
-- ____________________________________________________________________________________________________________________________________________
-- ⇩⇩⇩⇩⇩⇩ place tsql here ⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩⇩
SELECT
    IdSeller = st.Id,
    ProductsCount = CONVERT(INT, SUBSTRING(spsx.max_bin_val, 9, 4)),
    AvgPrice = CONVERT(MONEY, SUBSTRING(spsx.max_bin_val, 13, 8))
FROM
    #SellersTable st
    CROSS APPLY (
        SELECT
            MAX(CONVERT(BINARY(8), sps.Date) + CONVERT(BINARY(4), sps.ProductsCount) + CONVERT(BINARY(8), sps.AvgPrice))
        FROM
            dbo.SellerProductsSnapshots sps
        WHERE
            st.Id = sps.IdSeller
        ) spsx (max_bin_val);
-- ⇧⇧⇧⇧⇧⇧ place tsql here ⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧⇧
-- ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
DECLARE @_clock_stop DATETIME2(7) = SYSDATETIME(); SET STATISTICS IO OFF; PRINT(CONCAT(N'â•”', REPLICATE(N'â•', 148), N'â•—
â•‘', LEFT(STUFF(CONCAT(N'    Finish Time: ', @_clock_stop, N'   Duration: ', DATEDIFF(mcs, @_clock_start, @_clock_stop)
/ 1000000.0, N' secs.  ', DATEDIFF(mcs, @_clock_start, @_clock_stop) / 1000.0, N' ms.', REPLICATE(N' ', 100)), 76, 3, N''), 148), N'â•‘
â•š', REPLICATE(N'â•', 148), N'â•'));
GO
-- SET STATISTICS XML OFF;
GO


  The results

     wait a moment...
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Start Time: 2018-09-17 13:40:04.7958330   Test Name: Test Query 1                       â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
  Table 'SellerProductsSnapshots'. Scan count 1000001, logical reads 3196940, physical reads 1, read-ahead reads 4719, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#SellersTable_______________________________________________________________________________________________________0000000049C6'. Scan count 1, logical reads 2733, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Finish Time: 2018-09-17 13:40:08.8240652   Duration: 4.028232 secs.  4028.232000 ms.                â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•

     wait a moment...
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—                              
  â•‘    Start Time: 2018-09-17 13:40:10.0431337   Test Name: Test Query 2                       â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'SellerProductsSnapshots'. Scan count 1, logical reads 4718, physical reads 1, read-ahead reads 4711, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#SellersTable_______________________________________________________________________________________________________0000000049C6'. Scan count 1, logical reads 2733, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Finish Time: 2018-09-17 13:40:12.2462594   Duration: 2.203126 secs.  2203.126000 ms.                â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•

     wait a moment...
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—                              
  â•‘    Start Time: 2018-09-17 13:40:13.5003326   Test Name: Test Query 3                       â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
  Table '#SellersTable_______________________________________________________________________________________________________0000000049C6'. Scan count 1, logical reads 2733, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'SellerProductsSnapshots'. Scan count 1, logical reads 4718, physical reads 1, read-ahead reads 4711, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
  â•‘    Finish Time: 2018-09-17 13:40:15.2564298   Duration: 1.756097 secs.  1756.097000 ms.                â•‘
  â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
  

  Your queries assume that Id is unique on #SellersTable. If it isn't (it states nowhere that it is) your queries don't work.
  For example if you set up the test data like this:
  set statistics io,time off
IF OBJECT_ID('tempdb..#SellersTable') IS NOT NULL
DROP TABLE #SellersTable
GO
DECLARE @RowsToInsert as int = 1000000
;WITH A(A) AS
(SELECT 'Anything' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
B(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1 A))-1 FROM A A, A B, A C, A D, A E, A F, A G, A H)
SELECT TOP(@RowsToInsert) N Id
INTO #SellersTable
FROM B

  INSERT INTO #SellersTable
SELECT TOP(@RowsToInsert/2) * FROM #SellersTable
GO
IF OBJECT_ID('dbo.SellerProductsSnapshots') IS NOT NULL
DROP TABLE SellerProductsSnapshots

  CREATE TABLE SellerProductsSnapshots
(
IdSeller int NOT NULL,
ProductsCount int NOT NULL,
AvgPrice money NOT NULL,
Date datetime NOT NULL
)
GO
INSERT INTO SellerProductsSnapshots
(
IdSeller,
ProductsCount,
AvgPrice,
Date
)
SELECT Id,
 ABS(CHECKSUM(NewId())) % 50 + 5,
 ABS(CHECKSUM(NewId())) % 100,
 DATEADD(dd,ABS(CHECKSUM(NewId())) % 100,'20180101')
FROM #SellersTable
GO 6

  CREATE CLUSTERED INDEX IX_SellerProductsSnapshots_1 ON SellerProductsSnapshots(IdSeller,[Date],ProductsCount,AvgPrice);
CREATE CLUSTERED INDEX IX_#SellersTable_1 ON #SellersTable(Id);
  And then run this code:
  if object_id('tempdb..#1') is not null
    DROP TABLE #1
if object_id('tempdb..#2') is not null
    DROP TABLE #2
if object_id('tempdb..#3') is not null
    DROP TABLE #3
GO

  DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
set statistics io,time on
GO
PRINT 'Query 1 *************************************************************************************************************'
;WITH CTE AS
(
SELECT DISTINCT
 sps.IdSeller
FROM SellerProductsSnapshots sps
WHERE EXISTS(SELECT *
  FROM #SellersTable st
 WHERE st.Id = sps.IdSeller)
)
SELECT sps.IdSeller,
sps2.ProductsCount,
sps2.AvgPrice
INTO #1
FROM CTE sps
CROSS APPLY(SELECT TOP(1)
  sps2.ProductsCount,
  sps2.AvgPrice,
  sps2.Date
 FROM SellerProductsSnapshots sps2
WHERE sps2.IdSeller = sps.IdSeller
ORDER BY sps2.Date DESC) AS sps2;
GO
PRINT 'Query 1 End *************************************************************************************************************'
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
PRINT 'Query 2 *************************************************************************************************************'
GO
;WITH
  cte_AddRN AS (
   SELECT
    sps.IdSeller,
    sps.ProductsCount,
    sps.AvgPrice,
    sps.Date,
    rn= ROW_NUMBER() OVER (PARTITION BY sps.IdSeller ORDER BY sps.Date DESC)
   FROM
    dbo.SellerProductsSnapshots sps
   )
SELECT
  IdSeller = st.Id, arn.ProductsCount, arn.AvgPrice
INTO #2
FROM
  #SellersTable st
  INNER MERGE JOIN cte_AddRN arn  -- (since both tables are order on Id (IdSeller a MERGE join is appropriate...
   ON  st.Id = arn.IdSeller
WHERE
  arn.rn = 1;
PRINT 'Query 2 End *************************************************************************************************************'
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
PRINT(CONCAT(CHAR(13), CHAR(10), N'  wait a moment...', CHAR(13), CHAR(10)));
GO
PRINT 'Query 3 *************************************************************************************************************'
SELECT
  IdSeller = st.Id,
  ProductsCount = CONVERT(INT, SUBSTRING(spsx.max_bin_val, 9, 4)),
  AvgPrice = CONVERT(MONEY, SUBSTRING(spsx.max_bin_val, 13, 8))
INTO #3
FROM
  #SellersTable st
  CROSS APPLY (
   SELECT
    MAX(CONVERT(BINARY(8), sps.Date) + CONVERT(BINARY(4), sps.ProductsCount) + CONVERT(BINARY(8), sps.AvgPrice))
   FROM
    dbo.SellerProductsSnapshots sps
   WHERE
    st.Id = sps.IdSeller
   ) spsx (max_bin_val);
GO
PRINT 'Query 3 End *************************************************************************************************************'
set statistics io,time off

  select count(*) from #1
select count(*) from #2
select count(*) from #3
  You get this:
  
  Also the times I get are here:
  Query 1 - CPU time = 7984 ms,   elapsed time = 1513 ms.
  Query 2 - CPU time = 13358 ms, elapsed time = 3597 ms.
  Query 3 - CPU time = 7048 ms,   elapsed time = 1079 ms.

  If I'd assumed that Id was unique on #SellersTable I would have written my query like this:
  SELECT st.Id IdSeller,
       sps2.ProductsCount,
       sps2.AvgPrice
  FROM #SellersTable st
CROSS APPLY(SELECT TOP(1)
                    sps2.ProductsCount,
                    sps2.AvgPrice,
                    sps2.Date
               FROM SellerProductsSnapshots sps2
              WHERE sps2.IdSeller = st.Id
              ORDER BY sps2.Date DESC) AS sps2;
  Which I think beats all the other queries.

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  September 17, 2018 at 1:51 pm

  #2006048

  Why would you not assume that? 
  Given that it's a temp table there's a pretty high likelihood that it's being populated with a distinct set of values, specifically for this query.
  Doing unnecessary DISTINCT operations because "just in case", isn't kind to performance.

• Jonathan AC Roberts

  SSCoach

  Points: 18361

  More actions

  September 17, 2018 at 2:28 pm

  #2006051

  Jason A. Long - Monday, September 17, 2018 1:51 PM

  Why would you not assume that? 
  Given that it's a temp table there's a pretty high likelihood that it's being populated with a distinct set of values, specifically for this query.
  Doing unnecessary DISTINCT operations because "just in case", isn't kind to performance.

  Well maybe you're right to assume that and I'm not. But the result is we ended up with functionally different queries.

• Jonathan AC Roberts

  SSCoach

  Points: 18361

  More actions

  September 17, 2018 at 2:28 pm

  #2006052

  Jason A. Long - Monday, September 17, 2018 1:51 PM

  Why would you not assume that? 
  Given that it's a temp table there's a pretty high likelihood that it's being populated with a distinct set of values, specifically for this query.
  Doing unnecessary DISTINCT operations because "just in case", isn't kind to performance.

  Well maybe you're right to assume that and I'm not. But the result is we ended up with functionally different queries.

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  September 17, 2018 at 2:55 pm

  #2006058

  Jonathan AC Roberts - Monday, September 17, 2018 2:28 PM

  Jason A. Long - Monday, September 17, 2018 1:51 PM

  Why would you not assume that? 
  Given that it's a temp table there's a pretty high likelihood that it's being populated with a distinct set of values, specifically for this query.
  Doing unnecessary DISTINCT operations because "just in case", isn't kind to performance.

  Well maybe you're right to assume that and I'm not. But the result is we ended up with functionally different queries.

  How so? All 3 queries produce exactly 1M rows and a random spot check is showing identical ProductCount & AvgPrice values...

• Jonathan AC Roberts

  SSCoach

  Points: 18361

  More actions

  September 17, 2018 at 5:24 pm

  #2006068

  Jason A. Long - Monday, September 17, 2018 2:55 PM

  Jonathan AC Roberts - Monday, September 17, 2018 2:28 PM

  Jason A. Long - Monday, September 17, 2018 1:51 PM

  Why would you not assume that? 
  Given that it's a temp table there's a pretty high likelihood that it's being populated with a distinct set of values, specifically for this query.
  Doing unnecessary DISTINCT operations because "just in case", isn't kind to performance.

  Well maybe you're right to assume that and I'm not. But the result is we ended up with functionally different queries.

  How so? All 3 queries produce exactly 1M rows and a random spot check is showing identical ProductCount & AvgPrice values...

  Did you try the data setup in this reply? The queries return different results. Your queries produce 1.5 million rows. The queries are functionally different.

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  September 17, 2018 at 6:53 pm

  #2006069

  Jonathan AC Roberts - Monday, September 17, 2018 5:24 PM

  Jason A. Long - Monday, September 17, 2018 2:55 PM

  Jonathan AC Roberts - Monday, September 17, 2018 2:28 PM

  Jason A. Long - Monday, September 17, 2018 1:51 PM

  Why would you not assume that? 
  Given that it's a temp table there's a pretty high likelihood that it's being populated with a distinct set of values, specifically for this query.
  Doing unnecessary DISTINCT operations because "just in case", isn't kind to performance.

  Well maybe you're right to assume that and I'm not. But the result is we ended up with functionally different queries.

  How so? All 3 queries produce exactly 1M rows and a random spot check is showing identical ProductCount & AvgPrice values...

  Did you try the data setup in this reply? The queries return different results. Your queries produce 1.5 million rows. The queries are functionally different.

  Not the way I wrote them, they didn't. The The 3 queries I posted all produced 1M rows  apeace. I went back and reverified after you posted just to make sure. I'm not sure what you did to make it output 1.5M rows,

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  September 17, 2018 at 7:11 pm

  #2006070

  Jason A. Long - Monday, September 17, 2018 6:53 PM

  Not the way I wrote them, they didn't. The The 3 queries I posted all produced 1M rows  apeace. I went back and reverified after you posted just to make sure. I'm not sure what you did to make it output 1.5M rows,

  Actually, There is one thing that I can think of that may account for the descrepancy... I used you table setup code, including the values in the temp table. I made a point to verify uniqueness by comparing the count(*) value to the count(DISTINCT SellerId) value and came up with 1M for each... If you added half a mil of duplicates into the temp table on your end, that would account for the the difference... Being that you did include a DISTINCT step and I didn't.
  I don't know that to be the case but it's the only reasonable explination that comes to mind at the moment.