Of course, PIVOting uses up some CPU. However, if the data is dense (all the cells in the pivoted result set are not NULL), then the size of the pivoted result set may be significantly less. As a result, the overall time to retrieve a pivoted result set and transmit it over the network may be less. Here are my benchmarks.
Let us create a helper table with 1M numbers:
CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO
TRUNCATE TABLE dbo.Numbers;
INSERT INTO dbo.Numbers
( n )
VALUES ( 1
);
GO
DECLARE @i INT;
SET @i=0;
WHILE @i<21
BEGIN
INSERT INTO dbo.Numbers
( n )
SELECT n + POWER(2, @i)
FROM dbo.Numbers;
SET @i = @i + 1;
END;
Let us create and populate an typical EAV table:
CREATE TABLE dbo.ObjectAttributes(ObjectID INT NOT NULL, AttributeID INT NOT NULL, IntValue INT NOT NULL,
CONSTRAINT PK_ObjectAttributes PRIMARY KEY(ObjectID, AttributeID));
GO
TRUNCATE TABLE dbo.ObjectAttributes;
INSERT INTO dbo.ObjectAttributes(ObjectID, AttributeID, IntValue)
SELECT n1.n, n2.n, n1.n
FROM dbo.Numbers AS n1
CROSS JOIN
dbo.Numbers AS n2
WHERE n1.n <100001
AND n2.n<11;
Let us select all the data from it as is a couple of times:
SET STATISTICS TIME ON;
SELECT *
FROM dbo.ObjectAttributes;
SELECT *
FROM dbo.ObjectAttributes;
SQL Server Execution Times:
CPU time = 157 ms, elapsed time = 8369 ms.
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 5935 ms.
Let us select pivoted data:
SELECT ObjectID, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
FROM dbo.ObjectAttributes
PIVOT( MAX(IntValue) FOR AttributeID IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS pvt
ORDER BY pvt.ObjectID;
SQL Server Execution Times:
CPU time = 1219 ms, elapsed time = 1794 ms.
I was inspired by the following Aaron's post: