How can I improve its query speed?

  • 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 2 months, 3 weeks ago by  AhTu_SQL2k+.
    Together, we can make wonders!
  • 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.

  • 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?

     

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

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

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

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

  • 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!
  • 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.

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

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

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