SQL performance assistance
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. 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
Number of special columns 0
Number of keys 0
Number of extents 6
Current serial value 1
First extent size 1136
Next extent size 4544
Number of pages allocated 1423408
Number of pages used 1419244
Number of data pages 1418889
Number of rows 1418889
Partition partnum 15729025
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 67:159340 859952
859952 68:3 545280
1405232 74:581162 4544
1409776 74:586294 4544
1414320 74:593280 4544
1418864 74:601338 4544
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 4167
Bit-Map 352
Index 0
Data (Home) 1418889
----------
Total Pages 1423408
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 1299702324
Home Data Page Version Summary
Version Count
0 (current) 1418889
Index pyt_hrs_dtl_a fragment in DBspace ifasdev2dbs
Physical Address 66:721162
Creation date 08/21/2006 10:33:30
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 1100
Number of special columns 0
Number of keys 1
Number of extents 4
Current serial value 1
First extent size 14
Next extent size 28
Number of pages allocated 4802
Number of pages used 4786
Number of data pages 0
Number of rows 0
Partition partnum 15729026
Partition lockid 15729025
Extents
Logical Page Physical Page Size
0 67:1019292 4438
4438 68:545283 308
4746 74:590980 28
4774 74:597940 28
TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 16
Bit-Map 2
Index 4784
Data (Home) 0
----------
Total Pages 4802
Unused Space Summary
Unused data slots 0
Unused bytes per data page 916
Total unused bytes in data pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index pyt_hrs_dtl_a on ifasdev2:bsi.pyt_hrs_dtl
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 41 1356
2 41 11