Hi heLen,

> From: Helen Borrie
> To: firebird-support@yahoogroups.com
>
> At 03:31 PM 22/06/2004 +0200, you wrote:
> >Hello,
> >
> >I'm using a fake temporary table to hold lists of IDs. It has two fields

:

> >LISTID and ID.
> >The common scenario is that I insert records in this table at the start

of

> a
> >tansaction and delete them all when committing, so that the table always
> >"looks" empty.
> >
> >I expect to insert and delete at most 300000 rows a day (10 consecutive
> days
> >per month).
> >
> >What kind of issues should I expect and how should I prevent them ?
>
> Because of the compound index, it's possible the index will become very
> inefficient if you have a lot of duplications in one or two of index key
> columns.

Just to make sure I got it clear. You mean that the fact the index is unique
isn't sufficient because old record versions also count in that matter.
Right ?

The first field of the index (LISTID) comes from a generator and I never
"reuse" a LISTID so it shouldn't introduce too many duplicates. Looking at
the whole index, this means there shouldn't be any duplicate at all even
considering old versions.

In this case, I'm safe on the "duplicate" side. Right ?

> So that's a "plus" for deleting as you go.
>
>
> >Some ideas ...
> >
> >Should I issue a "Select count(*)" every once in a while during the day

to

> >take care of GC, or should I leave that up the nightly SWEEP ?
>
> If you're sweeping nightly anyway, forget about trying to force GC - just
> make sure that any transactions on the table are hard-committed.
>
> >Should I delete rows during production at all, or instead leave that up

to

> a
> >nightly bulk_deletes_task ? (The table has a unique compound index, so I
> >think it shouldn't introduce to much performance penalty)
>
> Depends on the index. If you notice things tending to slow down, you're
> probably better to delete as you go. If the performance keeps up, I'd go
> for the nightly bulk delete with a hard commit, and follow it up with a
> sweep.

Ok. Thanks. I'll follow your advice.

Regards.

--
Jerome