Re: sql analysis and perf assistance
hmmmm a dschema makes live a lot easier....
i do not know how unique cols etc are however:
> 3) bsi.pyt_hrs_dtl: INDEX PATH
>
> Filters: ((((bsi.pyt_hrs_dtl.hr_pe_id = 111800 AND bsi.pyt_hrs_dtl.pyt_h
i can imagine that bsi.pyt_hrs_dtl.hr_pe_id = 111800 would be unique
or??
also joining the smaller tables and fetch from the big one may be a
problem.
so you could try a
>
select {+ORDERED }
pyt_per_cc, py_batch_name, pyt_date01, pyt_hrs_no01,
> pyt_hrs01, pyt_rt01, pyt_amt01, type, statuscd, system,
> plan, deferflag, py_per_check_dt, py_per_end,
> RetirePB, RetireHB, pyt_num_cd
from pyt_hrs_dtl, cdhtmp19494, py_per_mstr
where hr_pe_id = 111800
> and (py_batch_name like 'SYSTM%' or py_batch_name like 'DRS%')
> and (pyt_status = 'DS' or pyt_status = 'DM' or pyt_status = 'DT')
> and (pyt_date01 <= 08/31/2006)
> and (pyt_date01 >= 02/01/2000)
> and pyt_hrs_no01= cdhtmp19494.CdhNo
> and ((cdhtmp19494.RetirePB > ' '
> and cdhtmp19494.RetirePB is not NULL) or (cdhtmp19494.RetireHB > ' '
> and cdhtmp19494.RetireHB is not NULL)) and (cdhtmp19494.Type <> 'P')
> and ((py_per_check_dt >= cdhtmp19494.dtBeg or
> cdhtmp19494.dtBeg is NULL or cdhtmp19494.dtBeg = ' ')
> and (py_per_check_dt <= cdhtmp19494.dtEnd or
> cdhtmp19494.dtEnd is NULL or cdhtmp19494.dtEnd = ' '))
> and py_per_cc = pyt_per_cc
> rs_no01 = informix.cdhtmp19494.cdhno ) AND (bsi.pyt_hrs_dtl.py_batch_name LIKE '
> SYSTM%' OR bsi.pyt_hrs_dtl.py_batch_name LIKE 'DRS%' ) ) AND bsi.pyt_hrs_dtl.pyt
> _date01 = 12/31/1899 ) AND ((bsi.pyt_hrs_dtl.pyt_status = 'DS' OR bsi.pyt_hrs_dt
> l.pyt_status = 'DM' ) OR bsi.pyt_hrs_dtl.pyt_status = 'DT' ) )
>
> (1) Index Keys: pyt_per_cc (Serial, fragments: ALL)
> Lower Index Filter: bsi.py_per_mstr.py_per_cc = bsi.pyt_hrs_dtl.pyt_per_cc
> NESTED LOOP JOIN
and check if that is faster (Assumimg indexes etc are in place).
how unique / dupl is
> (1) Index Keys: pyt_per_cc (Serial, fragments: ALL)
> Lower Index Filter: bsi.py_per_mstr.py_per_cc = bsi.pyt_hrs_dtl.pyt_per_cc
> NESTED LOOP JOIN
Superboer.
Doug Fossmeyer schreef:
> First, thanks in advance for any help you may provide.
>
> We have a vendor supplied 4gl process that has doubled in time since an upgrade to IDS 9.4fc8, tools7.32, sdk2.81 respectively. I have narrowed the issue down to several sql's in the 4gl. Below is the sqexplain on the first sql problem. While we don't normally amend our vendor's sql in this case we are willing to make the attempt. I have optcompind set to 0, pdq off, buffers at 900000, shm at 360488. I added update stats high for cdhtmp after the inserts are done and have been running the dostats utility. I have included the sqexplain, temp table info, oncheck -pt on the large table.
>
> Tables in join:
> cdhtmp = 234 rows
> py_per_mstr = 236 rows
> pyt_hrs_dtl = 1.4 mil rows (but it is a denormalized table of 322 columns.)
>
> 4gl temp table stmt:
> let sSql = "create temp table ", cdhtmp_name,
> "(",
> " CdhNo smallint,",
> " CdhCd char(8),",
> " StatusCd char(2),",
> " DeferFlag char(1),",
> " Type char(1),",
> " System char(1),",
> " Plan smallint,",
> " dtbeg date,",
> " dtend date,",
> " RetirePB char(1),",
> " RetireHB char(1)",
> ") with no log"
>
>
> sqexplain.out:
> QUERY:
> ------
> select pyt_per_cc, py_batch_name, pyt_date01, pyt_hrs_no01,
> pyt_hrs01, pyt_rt01, pyt_amt01, type, statuscd, system,
> plan, deferflag, py_per_check_dt, py_per_end,
> RetirePB, RetireHB, pyt_num_cd from pyt_hrs_dtl,
> cdhtmp19494, py_per_mstr where hr_pe_id = 111800
> and (py_batch_name like 'SYSTM%' or py_batch_name like 'DRS%')
> and (pyt_status = 'DS' or pyt_status = 'DM' or pyt_status = 'DT')
> and (pyt_date01 <= 08/31/2006)> "sqexplain.out" 54 lines, 2351 characters
>
> QUERY:
> ------
> select count(*), current from hr_empmstr>
> Estimated Cost: 1
> Estimated # of Rows Returned: 1
>
> 1) bsidba.hr_empmstr: INDEX PATH
>
> (1) Index Keys: (count)
>
>
> QUERY:
> ------
> select pyt_per_cc, py_batch_name, pyt_date01, pyt_hrs_no01,
> pyt_hrs01, pyt_rt01, pyt_amt01, type, statuscd, system,
> plan, deferflag, py_per_check_dt, py_per_end,
> RetirePB, RetireHB, pyt_num_cd from pyt_hrs_dtl,
> cdhtmp19494, py_per_mstr where hr_pe_id = 111800
> and (py_batch_name like 'SYSTM%' or py_batch_name like 'DRS%')
> and (pyt_status = 'DS' or pyt_status = 'DM' or pyt_status = 'DT')
> and (pyt_date01 <= 08/31/2006)
> and (pyt_date01 >= 02/01/2000)
> and pyt_hrs_no01= cdhtmp19494.CdhNo
> and ((cdhtmp19494.RetirePB > ' '
> and cdhtmp19494.RetirePB is not NULL) or (cdhtmp19494.RetireHB > ' '
> and cdhtmp19494.RetireHB is not NULL)) and (cdhtmp19494.Type <> 'P')
> and ((py_per_check_dt >= cdhtmp19494.dtBeg or
> cdhtmp19494.dtBeg is NULL or cdhtmp19494.dtBeg = ' ')
> and (py_per_check_dt <= cdhtmp19494.dtEnd or
> cdhtmp19494.dtEnd is NULL or cdhtmp19494.dtEnd = ' '))
> and py_per_cc = pyt_per_cc>
> Estimated Cost: 25162
> Estimated # of Rows Returned: 1
>
> 1) informix.cdhtmp19494: SEQUENTIAL SCAN
>
> Filters: (informix.cdhtmp19494.type != 'P' AND ((informix.cdhtmp19494.re
> tirepb > ' ' AND informix.cdhtmp19494.retirepb IS NOT NULL ) OR (informix.cdhtmp
> 19494.retirehb > ' ' AND informix.cdhtmp19494.retirehb IS NOT NULL ) ) )
>
> 2) bsi.py_per_mstr: SEQUENTIAL SCAN
>
> Filters: (((bsi.py_per_mstr.py_per_check_dt >= informix.cdhtmp19494.dtbe
> g OR informix.cdhtmp19494.dtbeg IS NULL ) OR informix.cdhtmp19494.dtbeg = ) AND
> ((bsi.py_per_mstr.py_per_check_dt <= informix.cdhtmp19494.dtend OR informix.cdh
> tmp19494.dtend IS NULL ) OR informix.cdhtmp19494.dtend = ) )
> NESTED LOOP JOIN
>
> 3) bsi.pyt_hrs_dtl: INDEX PATH
>
> Filters: ((((bsi.pyt_hrs_dtl.hr_pe_id = 111800 AND bsi.pyt_hrs_dtl.pyt_h
> rs_no01 = informix.cdhtmp19494.cdhno ) AND (bsi.pyt_hrs_dtl.py_batch_name LIKE '
> SYSTM%' OR bsi.pyt_hrs_dtl.py_batch_name LIKE 'DRS%' ) ) AND bsi.pyt_hrs_dtl.pyt
> _date01 = 12/31/1899 ) AND ((bsi.pyt_hrs_dtl.pyt_status = 'DS' OR bsi.pyt_hrs_dt
> l.pyt_status = 'DM' ) OR bsi.pyt_hrs_dtl.pyt_status = 'DT' ) )
>
> (1) Index Keys: pyt_per_cc (Serial, fragments: ALL)
> Lower Index Filter: bsi.py_per_mstr.py_per_cc = bsi.pyt_hrs_dtl.pyt_per_cc
> NESTED LOOP JOIN
>
>
> oncheck -pT ifasdev2:pyt_hrs_dtl>
>
>
> TBLspace Report for ifasdev2:bsi.pyt_hrs_dtl
>
> Physical Address 66:721161
> Creation date 08/21/2006 09:42:25
> TBLspace Flags 802 Row Locking
> TBLspace use 4 bit bit-maps
> Maximum row size 1100
> N