Subject First insert in large table takes extreme time
Author Kjell Rilbe

In my now probably well-known 150 million record table ;-) "Uppgift" it
seems like the first insert from a new connection takes an extreme time
to complete. I think it has been running for over an hour by now.

Is there any particular reason this might happen EVERY TIME?

Now, I haven't investigated it thoroughly, but what I see is:

1. Start the app that opens a single connection and is the only
connected app/user.

2. Start an operation, which does several selects and then inserts.

3. Check mon$statements, which seem to be running selects in a timely
manner. Then it seems to be stuck at an insert into "Uppgift". Now, I
can't be sure this is the same statement I'm seeing on consecutive
selects from mon$statements, but I keep getting the same
mon$statement_id and the same mon$sql_text. I am also not certain that
this is the first insert.

4. If i run the same operation again without closing and reopening the
app, it's much faster.

5. The table has several indices, on one column each, one of which is a
PK. There are 18 columns, 9 of which have an index. 7 of the indices
have selectivit < 0.0006, one has 0.5 and one has selectivit 1. The 0.5
index will become more selective later on while the 1.0 one will
probably keep having over 99% nulls, but be very useful for the records
where the indexed column is not null.

I'm running FB 64 bit on Windows 2008 Server Web edition 64
bit with 8 Gbyte RAM and two SATA RAID 1 disk pairs, one of which has
the OS and program files and the other has the DB file.

Should I provide more stats/info to help answering my question?

Kjell Rilbe
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64