Re: Sequential Scan on TEMP table

donsem wrote: See Below: > 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 And here's the clue^ You've loaded many rows into the temp table I presume, but the engine things that a sequential scan of the table will only require a single IO! You need to run UPDATE STATISTICS on the temp table as recommended in the Performance Guide in your application after loading the data and creating the indexes. I would also suggest modifying the first index to key on lmatter AND lzero for improved efficiency. Art S. Kagel > 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 >

Art S. Kagel wrote: > And here's the clue^ You've loaded many rows into the temp table I presume, > but the engine things that a sequential scan of the table will only require > a single IO! You need to run UPDATE STATISTICS on the temp table as > recommended in the Performance Guide in your application after loading the > data and creating the indexes. I would also suggest modifying the first > index to key on lmatter AND lzero for improved efficiency. > > Art S. Kagel Thanks for the info. But, read a bit further... > > 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. Not that I haven't changed the vendor's code in the past...And getting them to make a change in this particular function that hundreds of clients are using seems pretty remote. Anyway, changing the OPTCOMPIND setting did the trick. don Don Semmens donsem@comcast.net