0

I have a query that written in two ways, one using derived table and other in cross apply.As per IO count and execution plan , derived table comparatively showing good numbers in scan count,logical count and execution plan percentage. But the subquery executed in 2 min while cross apply took 1 min.

What are basic determination on query performance comparison?

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 11 ms.

(22050 row(s) affected) Table '#bigger_products____________________________________________________________________________________________________000000000015'. Scan count 9, logical reads 55, physical reads 1, read-ahead reads 11, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#bigger_transactions________________________________________________________________________________________________000000000016'. Scan count 9, logical reads 291252, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Workfile'. 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 '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.

Derived Table

SQL Server Execution Times: CPU time = 140499 ms, elapsed time = 196356 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 14 ms.

CROSS APPLY

(22050 row(s) affected) Table '#bigger_transactions________________________________________________________________________________________________000000000016'. Scan count 2520, logical reads 303885, physical reads 0, read-ahead reads 15, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#bigger_products____________________________________________________________________________________________________000000000015'. Scan count 9, logical reads 55, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 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.

SQL Server Execution Times: CPU time = 70094 ms, elapsed time = 106364 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

USE [AdventureWorks2014]

--(2520 row(s) affected)

SELECT p.Name, p.ProductID + x.v AS ProductID INTO #bigger_products FROM Production.Product AS p CROSS JOIN ( VALUES (0),(1000),(2000),(3000),(4000) ) AS x(v);

CREATE CLUSTERED INDEX i_ProductID ON #bigger_products (ProductID); GO

-- (57175272 row(s) affected)

SELECT th.ProductID + x.v AS ProductID, th.ActualCost, th.TransactionDate INTO #bigger_transactions FROM Production.TransactionHistory AS th CROSS JOIN ( SELECT (NTILE(5) OVER (ORDER BY p.ProductId) - 1) * 1000 FROM Production.Product AS p ) AS x(v);

CREATE CLUSTERED INDEX i_ProductID ON #bigger_transactions (ProductID); GO

-- (22050 row(s) affected)

-- 2 min 56 sec

SELECT p.Name, x.TransactionDate, x.ActualCost FROM ( SELECT th.ProductID, th.TransactionDate, th.ActualCost, ROW_NUMBER() OVER ( PARTITION BY th.ProductId ORDER BY th.ActualCost DESC ) AS rn FROM #bigger_transactions AS th ) AS x INNER JOIN #bigger_products AS p ON p.ProductID = x.ProductID WHERE x.rn <= 10; GO

-- (22050 row(s) affected)

-- 1 Min 10

SELECT p.Name, x.TransactionDate, x.ActualCost FROM #bigger_products AS p CROSS APPLY ( SELECT th.TransactionDate, th.ActualCost, ROW_NUMBER() OVER ( ORDER BY th.ActualCost DESC ) AS rn FROM #bigger_transactions AS th WHERE th.ProductID = p.ProductID ) AS x WHERE x.rn <= 10;

GO

enter image description here

  • 1
    Can you post both queries and their execution plans? Use pastetheplan for the execution plans. – Tom V Jan 17 '18 at 8:30
  • Or on a more friendly site (not blocked in corporate) - brentozar.com/pastetheplan – Kin Jan 17 '18 at 15:19

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.