FireBird : Insert data quickly

[moelski]

Hi !

Actually I test the UnDac 3.0 component. I use a firebird embedded database (version 2.5).

I try to detect what´s the best methode for inserting a lot of data to the database. We have a program which receives data from different sources. The data is send in timesteps between 50 und 2000 Milliseconds.

The problem is that we can connect a lot of sender to our software. So we have multiple Threads which using the database and store data to different tables.

To test the speed of the database connection I used this code:

Code: Select all

  StartTime := Now;
  DBQuery.SQL.Text := 'INSERT Into ' + Table + ' (ID, PROP01, PROP02, PROP03, PROP04, PROP05, PROP06, PROP07, PROP08, PROP09, PROP10) VALUES (:wert1, :wert2, :wert3, :wert4, :wert5, :wert6, :wert7, :wert8, :wert9, :wert10, :wert11);';
  DBQuery.Prepare;
  for I := 1 to Amount do begin
    Inc(Counter);
    DBQuery.Params[0].AsInteger := Counter;
    DBQuery.Params[1].AsFloat := Random(10000);
    DBQuery.Params[2].AsFloat := Random(10000);
    DBQuery.Params[3].AsFloat := Random(10000);
    DBQuery.Params[4].AsFloat := Random(10000);
    DBQuery.Params[5].AsFloat := Random(10000);
    DBQuery.Params[6].AsFloat := Random(10000);
    DBQuery.Params[7].AsFloat := Random(10000);
    DBQuery.Params[8].AsFloat := Random(10000);
    DBQuery.Params[9].AsFloat := Random(10000);
    DBQuery.Params[10].AsFloat := Random(10000);
    DBQuery.ExecSQL;
  end;

This is not like in our application because the time between the datasets is zero. In our application the time between is 50 ... 2000 milliseconds.

Anyway. This code is usefull to detect how long it takes to insert one dataset to the database (I calculate the time for the entries).

I figured out that one dataset takes about 5-10 milliseconds to get into the database. This results in a maximum of 100-200 datasets per second.

But maybe there is a much faster way to insert data to the database. I think transactions won´t help alot (but maybe I´m wrong).

So did you have some tips for speedup?

Greetz
Dominik

[Dimon]

You can use the CachedUpdate mode to load data quickly.Setting the TUniQuery.CachedUpdates property to True enables updates to a dataset to be stored in an internal cache on the client side instead of being written directly to the dataset's underlying database tables. When changes are completed, an application writes all cached changes to the database in the context of a single transaction.

Also you can use the TUniLoader component which serves for fast loading of data to the server.