Subject Re: [firebird-support] Embedded Firebird activity after Multiple Delete
Author Svein Erling Tysvaer
Sorry that no-one has answered you yet. I'll guess, but your best bet is
that someone else replies - I'm good with SQL puzzles, but don't know
much about other stuff.

I think embedded basically is the same as SuperServer and that switching
to SS will not make much difference. I just tried inserting about 850000
records on Fb 2.5 Alpha, delete 50000 of them and then do a select
count, first when indexing a field with pretty low selectivity, and then
doing the same when I had indexed a field with a lot better selectivity.

The select count was pretty quick in both cases, I think the first case
ran in 1.8 seconds, the second in just below 6 seconds. Things have
changed between version 2.0 and 2.5, though I don't know when the issue
with low selectivity indexes was resolved, nor remember whether it
manifested itself when deleting (which I thought was the case) or when
doing garbage collection.

Anyway, ten minutes simply sounds too long. Firebird 2.5 should be
better than 2.0, but not 100 times quicker.

Anything particular to your situation, do you e.g. have large blob
fields in your table or ON DELETE triggers that makes the delete involve
a lot more records than the ones you actually delete? What about indexes?


Matthew Finch wrote:
> Hello,
> I am using embedded firebird version in my windows application.=
> I have found that if multiple deletes e.g. 50,000 from 100,000 rows is per=
> formed then there is a long delay processing a subsequent select statement.=
> This delay may stretch to 10mins or so. Is this delay due to the embedded =
> firebird server performing inline garbage collection therefore the select s=
> tatement request is waiting for the garbage collection to complete before b=
> eing requested?? It does seem that the delay is dependent on the number of =
> rows being deleted and the size of the current database. Has anyone else ex=
> perienced such behaviour. I am=A0wondering=A0whether or not to change to an=
> non embedded version that=A0I would suspect would remove such=A0an issue. =
> It would be good to=A0hear if this is an embedded server=A0related problem=
> =A0before switching to a superserver.
> Thanks for any=A0help,
> Kindest Regards,
> Matt Finch=A0