如何提高此慢查询的性能

I have the following query which takes about 10 minutes. I need this to be much quicker. Any ideas what can be done to tune this query? The r_pos_transaction_head table has a little under 500,000 records, and the r_pos_transaction_detl has a little under 900,000 records.

我有以下查询,大约需要10分钟。我需要这个更快。有什么想法可以做些什么来调整这个查询? r_pos_transaction_head表的记录略低于500,000,而r_pos_transaction_detl的记录略低于900,000。

I have created indexes where I thought appropriate (you can see these in use in the plan).

我已经创建了我认为合适的索引(您可以在计划中看到这些索引)。

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

Any help appreciated!

任何帮助赞赏!

3 个解决方案

#1

The query is correctly written, as everyone suggest, try to add some indexes on joined fields.

正如所有人建议的那样,正确编写查询,尝试在连接字段上添加一些索引。

For me the bad part of the query is :

对我来说,查询的不好部分是:

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
)

Because OR condition in INNER JOIN statement is performance killer. They are many trick to avoid it (like do 2 left join instead for each condition and then in where clause remove left join that occurs null).

因为INNER JOIN语句中的OR条件是性能杀手。它们是避免它的许多技巧(比如对每个条件执行2次左连接,然后在where子句中删除出现null的左连接)。

I just do some other research and I found this post on * that propose you something. You should try the Union option to not rebuild all your SELECT fields part.

我只是做了一些其他的研究,我在*上找到了这篇文章,它提出了一些建议。您应该尝试使用Union选项不重建所有SELECT字段部分。

I don't have time to rewrite all the query for you, keep me informed.

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

#2

You can use indexed views in order to perform beter joining. Perform better indexing so instead of index scan it can use index seek. Image percentage sum is not 100% where is others?

您可以使用索引视图来执行beter连接。执行更好的索引,因此它可以使用索引搜索而不是索引扫描。图像百分比总和不是100%在哪里是其他人?

#3

It seems you don't have the required indexes to support your query. You should see if you can create indexes on the columns where you join.

您似乎没有必要的索引来支持您的查询。您应该看看是否可以在您加入的列上创建索引。