如何改善此慢查询的性能

Lock 发表于 Dev

锁

我有以下查询，大约需要10分钟。我需要这个要快得多。有什么想法可以调优该查询吗？该r_pos_transaction_head表的记录少于500,000条，而r_pos_transaction_detl记录的记录少于900,000条。

我创建了我认为合适的索引（您可以在计划中看到它们在使用中）。

truncate table t_retail_history
insert into t_retail_history
select
  h.source_db as legacy_source_db,
  h.company as legacy_company,
  h.store_code as legacy_store_code,
  h.register as legacy_register,
  cast(h.register as char(1)) + '/' + cast(h.transaction_no as varchar(10)) as legacy_transaction_no,
  t_warehouse.store_number as store_number,
  h.transaction_no as reference,
  convert(varchar(10),dbo.datefromdays(h.date),103) as transaction_date,
  convert(varchar(5),dateadd(second,h.time,cast(cast(getdate() as date) as datetime)), 108) as transaction_time,
  d.product_code as legacy_product_code,
  coalesce(d.colour_no,0) as legacy_colour_no,
  coalesce(g_colour_name_replacement.new_colour_name,s.colour_name,'') as legacy_colour_name,
  coalesce(d.size_no,0) as legacy_size_no,
  coalesce(s.size_code,'') as legacy_size_code,
  d.price_inc_tax as legacy_price_inc_tax,
  d.sku_no as legacy_sku_no,
  null as barcode,
  d.quantity as qty,
  d.nett_total as sales_total,
  null as person_code,
  t_warehouse.destination_busdiv_prefix
from
  svi.r_pos_transaction_head h
inner join
  svi.r_pos_transaction_detl d on
  d.company = h.company
  and d.store_code = h.store_code
  and d.register = h.register
  and d.tx_code = h.transaction_no
inner join
  svi.g_skus s on
  s.company = h.company
  and s.product_code = d.product_code
  and (
    s.colour_position = d.colour_no
    or s.colour_position is null and d.colour_no = 0
  )
  and (
    s.size_position = d.size_no
    or s.size_position is null and d.size_no = 0
  )
left outer join
  g_colour_name_replacement on
  g_colour_name_replacement.product_code = d.product_code
  and g_colour_name_replacement.old_colour_name = s.colour_name
left outer join
  t_warehouse on
  t_warehouse.legacy_svi_code = right('000' + cast(h.store_code as nvarchar(5)),3)
where
  d.quantity <> 0
  and d.nett_total <> 0

解释计划

任何帮助表示赞赏！

Ryx5

就像每个人都建议的那样，查询是正确编写的，请尝试在连接的字段上添加一些索引。

对我而言，查询的糟糕之处在于：

and (
    s.colour_position = d.colour_no
    or s.colour_position is null and d.colour_no = 0
)
and (
    s.size_position = d.size_no
    or s.size_position is null and d.size_no = 0
)

因为OR condition在INNER JOIN声明是性能杀手。他们有很多技巧来避免这种情况（例如，对每个条件都使用2个左连接，然后在where子句中删除出现null的左连接）。

我只是做了其他一些研究，我发现了有关stackoverflow的这篇文章为您提供了一些建议。您应该尝试使用Union选项来不重建所有SELECT字段。

我没有时间为您重写所有查询，请随时告知我。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。