THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

PIVOting dense data may speed up your queires

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(INT NOT NULL PRIMARY KEY);
GO
            
TRUNCATE TABLE dbo.Numbers;
INSERT INTO dbo.Numbers
    
)
        
VALUES  1
    
);
GO
DECLARE @i INT;
    
SET @i=0;
WHILE @i<21 
    
BEGIN
    INSERT INTO 
dbo.Numbers
        
)
        
SELECT 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(ObjectIDAttributeID));
GO
            
TRUNCATE TABLE dbo.ObjectAttributes;
INSERT INTO dbo.ObjectAttributes(ObjectIDAttributeIDIntValue)
    
SELECT n1.nn2.nn1.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(IntValueFOR AttributeID IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS pvt
    
ORDER BY pvt.ObjectID;

Although the pivoted query used more CPU, the overall time was substantially less:

SQL Server Execution Times:
   CPU time = 1219 ms,  elapsed time = 1794 ms.
 

I was inspired by the following Aaron's post:

What is so bad about EAV, anyway?

 

 

 

 

 

 

Published Friday, November 20, 2009 1:53 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement