SEO

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

A reader e-mailed me with a suggestion that worked. Our OPTCOMPIND was
set to 2 on our test system. I changed it to 0 and the performance
problem cleared up.

Weird thing was this setting was 2 on our old test system running
Informix 7.3 and we didn't have a performance problem on that machine.

The other weird thing, is the answer came from someone in the Richmond,
Virginia area, which is where I'm located. Small world indeed. :-)

Thanks,

don

Don Semmens
donsem@comcast.net

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