Join IIUG
 for   
 

Informix News
18 Nov 13 - ZDNet - Top 20 mobile skills in demand... Read
09 Sep 13 - telecompaper - Shaspa and Tatung have shown a new smart home platform at Ifa in Berlin. Powered by the IBM Informix software... Read
06 Sep 13 - IBM data magazine - Mission Accomplished - Miami, Florida will be the backdrop for the 2014 IIUG Informix Conference... Read
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read


End of Support Dates

IIUG on Facebook IIUG on Twitter

[ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

SQL performance assistance

Posted By: Doug Fossmeyer
Date: Monday, 18 September 2006, at 1:38 p.m.

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 115 838

3 4742 299 490

----- -------- -------- ----------

Total 4784 297 493

Index pyt_hrs_dtl_b fragment in DBspace ifasdev2dbs

Physical Address 66:721163

Creation date 08/21/2006 10:33:39

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 12

Current serial value 1

First extent size 14

Next extent size 14

Number of pages allocated 5852

Number of pages used 5846

Number of data pages 0

Number of rows 0

Partition partnum 15729027

Partition lockid 15729025

Extents

Logical Page Physical Page Size

0 67:1023730 14

14 68:545591 5698

5712 74:580606 14

5726 74:581020 14

5740 74:586138 14

5754 74:586280 14

5768 74:590966 14

5782 74:591252 14

5796 74:592734 14

5810 74:598064 14

5824 74:599630 14

5838 74:601324 14

TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl

Type Pages Empty Semi-Full Full Very-Full

---------------- ---------- ---------- ---------- ---------- ----------

Free 6

Bit-Map 2

Index 5844

Data (Home) 0

----------

Total Pages 5852

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_b on ifasdev2:bsi.pyt_hrs_dtl

Average Average

Level Total No. Keys Free Bytes

----- -------- -------- ----------

1 1 61 848

2 61 94 654

3 5782 245 763

----- -------- -------- ----------

Total 5844 243 762

Index pyt_hrs_dtl_c fragment in DBspace ifasdev2dbs

Physical Address 66:721164

Creation date 08/21/2006 10:34:03

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 8

Current serial value 1

First extent size 17

Next extent size 128

Number of pages allocated 12433

Number of pages used 12349

Number of data pages 0

Number of rows 0

Partition partnum 15729028

Partition lockid 15729025

Extents

Logical Page Physical Page Size

0 68:551289 11409

11409 74:580764 256

11665 74:581034 128

11793 74:585706 128

11921 74:585866 128

12049 74:586152 128

12177 74:590838 128

12305 74:599406 128

TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl

Type Pages Empty Semi-Full Full Very-Full

---------------- ---------- ---------- ---------- ---------- ----------

Free 84

Bit-Map 4

Index 12345

Data (Home) 0

----------

Total Pages 12433

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_c on ifasdev2:bsi.pyt_hrs_dtl

Average Average

Level Total No. Keys Free Bytes

----- -------- -------- ----------

1 1 3 1964

2 3 66 526

3 198 61 787

4 12143 116 1042

----- -------- -------- ----------

Total 12345 115 1038

Index pyt_hrs_dtl_d fragment in DBspace ifasdev2dbs

Physical Address 66:721165

Creation date 08/21/2006 10:34:18

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 5

Current serial value 1

First extent size 19

Next extent size 144

Number of pages allocated 23059

Number of pages used 22960

Number of data pages 0

Number of rows 0

Partition partnum 15729029

Partition lockid 15729025

Extents

Logical Page Physical Page Size

0 68:562698 22483

22483 74:580620 144

22627 74:585994 144

22771 74:591266 144

22915 74:599968 144

TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl

Type Pages Empty Semi-Full Full Very-Full

---------------- ---------- ---------- ---------- ---------- ----------

Free 99

Bit-Map 6

Index 22954

Data (Home) 0

----------

Total Pages 23059

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_d on ifasdev2:bsi.pyt_hrs_dtl

Average Average

Level Total No. Keys Free Bytes

----- -------- -------- ----------

1 1 9 1804

2 9 41 938

3 375 60 459

4 22569 62 353

----- -------- -------- ----------

Total 22954 62 355

Index pyt_hrs_dtl_e fragment in DBspace ifasdev2dbs

Physical Address 66:721166

Creation date 08/21/2006 10:35:06

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 1

Current serial value 1

First extent size 42

Next extent size 1344

Number of pages allocated 63210

Number of pages used 62204

Number of data pages 0

Number of rows 0

Partition partnum 15729030

Partition lockid 15729025

Extents

Logical Page Physical Page Size

0 68:585181 63210

TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl

Type Pages Empty Semi-Full Full Very-Full

---------------- ---------- ---------- ---------- ---------- ----------

Free 1006

Bit-Map 16

Index 62188

Data (Home) 0

----------

Total Pages 63210

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_e on ifasdev2:bsi.pyt_hrs_dtl

Average Average

Level Total No. Keys Free Bytes

----- -------- -------- ----------

1 1 2 1968

2 2 22 1048

3 45 35 474

4 1581 38 334

5 60559 23 965

----- -------- -------- ----------

Total 62188 23 949

Index pyt_hrs_dtl_f fragment in DBspace ifasdev2dbs

Physical Address 66:721167

Creation date 08/21/2006 10:37:24

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 5

Next extent size 32

Number of pages allocated 4101

Number of pages used 4085

Number of data pages 0

Number of rows 0

Partition partnum 15729031

Partition lockid 15729025

Extents

Logical Page Physical Page Size

0 66:1023738 5

5 68:648391 4032

4037 74:585834 32

4069 74:592960 32

TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl

Type Pages Empty Semi-Full Full Very-Full

---------------- ---------- ---------- ---------- ---------- ----------

Free 16

Bit-Map 2

Index 4083

Data (Home) 0

----------

Total Pages 4101

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_f on ifasdev2:bsi.pyt_hrs_dtl

Average Average

Level Total No. Keys Free Bytes

----- -------- -------- ----------

1 1 21 1850

2 21 193 467

3 4061 349 267

----- -------- -------- ----------

Total 4083 348 268

Index pyt_hrs_dtl_g fragment in DBspace ifasdev2dbs

Physical Address 66:721168

Creation date 08/21/2006 10:37:27

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 2

Current serial value 1

First extent size 7

Next extent size 32

Number of pages allocated 4103

Number of pages used 4100

Number of data pages 0

Number of rows 0

Partition partnum 15729032

Partition lockid 15729025

Extents

Logical Page Physical Page Size

0 68:652423 4071

4071 74:591008 32

TBLspace Usage Report for ifasdev2:bsi.pyt_hrs_dtl

Type Pages Empty Semi-Full Full Very-Full

---------------- ---------- ---------- ---------- ---------- ----------

Free 3

Bit-Map 2

Index 4098

Data (Home) 0

----------

Total Pages 4103

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_g on ifasdev2:bsi.pyt_hrs_dtl

Average Average

Level Total No. Keys Free Bytes

----- -------- -------- ----------

1 1 31 1708

2 31 131 958

3 4066 348 267

----- -------- -------- ----------

Total 4098 347 272

4071 74:591008 32

[ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.