Sequential Scan on TEMP table

I'm having a performance problem on our test server. We are running Informix 9.40.FC5 on HP-UX. Our live server is a rp400 and has no performance problems. Our test server is a rp3410 and is having performance problems with programs using TEMP tables. I've narrowed this down to an index on a TEMP table. The 4400 is using the index while the 3410 is doing a sequential scan. Here's the code: SELECT * FROM ledger WHERE lzero="N" INTO TEMP templedg WITH NOLOG CREATE INDEX i_templ1 ON templedg(lmatter) CREATE UNIQUE INDEX i_templ2 ON templedg(lindex) As I loop through each matter, I execute this query: SELECT * FROM templedg WHERE lmatter=? AND lzero='N' When I run the process with SQL EXPLAIN ON here's what I get: 3410 QUERY: ------ SELECT * FROM templedg WHERE lmatter=? AND lzero='N' Estimated Cost: 1 Estimated # of Rows Returned: 1 1) don.templedg: SEQUENTIAL SCAN Filters: (don.templedg.lzero = 'N' AND don.templedg.lmatter = '0010402-0500 ' ) However, when I run the exact same thing on the 4400, I'm getting this: 4400 QUERY: ------ SELECT * FROM templedg WHERE lmatter=? AND lzero='N' Estimated Cost: 1 Estimated # of Rows Returned: 1 1) don.templedg: INDEX PATH Filters: don.templedg.lzero = 'N' (1) Index Keys: lmatter (Serial, fragments: ALL) Lower Index Filter: don.templedg.lmatter = '0001049-0500 ' Any ideas as to why one server isn't using the index, while the other one is? They essentially have the same setup, although the 4400 is multi-processor, while the 3410 is single processor. BTW, I can't or won't change the code since we've been running this process every day for the last 12 years on various Unix servers without a problem. thanks, don Don Semmens donsem@comcast.net

Have you run update statistics on the 3410 for table templdg ? ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/ ----- Original Message ---- From: donsem <donsem@comcast.net> To: informix-list@iiug.org Sent: Friday, August 4, 2006 3:31:05 PM Subject: Sequential Scan on TEMP table I'm having a performance problem on our test server. We are running Informix 9.40.FC5 on HP-UX. Our live server is a rp400 and has no performance problems. Our test server is a rp3410 and is having performance problems with programs using TEMP tables. I've narrowed this down to an index on a TEMP table. The 4400 is using the index while the 3410 is doing a sequential scan. Here's the code: SELECT * FROM ledger WHERE lzero="N" INTO TEMP templedg WITH NOLOG CREATE INDEX i_templ1 ON templedg(lmatter) CREATE UNIQUE INDEX i_templ2 ON templedg(lindex) As I loop through each matter, I execute this query: SELECT * FROM templedg WHERE lmatter=? AND lzero='N' When I run the process with SQL EXPLAIN ON here's what I get: 3410 QUERY: ------ SELECT * FROM templedg WHERE lmatter=? AND lzero='N' Estimated Cost: 1 Estimated # of Rows Returned: 1 1) don.templedg: SEQUENTIAL SCAN Filters: (don.templedg.lzero = 'N' AND don.templedg.lmatter = '0010402-0500 ' ) However, when I run the exact same thing on the 4400, I'm getting this: 4400 QUERY: ------ SELECT * FROM templedg WHERE lmatter=? AND lzero='N' Estimated Cost: 1 Estimated # of Rows Returned: 1 1) don.templedg: INDEX PATH Filters: don.templedg.lzero = 'N' (1) Index Keys: lmatter (Serial, fragments: ALL) Lower Index Filter: don.templedg.lmatter = '0001049-0500 ' Any ideas as to why one server isn't using the index, while the other one is? They essentially have the same setup, although the 4400 is multi-processor, while the 3410 is single processor. BTW, I can't or won't change the code since we've been running this process every day for the last 12 years on various Unix servers without a problem. thanks, don Don Semmens donsem@comcast.net _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list