> From: Helen Borrie
> To: firebird-support@yahoogroups.com
> Sent: Tuesday, May 11, 2004 4:48 PM
> Subject: Re: [firebird-support] UPs and DOWNs after bulk inserts.
>
>
> 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...

Argh. Don't blame me if I "try" again. Maybe you'll find a tiny time window
before leaving or someone else can jump in... ;-)

> > > 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.

Ok.

> >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.

That's why I actually disconnected the only connection and reconnected. ;-)

> >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.

It's not clear for me. Suppose there was GC to do (there was not).

What's the difference between following actions :

- Select count(*) in a read-only read-committed exclusive tx
- Select count(*) in a read-committed exclusive tx
- Select count(*) in a read-only read-committed non-exclusive tx
- Select count(*) in a read-committed non-exclusive tx

[..]

> > > 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.

Ok. I'll look.

> >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?

Yes. Don't know what to do with this one yet. Half of the records (which
will come later) *will* use that field. And for those records the index will
be selective. I will most probably add the PK to the index though.

> > > 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.

Humm. I'd like to. But from the moment the SP went dizzy until the moment it
went back to normal (a few minutes), no records were modified.


So, mistery remains for now...


Thanks.

--
Jerome