How to reduce SQL query execution time

Question

declare @SQL      nvarchar(max)
       ,@Columns1 nvarchar(max)
       ,@Columns2 nvarchar(max);

set @Columns1 = N'';

set @Columns2 = N'';

select @Columns1 += iif(@Columns1 = '',quotename(Columns1),N','+quotename(Columns1))
from (select Month+' Count  of TonerQty' as Columns1
      from MPSSell
      where Month is not null
            and Month != ''
            and Country in(select *
                            from [dbo].[UF_CSVToArray]('Hong Kong,South Korea,New Zealand,Philippines,Australia,India')
                           )
      group by Month
     ) as colPvt1;

select @Columns2 += iif(@Columns2 = '',quotename(Columns2),N','+quotename(Columns2))
from
     (select Month+' Count  of PL' as Columns2
      from
           MPSSell
      where Month is not null
            and Month != ''
            and Country in(select *
                            from [dbo].[UF_CSVToArray]('Hong Kong,South Korea,New Zealand,Philippines,Australia,India')
                           )
      group by Month
     ) as colPvt2;

set @SQL = N'
select result1.Country
        ,['+stuff(@Columns1,1,1,'')
        +', ['+stuff(@Columns2,1,1,'')
        +' from(SELECT *
                FROM (Select Country
                            ,Month + '' Count  of TonerQty'' as Columns1
                            ,TonerQty as opValue1
                        from MPSSell 
                        where Country is not null
                            and Country != ''''
                            and Month is not null
                            and Month != ''''
                            and Country in(Select *
                                            from [dbo].[UF_CSVToArray](''Hong Kong,South Korea,New Zealand,Philippines,Australia,India'')
                                            )
                        group by Country
                                ,Month
                                ,TonerQty
                        ) as resPvt1
PIVOT(Count(opValue1) FOR Columns1 IN( ['+stuff(@Columns1,1,1,'')+')) as p
) as result1

join (SELECT *
        FROM (Select Country
                    ,Month + '' Count  of PL'' as Columns2
                    ,PL as opValue2
                from MPSSell 
                where Country is not null
                    and Country != ''''
                    and Month is not null
                    and Month != ''''
                    and Country in(Select *
                                    from [dbo].[UF_CSVToArray](''Hong Kong,South Korea,New Zealand,Philippines,Australia,India'')
                                    )
                group by Country
                        ,Month
                        ,PL
                ) as resPvt2
        PIVOT(Count(opValue2) FOR Columns2 IN  ( ['+stuff(@Columns2,1,1,'')+')) as p
        ) as result2
    on result1.Country=result2.Country';

exec sp_executesql
     @SQL;

This is my SQL pivot query which is working fine, but it is taking too much time to execute. Please help me how to can I reduce execution time.

Estimated Execution plan in Google Drive

Estimated plan on pastetheplan.com

Actual plan on pastetheplan.com

Please provide an execution plan. Put it onto www.pastetheplan.com and add the link to your question. — Rich Benner, Nov 25 '16 at 09:52
Please put it on pastetheplan.com it will make it easier for people to help you. Also, is that an extimated execution plan? If so, an actual plan would be good. — Rich Benner, Nov 25 '16 at 10:07

Ivan Starostin · Answer 1 · 2016-11-25T11:38:17.037

1

Be aware of WHERE ... IN (SELECT...), don't do same things many times, help server to understand what are you attempting to achieve:

declare @countries table (country <actual type>)

insert into @countries (country)
Select <actual col>
from [dbo].[UF_CSVToArray](''Hong Kong,South Korea,New Zealand,Philippines,Australia,India'')

after that everywhere use:

...
where exists(select 1 from @countries cc where cc.country = mps.country)
...

2

What's the difference between sources for both queries? None.

select @Columns1 += iif(@Columns1 = '',quotename(Columns1),N','+quotename(Columns1))
from (select Month+' Count  of TonerQty' as Columns1
      from MPSSell
      where Month is not null
            and Month != ''
            and Country in(select *
                            from [dbo].[UF_CSVToArray]('Hong Kong,South Korea,New Zealand,Philippines,Australia,India')
                           )
      group by Month
     ) as colPvt1;

select @Columns2 += iif(@Columns2 = '',quotename(Columns2),N','+quotename(Columns2))
from
     (select Month+' Count  of PL' as Columns2
      from
           MPSSell
      where Month is not null
            and Month != ''
            and Country in(select *
                            from [dbo].[UF_CSVToArray]('Hong Kong,South Korea,New Zealand,Philippines,Australia,India')
                           )
      group by Month
     ) as colPvt2;

convert to

select @Columns1 += iif(@Columns1 = '',quotename(Month+' Count  of TonerQty'),N','+quotename(Month+' Count  of TonerQty')),
      @Columns2 += iif(@Columns2 = '',quotename(Month+' Count  of PL'),N','+quotename(Month+' Count  of PL'))
from
     (select DISTINCT Month
      from MPSSell mps
      where Month is not null
            and Month != ''
            and exists(select 1 from @countries cc where cc.country = mps.country)
     ) as colPvt2;

now you scan the source table for month list only once.

3

Obtain month list from somewhere else (cache, arguments, shorter table). And scan source table only once - in the very end where you are retrieving data.

score 0 · Answer 2 · edited May 23 '17 at 10:30

0

Certain pivot queries using the PIVOT clause can benefit from the HASH GROUP query hint. Try with specifying OPTION(HASH GROUP) at the end of your dynamic queries. See Query Hints for more details.

Another possible performance improvement is to not use the PIVOT clause to pivot but rather do the pivotting the old-school way. You can find several examples of that if you search on the internet for sql server old-school pivot, but here's one example on Stack Overflow of how it's done.

edited May 23 '17 at 10:30

Community

1
1

answered Nov 25 '16 at 10:46

TT.

15,774
6
47
88

'sql server old-school pivot ' we can use when we know about data.we dont know anything about data then ? – Hitesh Thakor Nov 25 '16 at 10:49
@HiteshThakor You can always use the old-school version, you can transform a pivot query using `PIVOT` to the old-school version with `GROUP BY` and `COUNT`. Just see how it's done the old-school version and set it up in dynamic SQL. – TT. Nov 25 '16 at 11:37

How to reduce SQL query execution time

2 Answers2

1

2

3