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 NO
LOG

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