Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2015
    Posts
    1

    Unanswered: Query performance varies when like condition varies b/w like '4%' & '403%'

    I have table shipment_line, which has 50 columns and 80K rows. wrt column ordnum has around 60k rows with ordnum like '4%' and 40k rows with ordnum like '403%'. The ordnum is indexed(we also tried removing index). when this table is used with some more table(which also have > 50k rows) as left join, the ''403%' able to return the the rows faster in around 10sec, but query with like '4%' takes almost 10 mins. what might be the reason?
    below is the full query:

    with loaded as (select s.ship_id, s.wh_id,
    sum (cast (untqty as float)) loadedqty
    from invdtl,
    invsub,
    invlod,
    shipment_line l,
    ship_struct_view s
    where invdtl.subnum = invsub.subnum
    and invsub.lodnum = invlod.lodnum
    and invlod.stoloc = s.trlr_id
    and invdtl.ship_line_id = l.ship_line_id
    and l.ship_id = s.ship_id
    and l.wh_id = s.wh_id
    group by s.ship_id,s.wh_id),
    staged as (select sum (cast (invdtl.untqty as float)) stgqty,
    pckwrk.ship_id,
    pckwrk.wh_id
    from pckwrk join pckmov on (pckwrk.cmbcod = pckmov.cmbcod and pckwrk.wh_id = pckmov.wh_id) join aremst pck_aremst on (pck_aremst.wh_id = pckmov.wh_id and pck_aremst.arecod = pckmov.arecod) join invdtl on invdtl.wrkref = pckwrk.wrkref join invsub on invsub.subnum = invdtl.subnum join invlod on invlod.lodnum = invsub.lodnum join locmst inv_locmst on (invlod.stoloc = inv_locmst.stoloc and invlod.wh_id = inv_locmst.wh_id) join aremst inv_aremst on (inv_aremst.arecod = inv_locmst.arecod and inv_aremst.wh_id = inv_locmst.wh_id)
    where pckwrk.pcksts in ('P',
    'H',
    'R')
    and pckwrk.wrktyp = 'P'
    and pckwrk.prtnum != 'KITPART'
    and not exists (select 'x'
    from pckmov pm
    where pm.cmbcod = pckmov.cmbcod
    and pm.seqnum > pckmov.seqnum)
    and pck_aremst.stgflg = 1
    and ((pckmov.stoloc = invlod.stoloc and pckmov.wh_id = invlod.wh_id) or (inv_aremst.rdtflg = 1 and pckmov.arrqty > 0 and pckmov.prcqty > 0 and not exists (select 1
    from nxtloc_view
    where pndloc = pckmov.stoloc
    and nxtare = pckmov.arecod
    and lodnum = invlod.lodnum
    and wh_id = pckmov.wh_id)))
    group by pckwrk.ship_id,pckwrk.wh_id ),
    shipment_line_h as (select * from shipment_line where ordnum like '4%')


    select distinct ' ' pckexc,
    (select sum (cast (pckqty as float))
    from pckwrk
    where pckwrk.ship_id = h.ship_id
    and pckwrk.wh_id = o.wh_id
    and pckwrk.pcksts = 'P') alcqty,
    (select sum (cast ((pckqty - appqty) as float))
    from pckwrk
    where pckwrk.ship_id = h.ship_id
    and pckwrk.wh_id = o.wh_id
    and pckwrk.pcksts in ('R',
    'L')) relqty,
    (select sum (cast (appqty as float))
    from pckwrk
    where pckwrk.ship_id = h.ship_id
    and pckwrk.wh_id = o.wh_id) pickedqty,
    (select sum (cast (pckqty as float))
    from rplwrk
    where rplwrk.ship_id = h.ship_id
    and rplwrk.wh_id = o.wh_id) shrt_qty,
    loaded.loadedqty loadedqty,
    staged.stgqty stgqty,
    o.wh_id,
    h.ship_id,
    o.client_id,
    o.ordtyp,
    h.host_client_id,
    h.host_ext_id,
    h.shpsts,
    h.rt_adr_id,
    h.stop_id,
    h.car_move_id,
    h.cargrp,
    h.carcod,
    h.srvlvl,
    sum (cast (d.pckqty as float)) unalloc_qty,
    h.sddflg,
    h.doc_num,
    h.track_num,
    h.frtchg,
    h.frtcod,
    h.frtrte,
    h.adddte,
    h.alcdte,
    h.stgdte,
    h.loddte,
    h.entdte,
    h.early_shpdte,
    h.late_shpdte,
    h.early_dlvdte,
    h.late_dlvdte,
    h.rrlflg,
    appt.start_dte appt_start_dte,
    appt.end_dte appt_end_dte,
    sum (ol.ordqty) ordqty,
    cast ((case when sum (d.pckqty) > 0 and h.stgdte is null then 0
    else sum (ol.host_ordqty) - (sum (d.inpqty) + sum (d.stgqty) +
    sum (d.shpqty)) end) as float) cancelledqty
    from cstmst c,
    prtftp,
    prtftp_dtl,
    prtmst_view,
    ord_line ol,
    ord o,
    shipment_line_h d,
    ship_struct_view h left outer join appt on h.appt_id = appt.appt_id
    and h.wh_id = appt.wh_id left outer join loaded on loaded.ship_id = h.ship_id
    and loaded.wh_id = h.wh_id left outer join staged on staged.ship_id = h.ship_id
    and staged.wh_id = h.wh_id
    where
    prtftp_dtl.prtnum = prtftp.prtnum
    and prtftp_dtl.wh_id = prtftp.wh_id
    and prtftp_dtl.prt_client_id = prtftp.prt_client_id
    and prtftp_dtl.ftpcod = prtftp.ftpcod
    and prtftp_dtl.uomcod = prtmst_view.stkuom
    and prtftp.prtnum = prtmst_view.prtnum
    and prtftp.prt_client_id = prtmst_view.prt_client_id
    and prtftp.wh_id = prtmst_view.wh_id
    and prtftp.defftp_flg = 1
    and prtmst_view.prtnum = ol.prtnum
    and prtmst_view.prt_client_id = ol.prt_client_id
    and prtmst_view.wh_id = ol.wh_id
    and d.ship_id = h.ship_id
    and d.client_id = o.client_id
    and d.ordnum = o.ordnum
    and d.wh_id = o.wh_id
    and o.wave_flg = '0'
    and d.client_id = ol.client_id
    and d.ordnum = ol.ordnum
    and d.ordlin = ol.ordlin
    and d.ordsln = ol.ordsln
    and o.wh_id = ol.wh_id
    and ol.non_alc_flg = 0
    and o.client_id = c.client_id
    and o.stcust = c.cstnum
    and h.super_ship_flg = '0'
    and h.super_ship_id is null
    and not exists (select 'x'
    from trlr t
    where h.trlr_id = t.trlr_id
    and trlr_stat = 'D')


    group by o.wh_id,
    h.ship_id,
    o.client_id,
    o.ordtyp,
    h.host_client_id,
    h.host_ext_id,
    h.shpsts,
    h.rt_adr_id,
    h.stop_id,
    h.car_move_id,
    h.cargrp,
    h.carcod,
    h.srvlvl,
    h.sddflg,
    h.doc_num,
    h.track_num,
    h.frtchg,
    h.frtcod,
    h.frtrte,
    h.adddte,
    h.alcdte,
    h.stgdte,
    h.loddte,
    h.entdte,
    h.early_shpdte,
    h.late_shpdte,
    h.early_dlvdte,
    h.late_dlvdte,
    h.rrlflg,
    appt.start_dte,
    appt.end_dte,
    loaded.loadedqty,
    staged.stgqty
    order by o.wh_id,
    h.ship_id;
    Last edited by rameshbabukm; 02-11-15 at 02:02.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what datatype is column ORDNUM?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you really would have to compare the access paths to see why the difference. Also, Your SQL doesn't quite match up with your explanation.
    have table ord, which has 50 columns and 80K rows. wrt column ordnum has around 60k rows with ordnum like '4%' and 40k rows with ordnum like '403%'.
    shipment_line_h as (

    select * from shipment_line
    where ordnum like '4%')
    Also, seems as you have quite a bit of redundancy in this statement with tables being accessed multiple times might want to take a look at that while you are at it. I always try to " Keep It Simple, Stupid" believe me it helps.
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •