sql-server - 使用 CROSS APPLY() 加速 Sql 查询，消除 ROW_NUMBER()

Question

目标：

我想加快大约一百万行交易数据（订单数据）的 sql 查询。我已经能够使用 CROSS APPLY() 将时间从 50 分钟（使用临时表）减少到 9 分钟（请参阅下面的查询）。有没有一种方法可以消除使用 ROW_NUMBER() 来查找客户/年（按客户、年份分组）的最高美元支出？ROW_NUMBER() 在计算上可能很昂贵。此外，这些表上没有索引。

代码：

select z.string_customer_name, z.string_customer_region, z.string_industry_group, 
  z.string_city, z.string_state, z.string_country, z.string_booking_type, 
  z.string_sales_branch, z.string_sales_region, z.string_sales_area,
  z.int_booking_year, z.float_sum_total, z.string_tpis_concat, z.string_groupby
from (
    select #temp_00.*, ca_01.float_sum_total, ca_00.string_tpis_concat, 
      ROW_NUMBER() over (partition by #temp_00.string_groupby order by #temp_00.string_groupby, 
        ca_01.float_sum_total) as row_num
    from #temp_00
    cross apply(
        select string_groupby, int_booking_year, sum(float_total) as float_sum_total
        from #temp_00
       group by string_groupby, int_booking_year
    ) as ca_01
    cross apply(
        select string_groupby, STRING_AGG(cast(string_customer_tpi 
          as varchar(max)), '|') as string_tpis_concat
        from #temp_00
        group by string_groupby
    ) as ca_00
    where ca_00.string_groupby = #temp_00.string_groupby and 
      ca_01.string_groupby = #temp_00.string_groupby and 
      ca_01.int_booking_year = #temp_00.int_booking_year
) as z
where z.row_num = 1

临时表列：

string_customer_name -> 'customer name'
string_customer_tpi -> 'customer id'
string_customer_region -> 'customer region'
string_industry_group -> 'customer industry group'
string_city -> 'customer city'
string_state -> 'customer state'
string_country -> 'customer country'
string_booking_type -> 'order type'
string_sales_branch -> 'sales branch'
string_sales_region -> 'sales region'
string_sales_area -> 'sales area of the world'
int_booking_year -> 'order year'
float_total -> 'order total in dollars'
string_groupby -> 'concatenation of customer name, customer city, customer state, 
  customer country, customer industry group'  

发布查询的执行计划

查询的 XML 太大而无法发布。尽管执行计划的图片很小，但我认为第二篇文章大部分时间都在 Sort() 处。初始数据拉取和发布查询的 60%（发布查询成本为 79%，而数据拉取成本为 21%）在 Sort() 中。

查询执行计划链接

标签： sql-serverperformancetsqlrow-numbercross-apply

Answer 1

我不能确定，但​​如果我理解你在做什么，可以避免交叉应用。这将有助于提高性能，但由于我无法访问数据，您必须对其进行测试并查看。

所以我将在您将数据放入临时表后开始。试试下面的： -

 ;with TempWithSum as (
 --get the sum partition by string_groupby, int_booking_year
  select *,sum(float_total) over(partition by string_groupby, int_booking_year) as float_sum_total
  from @temp_00
 ),NamesCat as(
  --get all customer names grouped by string_groupby
  select string_groupby, STRING_AGG(cast(string_customer_tpi as varchar(max)), '|') as string_tpis_concat
  from @temp_00
  group by string_groupby
 ),AllData as(
 --get the row number partition string_groupby and ordered by string_groupby, float_sum_total
  select string_customer_name, string_customer_region, string_industry_group, string_city, z.string_state,
         string_country, string_booking_type, string_sales_branch, string_sales_region, string_sales_area,
         int_booking_year, float_sum_total, string_tpis_concat, string_groupby
        ,ROW_NUMBER() over (partition by string_groupby order by string_groupby, float_sum_total) as row_num
    from TempWithSum z
    inner join NamesCat on NamesCat.string_groupby=TempWithSum.string_groupby
  )
  select * from AllData where row_num=1

希望它有效并在您期望的时间范围内提供您需要的结果。

注意：我知道您想消除行号，我建议消除交叉应用，但您的目标是性能。