如何减少SQL查询的执行时间

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;

这是我的SQL pivot查询，它工作正常，但执行时间太长。请告诉我怎样才能减少执行时间。

Estimated Execution plan in Google Drive

Estimated plan on pastetheplan.com

Actual plan on pastetheplan.com

Answer 1

1

注意WHERE ... IN (SELECT...)，不要多次做同样的事情，帮助服务器理解你试图实现的目标：

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'')

在此之后，到处使用：

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

2

这两个查询的源之间有什么区别？没有。

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;

转换为

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;

现在，您只需扫描源表中的月份列表一次。

3.

从其他地方获取月份列表(缓存、参数、短表)。并且只扫描一次源表-在检索数据的最后。

Answer 2

使用PIVOT子句的某些透视查询可以从HASH GROUP查询提示中受益。尝试在动态查询的末尾指定OPTION(HASH GROUP)。有关更多详细信息，请参阅Query Hints。

另一个可能的性能改进是不使用PIVOT子句进行透视，而是使用老式的方法进行透视。如果你在互联网上搜索sql server old-school pivot，你可以找到几个这样的例子，但是这里的one example on Stack Overflow介绍了它是如何做到的。