SEO

#1 (**permalink**) 04-20-2008, 07:22 AM

I think this is in the CDI archives, but I haven't found it yet. If
memory serves me right, it was discussed on this list some years ago.

Some of our programs run batch processes, dropping & creating tables,
and then inserting lots of records into them ... BUT, and this is my
problem, the program also reads from the tables being inserted into.

Because these tables are recreated, no (update) statistics exist on
them. But I also can't create them, run update stats and then read from
them - the reading and inserting happens at the same time.

How do I overcome this problem ? Hope my question is clear enough.

Dirk

IDS7.31FD3
Solaris 9

Dirk Moolman
Database and Unix Administrator
MXGROUP

"People demand freedom of speech as a compensation for the freedom of
thought which they seldom use."

sending to informix-list

#2 (**permalink**) 04-20-2008, 07:23 AM

Dirk Moolman wrote:
> I think this is in the CDI archives, but I haven't found it yet. If
> memory serves me right, it was discussed on this list some years ago.
>
> Some of our programs run batch processes, dropping & creating tables,
> and then inserting lots of records into them ... BUT, and this is my
> problem, the program also reads from the tables being inserted into.
>
> Because these tables are recreated, no (update) statistics exist on
> them. But I also can't create them, run update stats and then read from
> them - the reading and inserting happens at the same time.
>
> How do I overcome this problem ? Hope my question is clear enough.

Maintain minimal stats on the table (UPDATE STATISTICS LOW .... DROP
DISTRIBUTIONS

. That will force the optimizer to use older less
intelligent logic when evaluating the costs of joining to this table which
should give acceptable performance in this schenario. This update is cheap
to run and you can run it occassionally (say after every 100 or 500 reads)so
that the row counts and index depth (pretty much all that LOW keeps) are not
too far off.

Art S. Kagel

> Dirk

#3 (**permalink**) 04-20-2008, 07:23 AM

Dirk Moolman wrote:

>I think this is in the CDI archives, but I haven't found it yet. If
>memory serves me right, it was discussed on this list some years ago.
>
>Some of our programs run batch processes, dropping & creating tables,
>and then inserting lots of records into them ... BUT, and this is my
>problem, the program also reads from the tables being inserted into.
>
>Because these tables are recreated, no (update) statistics exist on
>them. But I also can't create them, run update stats and then read from
>them - the reading and inserting happens at the same time.
>
>How do I overcome this problem ? Hope my question is clear enough.
>
>Dirk
>
Is this a logged data base? Are you beginning work and committing work
after X transactions (say, every 1,000 or so)? Inserting with a cursor
instead of just doing "insert?" Along with other suggestions about
updating statistics, might not hurt (if it's logged).

#4 (**permalink**) 04-20-2008, 07:32 AM

We had a similar problem and did as Malcolm suggested - ie at a
particular point UPDATE STATS on the table(s) concerned (we also used
cursors with REOPTIMIZATION - such as:
> IF l_reoptimize THEN
> OPEN c_lock_carpool USING m_carpool.supplierRecNo,
> m_carpool.vendorUniqueID
> WITH REOPTIMIZATION
> ELSE
> OPEN c_lock_carpool USING m_carpool.supplierRecNo,
> m_carpool.vendorUniqueID
> END IF

To access tables that may have had thousands of rows added (where a
cursor was declared at the start of the prog) )

Dirk Moolman wrote:
> I think this is in the CDI archives, but I haven't found it yet. If
> memory serves me right, it was discussed on this list some years ago.
>
> Some of our programs run batch processes, dropping & creating tables,
> and then inserting lots of records into them ... BUT, and this is my
> problem, the program also reads from the tables being inserted into.
>
> Because these tables are recreated, no (update) statistics exist on
> them. But I also can't create them, run update stats and then read from
> them - the reading and inserting happens at the same time.
>
> How do I overcome this problem ? Hope my question is clear enough.
>
> Dirk
>
>
> IDS7.31FD3
> Solaris 9
>
>
>
>
>
> Dirk Moolman
> Database and Unix Administrator
> MXGROUP
>
>
>
> "People demand freedom of speech as a compensation for the freedom of
> thought which they seldom use."
>
>
> sending to informix-list

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Running 4GL on table that changes a lot