At 04:28 PM 11/05/2004 +0200, you wrote:

>Hello heLen,
>

Quick answers now, sorry, I'm racing against time here, trying to get stuff
done before leaving for Fulda...

> > The traditional solution is to perform SELECT * FROM THE_TABLE after
>you've
> > just done a mass insert or update. Do it in a read-only read-committed
> > transaction, ideally when you have exclusive access (for efficiency, but
> > not a requirement).
>
>You mean "Select *" or "Select count(*)" ? As mentionned, I did perform a
>Select count(*) with exclusive access to take care of GC (although I don't
>see what there is to GC provided I only *inserted* records with exclusive
>access).

Right, for inserts it won't do any good, because there is no GC to be
done. Sorry, I thought you had said "50,000 deletes" before you did the
50,000 inserts.

>I also doubled that with indices recomputes.
>
>As mentionned things where worse after then.

The recomputes won't be visible until after the last user has logged off
and users start logging on again. The new stats are not read into memory
except at file open.

>However, the Select count(*) was not performed in a read-only tx as you
>suggest. Could that make the difference ?

It should - if there was GC happening. But if you were doing inserts with
no preceding deletes, there is no GC needed so no transaction is getting
hit for it.

> > This cuts out the randomness regarding which
> > transaction will get hit with the GC.
>
>So you mean that it could be that my Select count(*) didn't perform GC at
>all ?

Not if there was no GC to do.

>My SP hit the same records each time. After its first execution, shouldn't
>touched records be GCed and consequently, shoudn't subsequent call to the
>same SP return faster ?

Yes, if...

> > Do SET STATS from isql after you've done other cleanup then inspect the
> > affected index selectivities using GSTAT.
>
>Which I did. What should I look for exactly ?

Indexes that you suspect might have changed selectivity as a result of the
inserts. Long dup chains, esp. a high value in Max Dup compared with the
total nodes in the index.

>Before and after SET STATS, my indices all have apparently good
>selectivities except one which is not at all (NULLs all along). Could the
>latter be a pb (index maintenance perf aside) ?

You have an index on a column that stores all nulls?

> > With inserts or deletes, it might
> > prove useful to set some indexes inactive/active if the operation seems
> > likely to have affected the "geometry" of indexes significantly.
>
>Ok. I'll remember that. But if it were the case in my scenario, why did my
>SP execution time finally returned back to normal (without rebuilding the
>indices) ?

Index rebuilding goes on all the time when you are inserting and
deleting. Probably you're seeing initial imbalances from new nodes being
balanced off in the normal course of events. I'm only guessing here; but
if you eliminate GC from the picture, you're really left with unbalanced
index trees as the likely source of performance loss.

>Lots of questions. Sorry.

Not too many answers, sorry.

/heLen

>