Trial user: Firebird INSERT with IBDAC slower than IBX!?

[fschulze]

Hello,
I'm locking for new database components for Delphi2009, because Firebird isn't full supported by IBX.
At website I read: "IBDAC is x times faster than IBX"!
Ok, I loaded the trial version.
After converting a program with IBDAC-wizard I measured:
INSERT statement is 3 times slower than IBX-version
SELECT statement is not significant faster, nearly the same time.

You can try it out. Here is the IBX-sourcecode of my test program, run this, convert it to IBDAC, run a second time and compare the results...

Code: Select all

procedure TForm2.Button1Click(Sender: TObject);
var
 Tick : DWord;
 AnzWerte,I,ID,Anz : integer;
 PN : string;
begin
 Memo1.Lines.Clear;
 With DataModule do
  begin
   //open DB
   Tick := GetTickCount;
   IBDatabase1.Connected := True;
   Memo1.Lines.Add('duration OpenDatabase: '+inttostr(GetTickCount-Tick));
   //create table
   Tick := GetTickCount;
   IBTransaction1.StartTransaction;
   IBSQL1.SQL.Text := 'CREATE TABLE FRANKS_TESTTABELLE (ID INTEGER NOT NULL, PATGRUPPE INTEGER DEFAULT 0 NOT NULL, PATNAME VARCHAR(100) DEFAULT '''' NOT NULL);';
   IBSQL1.ExecQuery;
   IBSQL1.SQL.Text := 'ALTER TABLE FRANKS_TESTTABELLE ADD PRIMARY KEY (ID)';
   IBSQL1.ExecQuery;
   IBTransaction1.Commit;
   Memo1.Lines.Add('duration CREATE TABLE: '+inttostr(GetTickCount-Tick));
   //fill table
   AnzWerte := StrToInt(EAnzWerte.Text);//default set to 2000
   Tick := GetTickCount;
   IBTransaction1.StartTransaction;
   for I := 0 to AnzWerte - 1 do
     begin
      IBSQL1.SQL.Text := 'INSERT INTO FRANKS_TESTTABELLE (ID, PATGRUPPE, PATNAME) VALUES (:ID, :PATGRUPPE, :PATNAME);';
      IBSQL1.ParamByName('ID').AsInteger := I;
      IBSQL1.ParamByName('PATGRUPPE').AsInteger := random(20);
      IBSQL1.ParamByName('PATNAME').AsString := inttostr(random($FFFF))+inttostr(random($FFFF))+inttostr(random($FFFF));//just any text
      IBSQL1.ExecQuery;
     end;
   IBTransaction1.Commit;
   Memo1.Lines.Add('duration INSERT: '+inttostr(GetTickCount-Tick));
   //load data
   Tick := GetTickCount;
   IBTransaction1.StartTransaction;
   for I := 0 to AnzWerte - 1 do
     begin
      IBQuery1.SQL.Text := 'SELECT ID, PATNAME from FRANKS_TESTTABELLE where PATGRUPPE=:PATGRUPPE;';
      IBQuery1.ParamByName('PATGRUPPE').AsInteger := random(20);
      IBQuery1.Open;
      while not IBQuery1.EOF do
       begin
         ID := IBQuery1.FieldByName('ID').AsInteger;
         PN := IBQuery1.FieldByName('PATNAME').AsString;
         Anz := ID+length(PN);//if I don't use ID and PN the compiler drops the FieldByName-statements
         if Anz<0 then
          ShowMessage('impossibel!');
         IBQuery1.Next;
       end;
     end;
   IBTransaction1.Commit;
   Memo1.Lines.Add('duration SELECT: '+inttostr(GetTickCount-Tick));
   //drop table
   Tick := GetTickCount;
   IBTransaction1.StartTransaction;
   IBSQL1.SQL.Text := 'DROP TABLE FRANKS_TESTTABELLE;';
   IBSQL1.ExecQuery;
   IBTransaction1.Commit;
   Memo1.Lines.Add('duration DROP TABLE: '+inttostr(GetTickCount-Tick));
   //close database
   IBDatabase1.Connected := False;
   Memo1.Lines.Add('duration CloseDatabase: '+inttostr(GetTickCount-Tick));
  end;//with DataModule
end;

My Questions:
I believe that IBDAC is faster than IBX. But where are my faults?
What to do to get a real fast database access?

After convert with wizard I had to change several lines at sourcecode from

Code: Select all

IBSQL1.ExecQuery;

to

Code: Select all

IBSQL1.ExecSQL;

manually.
Why the wizard don't did this work?

Thanks for your answer.

Frank

[fschulze]

Hello,

The Error

INSERT statement is 3 times slower than IBX-version

I've found.

The IBX -> IBDAC wizard substitutes then
old TIBSQL component by
new TIBCQuery.

On INSERT statements is TIBCQuery significant slower than TIBSQL.

So Devart has to fix this bug at wizard program...

Also a change from IBSQL1.ExecQuery (IBX) to IBSQL1.Execute (IBDAC) is neccessary...

My 2. problem: no significant higher speed on INSERT and SELECT remains. What to to do to get the speed values promised at IBDAC website?

Frank

[Plash]

TIBSQL is replaced with TIBCQuery because TIBSQL can be used to open and fetch data from SELECT query while TIBCSQL does not have such functionality.

Migration Wizard changes only types of the components. It does not convert user code.

IBDAC speed is approximately the same as IBX in this case. There are results of Data Loading test at out web site that shows higher speed than IBX. But we have found that these results are incorrect because IBX component was used in not optimal way.