How can I improve its query speed?

Question

Post reply

How can I improve its query speed?

AhTu_SQL2k+

Ten Centuries

Points: 1012

September 8, 2020 at 9:05 am

#3786897

Hi, I am facing an extremely slow result from below script. Can anyone advise how can I improve its query speed?

Thanks in advance,

Wilson

 SELECT 
     d.part_code, 
     x1.part_desc, 
     x1.specification, 
     d.odr_ref, d.dbcode, crcode, 
     d.ref_no, d.date, d.source, d.unit_cost, d.lot_no, d.wh_code, d.part_loc, d.shelf_loc, 0 as qty_open, 
     CASE 
      WHEN (d.source LIKE 'R%') THEN  d.qty
  ELSE 0  
  END AS qty_in,
     CASE 
      WHEN (d.source LIKE 'I%') THEN  d.qty 
  ELSE 0  
  END AS qty_out, 
 x1.part_uom,
     d.sel_price,      
     CASE 
      WHEN (d.source='RU' and isnull(d.dbcode,'')<>'' ) THEN d.po_no 
      WHEN (d.source='RM') THEN x4.remarks  
      WHEN (d.source='RW') THEN x5.prod_no 
      WHEN (d.source='IM') THEN x4.remarks  
      WHEN (d.source='IW' or d.source='IP') THEN x7.job_no 
      ELSE x8.supp_dono
     END AS CustSupp_DO, 
     CASE WHEN d.source LIKE 'R%' THEN x9.po_no ELSE d.po_no END as CustSupp_PO,
     d.trn_date,
     CASE WHEN (d.source='B' and x1.l_r_price>0) THEN x1.l_r_price ELSE x1.dec_cost END as Price,
     x11.carton_no,  
     d.doe,
     x1.max_qty AS ro_level, 
     x1.qty_odr,
 isnull((select max(dt_due) from s_pocont where part_code=d.part_code and status<>'CANCELLED'),'') ,0, 0
FROM s_stkdetl d
    OUTER APPLY (SELECT part_desc, specification, part_uom, l_r_price, dec_cost, max_qty, qty_odr  FROM s_stkmst WHERE Part_code = d.part_code) x1
    OUTER APPLY (select remarks from s_stktrn where odr_ref = d.odr_ref) x4
    OUTER APPLY (select top 1 prod_no from s_productoutput where job_no = d.ref_no) x5
    OUTER APPLY (select job_no from s_mrshdr where mrs_no = d.ref_no) x7
    OUTER APPLY (select supp_dono from s_porec where gidno = d.gid_no) x8
    OUTER APPLY (Select top 1 po_no from s_porecdtl where gidno = d.ref_no and part_code = d.part_code) x9
    OUTER APPLY (select top 1 carton_no from s_porecdtl where gidno = d.gid_no and part_code = d.part_code) x11 
    WHERE d.date >= @mDateFrom and d.date <= @mDateTo 
    and d.Part_code>=@PartFrom AND d.Part_code<=@PartTo
    and d.wh_code>=@FilterWhFrom and d.wh_code<=@FilterWhTo
    and d.part_loc>=@LocFrom and d.part_loc<=@locTo
    and d.shelf_loc >=@ShelfFrom and d.shelf_loc<=@shelfTo
    and d.lot_no >=@LotFrom and d.lot_no<=@LotTo
    Order by part_code, trn_date

This topic was modified 3 years, 8 months ago by AhTu_SQL2k+.

Together, we can make wonders!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Getting the query plan will really help us help you here. Can you post the query plan for us please? You can either attach it to your post, or if you prefer, you can post it via Paste the Plan. This'll tell us what the RDBMS is doing in the background. We might then need details of indexes, or suggest them.

On a related note to your query, you have a load of TOP 1s in your subqueries, however, not a single ORDER BY in them. This means that SQL Server is free to return any arbitrary row and your result could easily be inconsistent. If you are using a TOP you need to include an ORDER BY for consistent result sets.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sergiy SSC Guru Points: 110207 More actions · Answer 2

If you leave just the core table :

SELECT * FROM s_stkdetl d
WHERE d.date >= @mDateFrom and d.date <= @mDateTo 
and d.Part_code>=@PartFrom AND d.Part_code<=@PartTo
and d.wh_code>=@FilterWhFrom and d.wh_code<=@FilterWhTo
and d.part_loc>=@LocFrom and d.part_loc<=@locTo
and d.shelf_loc >=@ShelfFrom and d.shelf_loc<=@shelfTo
and d.lot_no >=@LotFrom and d.lot_no<=@LotTo
Order by part_code, trn_date

Does it help the performance?

and why all those "apply"s? Is JOIN outlawed or something?

_____________
Code for TallyGenerator

Thom A SSC Guru Points: 99126 More actions · Answer 3

Sergiy wrote:

and why all those "apply"s? Is JOIN outlawed or something?

All their queries in their APPLY's have a TOP 1 (without an ORDER BY) so that would explain the why. It appears that the only want 1 row from a one to many join, however, don't care what row that is, or even if those results are consistent for each run; so a flawed solution.

If they had a ORDER BY then would be "fine".

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sergiy SSC Guru Points: 110207 More actions · Answer 4

Actually, only 3 out of 7 have TOP 1.

looks like lousy attempt to eliminate "subquery returned more than 1 row" error when occurred.

well, the whole thing looks lousy.

_____________
Code for TallyGenerator

Thom A SSC Guru Points: 99126 More actions · Answer 5

Sergiy wrote:

Actually, only 3 out of 7 have TOP 1.
looks like lousy attempt to eliminate "subquery returned more than 1 row" error when occurred.
well, the whole thing looks lousy.

You're right, my bad. Some good aliasing in there wouldn't go amiss either. Having every aliases x1 to x7 makes it impossible to easily tell what comes from what.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ken McKelvey SSCoach Points: 18775 More actions · Answer 6

Also:

1. what is the point of x7? It is not referenced.

2. if the code is part of a stored procedure all those variables in the WHERE clause could produce parameter sniffing problems.

AhTu_SQL2k+ Ten Centuries Points: 1012 More actions · Answer 7

Dear Thom A and Sergiy,

Many thanks for your advice. After added "Order by" to those "Top 1" subqueries, its speed has improved dramatically. The original query speed was 2 mins and now has just 3s.

I admit and apologize for lousy scripted. This was created a few years back and since it produces correct result, I have never gone back to retouch it.

Thank you,

Wilson

Together, we can make wonders!

Thom A SSC Guru Points: 99126 More actions · Answer 8

AhTu_SQL2k+ wrote:

Many thanks for your advice. After added "Order by" to those "Top 1" subqueries, its speed has improved dramatically. The original query speed was 2 mins and now has just 3s.

If this works, I smell parameter sniffing, meaning that you are likely going to end up in this position again later.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sergiy SSC Guru Points: 110207 More actions · Answer 9

You have range selection conditions against 7 different columns in the WHERE clause.

which of this columns is used as a 1st column for the clustered index on stkdetl?

is it the best candidate in terms of continuity and cardinality?

And I agree with Thom: it's very likely a paremeter sniffing case.

changing the query forced its recompilation, and optimiser picked up the plan which is better for that particular set of parameters you used at the time.

next time, when selection criteria would be different you'll get the same problem back.

_____________
Code for TallyGenerator