CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2pivot
MAK
MAK

Reputation: 7260

SQL Server 2008 R2: Pivot table query performance

Table: Product

create table Product
(
    productID int,
    productName varchar(20),
    productsalesdate DATETIME,
    producttype varchar(20)
);

Insertion:

insert into product values(1,'PenDrive','2010-01-01','Electronic');
insert into product values(1,'Computer','2016-01-01','Electronic');
insert into product values(1,'Laptop','2011-02-02','Electronic');
insert into product values(2,'textbook','2014-02-02','books');
insert into product values(2,'notebook','2016-01-01','books');
insert into product values(3,'Car','2016-01-01','Vehicle');
insert into product values(3,'Bike','2016-01-07','Vehicle');

First Try: In this I am getting wrong sum of productType 

SELECT productID, FirstSale,LastSale, [Electronic],[books],[Vehicle]
FROM
(
    SELECT 
        productID, 
        MIN(ProductSalesdate) as FirstSale,
        MAX(ProductSalesdate) as LastSale,
        productType
    FROM
        Product
    Group by productID,productType
) a 
PIVOT
(
    COUNT(productType) 
    FOR productType IN ( [Electronic],[books],[Vehicle] )
) AS pvt;

Second Try: In this try I have solved the sum problem but the query is taking more time for execute for huge records. 

SELECT productID,FirstSale,LastSale ,[Electronic],[books],[Vehicle]
FROM
(
    SELECT a.ProductID, a.FirstSale, a.LastSale, b.ProductType
    FROM Product b
    inner join
    (
        SELECT 
            productID, 
            MIN(ProductSalesdate) as FirstSale,
            MAX(ProductSalesdate) as LastSale
        FROM
            Product
        Group by productID
    ) as a 
    ON a.ProductID = b.ProductID
) ab 
PIVOT
(
    COUNT(productType) 
    FOR productType IN ( [Electronic],[books],[Vehicle] )
) AS pvt;

Note: The second query is works fine but the problem is with the performance, because of I am joining two same table because to get count of productType in the pivot query.

Question: How to optimize the second query which is a my second try?

Upvotes: 2

Views: 102

Answers (2)

JamieD77
JamieD77

Reputation: 13949

Seems like you're trying to do something like this.. Not sure why you'd need extra joins or temp tables..

SELECT * FROM
(
    SELECT  productID, 
            productType,
            MIN(ProductSalesdate) as FirstSale,
            MAX(ProductSalesdate) as LastSale,
            COUNT(productType) AS ProductCount
    FROM Product
    GROUP BY productID,productType
) t
PIVOT 
(
    SUM(ProductCount)
    FOR productType IN ([Electronic],[books],[Vehicle])
) p

you'll get NULLS for the 0 counts but you can coalesce those values to 0 pretty easily

Upvotes: 0

TT.
TT.

Reputation: 16146

The following uses a temporary table to store the derived table ab. My guess is it will improve the execution plan of the second query.

SELECT a.ProductID, a.FirstSale, a.LastSale, b.ProductType
INTO #ab
FROM Product b
inner join
(
    SELECT 
        productID, 
        MIN(ProductSalesdate) as FirstSale,
        MAX(ProductSalesdate) as LastSale
    FROM
        Product
    Group by productID
) as a 
ON a.ProductID = b.ProductID;

SELECT productID,FirstSale,LastSale ,[Electronic],[books],[Vehicle]
FROM #ab AS ab 
PIVOT
(
    COUNT(productType) 
    FOR productType IN ( [Electronic],[books],[Vehicle] )
) AS pvt;

DROP TABLE #ab;

EDIT: Just for sports I wrote following script which has 15k rows in #product. The whole script executes in ~1 second. I still don't understand how your query takes 5.5 minutes. Here goes:

SET NOCOUNT ON;

CREATE TABLE #product (
    product_id INT,
    product_name VARCHAR(20),
    product_sales_date DATE,
    product_type VARCHAR(20)
);

DECLARE @cnt INT=0;
WHILE @cnt<15000
BEGIN
    INSERT INTO #product(
        product_id,
        product_name,
        product_sales_date,
        product_type
    )
    SELECT 
        product_id=ROUND(20*RAND(),0),
        product_name=LEFT(NEWID(),20),
        product_sales_date=DATEADD(DAY,ROUND((-10+20*RAND()), 0),GETDATE()),
        product_type=
            CASE ROUND(2*RAND(),0)
                WHEN 0 THEN 'Electronic'
                WHEN 1 THEN 'books'
                ELSE 'Vehicle'
            END;

    SET @cnt=@cnt+1;
END

SELECT a.product_id, a.first_sale, a.last_sale, b.product_type
INTO #ab
FROM #product b
inner join
(
    SELECT 
        product_id, 
        MIN(product_sales_date) as first_sale,
        MAX(product_sales_date) as last_sale
    FROM
        #product
    GROUP BY
        product_id
) as a 
ON a.product_id= b.product_id;

SELECT product_id,first_sale,last_sale,[Electronic],[books],[Vehicle]
FROM #ab AS ab 
PIVOT
(
    COUNT(product_type) 
    FOR product_type IN ( [Electronic],[books],[Vehicle] )
) AS pvt;

DROP TABLE #ab;
DROP TABLE #product;

Upvotes: 1

Related Questions