It is not enough to just create an index on the temp table, after loading the data?

Maybe I'm not having in count that step, but in the cases I've done it as Isaid i had acceptable results.

Updating statistics in the just created temp table give performace gains?


J.

-----Original Message-----
From: "Art S. Kagel" <kagel@bloomberg.net>
To: informix-list@iiug.org
Date: Mon, 07 Aug 2006 11:45:52 -0400
Subject: 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
>
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list


Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com

Jean Sagi wrote:
> It is not enough to just create an index on the temp table, after loading the data?

Without the data distributions produced by update statistics the optimizer
will not know whether to use one index or the other or to perform a table scan.

> Maybe I'm not having in count that step, but in the cases I've done it as I said i had acceptable results.

You lucked out. Join that temp table in a more complex query and you are
likely to get sub-optimal results

> Updating statistics in the just created temp table give performace gains?

Often, yes. If the optimizer is already making the correct decision by
accident you may not see any gain, but often you will. Certainly results
will be more consistent and predictable.

Art S. Kagel

>
> J.
>
> -----Original Message-----
> From: "Art S. Kagel" <kagel@bloomberg.net>
> To: informix-list@iiug.org
> Date: Mon, 07 Aug 2006 11:45:52 -0400
> Subject: 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
>>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
> Jean Sagi
> jeansagi@myrealbox.com
> jeansagi@gmail.com

Jean Sagi wrote:
> It is not enough to just create an index on the temp table, after loading the data?

Without the data distributions produced by update statistics the optimizer
will not know whether to use one index or the other or to perform a table scan.

> Maybe I'm not having in count that step, but in the cases I've done it as I said i had acceptable results.

You lucked out. Join that temp table in a more complex query and you are
likely to get sub-optimal results

> Updating statistics in the just created temp table give performace gains?

Often, yes. If the optimizer is already making the correct decision by
accident you may not see any gain, but often you will. Certainly results
will be more consistent and predictable.

Art S. Kagel

>
> J.
>
> -----Original Message-----
> From: "Art S. Kagel" <kagel@bloomberg.net>
> To: informix-list@iiug.org
> Date: Mon, 07 Aug 2006 11:45:52 -0400
> Subject: 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
>>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
> Jean Sagi
> jeansagi@myrealbox.com
> jeansagi@gmail.com