CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Lock
Lock

Reputation: 5522

How can I improve the performance of this slow query

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.

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

Explain Plan

Any help appreciated!

Upvotes: 2

Views: 142

Answers (3)

Ryx5
Ryx5

Reputation: 1366

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

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

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

Upvotes: 3

mordack550
mordack550

Reputation: 501

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.

Upvotes: 0

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

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?

Upvotes: 0

Related Questions