SQL Server pivot vs. multiple join

by

The answer will of course be “it depends” but based on testing this end…

Assuming

  1. 1 million products
  2. product has a clustered index on product_id
  3. Most (if not all) products have corresponding information in the product_code table
  4. Ideal indexes present on product_code for both queries.

The PIVOT version ideally needs an index product_code(product_id, type) INCLUDE (code) whereas the JOIN version ideally needs an index product_code(type,product_id) INCLUDE (code)

If these are in place giving the plans below

Plans

then the JOIN version is more efficient.

In the case that type 1 and type 2 are the only types in the table then the PIVOT version slightly has the edge in terms of number of reads as it doesn’t have to seek into product_code twice but that is more than outweighed by the additional overhead of the stream aggregate operator

PIVOT

Table 'product_code'. Scan count 1, logical reads 10467
Table 'product'. Scan count 1, logical reads 4750
   CPU time = 3297 ms,  elapsed time = 3260 ms.

JOIN

Table 'product_code'. Scan count 2, logical reads 10471
Table 'product'. Scan count 1, logical reads 4750
   CPU time = 1906 ms,  elapsed time = 1866 ms.

If there are additional type records other than 1 and 2 the JOIN version will increase its advantage as it just does merge joins on the relevant sections of the type,product_id index whereas the PIVOT plan uses product_id, type and so would have to scan over the additional type rows that are intermingled with the 1 and 2 rows.

More Related Contents:

  1. SQL Server Pivot Table with multiple column aggregates
  2. PIVOT in sql 2005
  3. SQL Server – Dynamic PIVOT Table – SQL Injection
  4. SQL Server: Examples of PIVOTing String data
  5. Check if table exists in SQL Server
  6. Simulation of CONNECT BY PRIOR of Oracle in SQL Server
  7. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  8. Using varchar(MAX) vs TEXT on SQL Server
  9. Do I really need to use “SET XACT_ABORT ON”?
  10. LIKE vs CONTAINS on SQL Server
  11. Creating audit triggers in SQL Server
  12. Regular Expressions in SQL Server servers?
  13. How do you specify a different port number in SQL Management Studio?
  14. Diagnosing Deadlocks in SQL Server 2005
  15. Disadvantages of MARS (Multiple Active Result Sets)?
  16. Aggregate bitwise-OR in a subquery
  17. cross apply xml query performs exponentially worse as xml document grows
  18. SQL Server PIVOT perhaps?
  19. Check if a temporary table exists and delete if it exists before creating a temporary table
  20. Drop primary key using script in SQL Server database [duplicate]
  21. sql use statement with variable
  22. Decimal values in SQL for dividing results
  23. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  24. Date range overlapping check constraint
  25. Bulk insert fixed width fields
  26. “Prevent saving changes that require the table to be re-created” negative effects
  27. Convert UTC Milliseconds to DATETIME in SQL server
  28. Table Valued Function Killing My Query Performance
  29. Pivot multiple columns based on one column in SQL Server
  30. @@ERROR and/or TRY – CATCH

Leave a Comment