CODEWITHSUNDEEP
sql-serversql-server-2008-r2pivot
MAK
MAK

Reputation: 7260

SQL Server 2008 R2: Dynamic Pivot table query performance

I have the following table with the records:

Example:

Table Name: Test

 cola   colb  colc
-------------------
 111    222   A1
 111    333   A2
 111    344   A3
 111    444   A4
 5443   555   B1
 767    222   A1
 767    333   A2
 767    344   A3
 8998   222   A1
 8998   333   A2

What I want to do with this data: I want to show the pivot table with some specific colc values which are belongs to cola values only not others. For example in the below script shown that A1,A2,A3 belongs to 767 exactly not more or less then that.

Note: The below script works fine with the expected result BUT the problem is with the query performance because I am joining many times the same table which is of millions of records which cause me waiting for long while loop.

Pivot table script:

Declare @sql varchar(max)
Declare @stuff varchar(max) = 'A1,A2,A3'

SET @sql = 'SELECT cola,Available,'+@Stff+'
           FROM                     
           (
                SELECT  v.cola,v.colc,c.Available
                FROM tft AS v   
                inner join
                (
                   select cola,count(distinct colc) AS Available
                   FROM tft a
                   where colc in ('+@stuff+')
                   group by cola
                   having(select count(DISTINCT colc) 
                   from tft b where b.cola= a.cola and colc in('+@stuff+'))= 3
                   and (select count(DISTINCT colc) from tft c where c.cola = a.cola) = 3
                ) c 
                on c.cola = v.cola
           ) p                     
           PIVOT                
           (            
                count(colc)                        
                FOR colc IN ('+@stuff+')                        
           ) AS pvt';   

print(@sql);
exec(@sql);

Problem: The problem is with the huge records which makes a delay for long while. Is there any better way to write same concept?

Upvotes: 0

Views: 380

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6771

Here's an alternate way, it avoids the DISTINCTs and uses a sum instead of the pivot function:

SELECT  orig.cola ,
        colas.counter ,
        SUM(CASE orig.colc WHEN 'A1' THEN 1 ELSE 0 END) AS A1 ,
        SUM(CASE orig.colc WHEN 'A2' THEN 1 ELSE 0 END) AS A2 ,
        SUM(CASE orig.colc WHEN 'A3' THEN 1 ELSE 0 END) AS A3
FROM    tft orig
        INNER JOIN ( SELECT cola ,
                            COUNT(1) AS counter
                     FROM   ( SELECT    cola ,
                                        colc
                              FROM      tft 
                              WHERE     cola NOT IN (
                                        SELECT  cola
                                        FROM    tft 
                                        WHERE   colc NOT IN ( 'A1', 'A2', 'A3' ) )
                              GROUP BY  cola ,
                                        colc
                            ) a
                     GROUP BY cola
                     HAVING COUNT(1) > 2
                   ) colas ON colas.cola = orig.cola
GROUP BY orig.cola ,
        colas.counter

I have tested it and it returns the same results as your query using your sample data.

Upvotes: 1

Related Questions