TIBCTable.Insert duration with lot of records

calou · Post by **calou** » Tue 10 Jun 2008 12:14

Hello,

I need to insert a lot of records (30 000 rows) in a firebird database.
Here is my code for one record:

frmMain.TblEnreg.Insert;
frmMain.TblEnregNum.Value:=NbEnreg+1;
//Enregistre le nom et références du DAT
frmMain.TblEnregNom_Site.AsString:=frmMain.SiteLog;
frmMain.TblEnregRef_DAT.AsString:=frmMain.RefDATLog;
frmMain.TblEnregDate.Value:=date;
frmMain.TblEnregHeure.Value:=heure;
frmMain.TblEnregVe.Value:=StrToInt(ve);
frmMain.TblEnregVs.Value:=StrToInt(vs);
frmMain.TblEnregPhase.Value:=StrToInt(phase);
frmMain.TblEnregK.Value:=StrToInt(Rapp);
frmMain.TblEnregTyp.AsString:=Typ;
Inc(NbEnreg);

Here is sql given by dbmonitor (for one record) :
INSERT INTO ENREGISTREMENTS
(NUM, REF_DAT, NOM_SITE, DATE_ENREG, HEURE, VE, VS, K, PHASE, TYP)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
CommitRetaining;

My problem is that this is very very long. If i do it with a bde database (with the same code) it takes less than half time.

How could i optimise it?

Thank you very much

Regards

calou · Post by **calou** » Wed 11 Jun 2008 15:50

Hello

Any idea?

Regards

rcmz · Post by **rcmz** » Wed 11 Jun 2008 22:57

What Version of Firebird ?

Did you try doing it via a SQL statement using TIBCQuery or TIBCSql ?

I think you can do this much faster if you use a SQL statement and read the performance issues in the help file.

The other thing you can do is a batch insert and that also is very fast.

TIA
Ramiro

Plash · Post by **Plash** » Thu 12 Jun 2008 06:24

You can assign INSERT statement to the SQL property of TIBCQuery or TIBCSQL component, and call it directly. Also call Prepare method at the beginning of the insert procedure.

Code: Select all

Query.SQL.Text := 'INSERT INTO ENREGISTREMENTS ' +
  '(NUM, REF_DAT, NOM_SITE, DATE_ENREG, HEURE, VE, VS, K, PHASE, TYP) ' +
  'VALUES (:NUM, :REF_DAT, :NOM_SITE, :DATE_ENREG, :HEURE, :VE, :VS, :K, :PHASE, :TYP)';
Query.Prepare;
for i := 1 to 30000 do begin
  Query.Params[0].AsInteger := NbEnreg+1;
  Query.Params[1].AsString := frmMain.SiteLog;
  Query.Params[2].AsString := frmMain.RefDATLog;
  ...
  Query.Execute;
end;
Query.Unprepare;

calou · Post by **calou** » Thu 12 Jun 2008 08:43

Hello,

Thank you for your answer

I have done time measurement.

With TblInsert it takes 50 sec
With query it takes 40 sec (it is better)

But if i do the same thing (with tblinsert) and a paradox database it takes about 10 sec!!!

I don't understand why the difference is so big?
I use FB2.1

Thank you for help

Regards

Plash · Post by **Plash** » Fri 13 Jun 2008 09:07

It depends on the way you are using to insert data into a Paradox table.
If you use TTable BDE component, maybe it can modify the Paradox table directly. While insertion into the Firebird table is performed with SQL statements.

Archer66 · Post by **Archer66** » Fri 13 Jun 2008 12:07

Try with transactions.

Also check sweep value.

rcmz · Post by **rcmz** » Sun 15 Jun 2008 18:25

Did you tried a batch Insert ?

Since your SQL sentence is not returning any value you should use IBSQL

Jus my 2 cents.
Ramiro

calou · Post by **calou** » Mon 16 Jun 2008 07:28

Hello,

I will test with IBSQL. I don't really understand the difference between ibcquery and ibcsql?
What is a batch insert?

Thank you for help

Regards