Firebird inserts very slow

Question

I have problem with slow executing of INSERT statements on Firebird database. Performance values are as follows:

Inserting 3800 records on notebook with Windows XP takes around 31 seconds (~ 120 inserts per second). On another PC with Windows 7 32 bit this same task takes 80 seconds (~ 50 inserts per second)! Version of Firebird used is 2.5.1 SuperServer from October 2011. The connection technology used is DBExpress.

This is how my database table is created:

CREATE TABLE RESULTS
(
    POS         INTEGER,
    FIELD_CODE  VARCHAR(255),
    FIELD_DESC  VARCHAR(255),
    ORD         INTEGER,
    RVALUE      VARCHAR(2048),
    DETAIL      VARCHAR(2048)
);

And this is source code that accesses it. It is somewhat simplified in comparison with reality (doesnt include callers methods) but does include all fundamental things. Profiler shows that this particular method is the bottleneck. One call of it takes around ~10 msec. So 3800 calls takes ~38 sec.

Field *field = NULL;
int ord = GetFieldOrder(field_code, &field);
if (field == NULL)
{
   return -1;
}

AnsiString sql;
sql.printf("delete from RESULTS where POS = %d and ord = %d", position, ord);
try
{
   Query_SQL->CommandText = sql;
   Query_SQL->ExecSQL();
}
catch (Exception &e)
{
}

if (field->write_field_code)
{
   field_code.printf("'%s'", field->field_code);
}
else
{
   field_code = "NULL";
}
AnsiString field_description;
if (field->write_field_description)
{
   field_description.printf("'%s'", field->field_description);
}
else
{
   field_description = "NULL";
}
sql.printf("insert into RESULTS (POS, FIELD_CODE, FIELD_DESC, ORD, RVALUE) VALUES (%d, %s, %s, %d, '%08X')", position, field_code, field_description, ord, value);

try
{
   Query_Insert->Params->Items[0]->AsInteger = position;
   Query_Insert->Params->Items[1]->AsString = field_code;
   Query_Insert->Params->Items[2]->AsString = field_description;
   Query_Insert->Params->Items[3]->AsInteger = ord;
   Query_Insert->Params->Items[4]->AsString = value;
   Query_Insert->Params->Items[5]->Clear();
   Query_Insert->ExecSQL();
   // Query_SQL->CommandText = sql;
   // Query_SQL->ExecSQL();
}
catch (Exception &e)
{
   return -1;
}
return 0;

As you can see from commented part of code I have tried to parametrize SQL query to speed its repeatedly execution but without significant change. All calls are inside transaction:

TDBXTransaction *transaction = DataModule->Database->BeginTransaction();

unsigned int i;
unsigned int c = meters.size();
for (i = 0; i < c; i++)
{
            ...
    DataModule->InsertDefaultValues(meters[i]); // <---- here are our INSERTs
            ...
}

DataModule->Database->CommitFreeAndNil(transaction);
transaction = NULL;

Output of gstat -h command on database file is as follows:

Database "C:\ELMA\EDEX\CAL_RESULTS.FDB"

Database header page information:

Flags           0
Checksum        12345
Generation      33255
Page size       4096
ODS version     11.2
Oldest transaction  33246
Oldest active       33247
Oldest snapshot     33247
Next transaction    33248
Bumped transaction  1
Sequence number     0
Next attachment ID  60
Implementation ID   16
Shadow count        0
Page buffers        0
Next header page    0
Database dialect    1
Creation date       Jul 6, 2013 12:58:03
Attributes      force write

Variable header data:
*END*

DefaultDbCachePages in firebird.conf is 2048.

During deleting and inserting records, fbserver.exe process fully utilizes one CPU core.

Answer 1

The reason for slow method were not inserts but deletes that were called before them. This simple SQL command speeded my method a few times.

CREATE INDEX INDEX_RESULTS ON RESULTS ( POS, ORD );

I will also do another optimization - instead of delete + insert I will use initial insert and then only updates. Also in this scenario its very important to have index defined for columns that are using for addressing particular record. Sometimes the index can speed things by a few orders. I have made following tests:

10000 updates, without index - 300 seconds

10000 updates, with index - 1.6 seconds