Board index » delphi » Interbase Express

Interbase Express

What is the best way to deal with the TIBTransaction component?
Should I create a new transaction for every new query? How much over
head does it involve in creation of a TIBTransaciton instance.

The function below returns a few fields from a database. Is this
method too slow? Is there a better way to do it? Thank you!

function SelectionByCode(Code: String): TSelection;
var
  sq: TIBSQL;
  tr: TIBTransaction;
begin
  sq := TIBSQL.Create(Application);
  tr := TIBTransaction.Create(Application);

  tr.DefaultDatabase := ad;  //TIBDatabase
  tr.Active := True;

  sq.Transaction := tr;

  sq.SQL.Clear;
  sq.SQL.Add('select * from selection');
  sq.SQL.Add('where SERVER_ID = ' + IntToStr(ServerID));
  sq.SQL.Add('and CODE = ''' + Code + '''');

  sq.ExecQuery;

  if not sq.Eof then
  begin
    Result.Found := True;
    Result.ID := sq.FieldByName('ID').AsInteger;
    Result.Description := sq.FieldByName('Description').AsString;
    Result.Price := sq.FieldByName('PRICE').AsCurrency;
    Result.Code := Code;
    Result.Output_Command :=
sq.FieldByName('OUTPUT_COMMAND').AsString;
    Result.Input_Command := sq.FieldByName('INPUT_COMMAND').AsString;
  end else
    Result.Found := False;

  tr.Commit;

  sq.Close;
  sq.Free;
  tr.Free;

end;

 

Re:Interbase Express


Your way will work ,

- sharing a Transaction will be cheaper of course, don't know by how much.
It becomes different when you do Updates and Inserts.

- you could use an IBSql instead of TIBQuery, is't leaner.

- if you execute the same SQL a lot it becomes more efficient to keep it in
a more permanent query (or ibsql), use parameters for Server_ID etc and
.Prepare it.

Note that your Execute below involves an automatic Prepare& UnPrepare.
Prepare involves sending SQL text to server where it is translated and
optimized.

-hh-

Quote
"Pin Lu" <P...@stredo.com> wrote in message

news:3ac6719c.114870182@8.8.8.88...
Quote
> What is the best way to deal with the TIBTransaction component?
> Should I create a new transaction for every new query? How much over
> head does it involve in creation of a TIBTransaciton instance.

> The function below returns a few fields from a database. Is this
> method too slow? Is there a better way to do it? Thank you!

> function SelectionByCode(Code: String): TSelection;
> var
>   sq: TIBSQL;
>   tr: TIBTransaction;
> begin
>   sq := TIBSQL.Create(Application);
>   tr := TIBTransaction.Create(Application);

>   tr.DefaultDatabase := ad;  //TIBDatabase
>   tr.Active := True;

>   sq.Transaction := tr;

>   sq.SQL.Clear;
>   sq.SQL.Add('select * from selection');
>   sq.SQL.Add('where SERVER_ID = ' + IntToStr(ServerID));
>   sq.SQL.Add('and CODE = ''' + Code + '''');

>   sq.ExecQuery;

>   if not sq.Eof then
>   begin
>     Result.Found := True;
>     Result.ID := sq.FieldByName('ID').AsInteger;
>     Result.Description := sq.FieldByName('Description').AsString;
>     Result.Price := sq.FieldByName('PRICE').AsCurrency;
>     Result.Code := Code;
>     Result.Output_Command :=
> sq.FieldByName('OUTPUT_COMMAND').AsString;
>     Result.Input_Command := sq.FieldByName('INPUT_COMMAND').AsString;
>   end else
>     Result.Found := False;

>   tr.Commit;

>   sq.Close;
>   sq.Free;
>   tr.Free;

> end;

Re:Interbase Express


Thanks for reply.

I wrote a testing program.
Creating a new Transaction for TIBSQL involving around 20% overhead to
retrieve the data in previous example.
Using TIBSQL with shared transaction take only 10% of the time needed
by TIBQuery.

TIBSQL seems to be a much better choice if we don't need to bond it to
a control. The only thing is that TIBSQL does not start an inactive
transaction automatically. so we have to check the state of the
tranction first. which is no big deal.

Quote
On Mon, 2 Apr 2001 11:49:16 +0200, "HH" <or...@rendo.dekooi.nl> wrote:

>Your way will work ,

>- sharing a Transaction will be cheaper of course, don't know by how much.
>It becomes different when you do Updates and Inserts.

>- you could use an IBSql instead of TIBQuery, is't leaner.

>- if you execute the same SQL a lot it becomes more efficient to keep it in
>a more permanent query (or ibsql), use parameters for Server_ID etc and
>.Prepare it.

>Note that your Execute below involves an automatic Prepare& UnPrepare.
>Prepare involves sending SQL text to server where it is translated and
>optimized.

>-hh-

>"Pin Lu" <P...@stredo.com> wrote in message
>news:3ac6719c.114870182@8.8.8.88...
>> What is the best way to deal with the TIBTransaction component?
>> Should I create a new transaction for every new query? How much over
>> head does it involve in creation of a TIBTransaciton instance.

>> The function below returns a few fields from a database. Is this
>> method too slow? Is there a better way to do it? Thank you!

>> function SelectionByCode(Code: String): TSelection;
>> var
>>   sq: TIBSQL;
>>   tr: TIBTransaction;
>> begin
>>   sq := TIBSQL.Create(Application);
>>   tr := TIBTransaction.Create(Application);

>>   tr.DefaultDatabase := ad;  //TIBDatabase
>>   tr.Active := True;

>>   sq.Transaction := tr;

>>   sq.SQL.Clear;
>>   sq.SQL.Add('select * from selection');
>>   sq.SQL.Add('where SERVER_ID = ' + IntToStr(ServerID));
>>   sq.SQL.Add('and CODE = ''' + Code + '''');

>>   sq.ExecQuery;

>>   if not sq.Eof then
>>   begin
>>     Result.Found := True;
>>     Result.ID := sq.FieldByName('ID').AsInteger;
>>     Result.Description := sq.FieldByName('Description').AsString;
>>     Result.Price := sq.FieldByName('PRICE').AsCurrency;
>>     Result.Code := Code;
>>     Result.Output_Command :=
>> sq.FieldByName('OUTPUT_COMMAND').AsString;
>>     Result.Input_Command := sq.FieldByName('INPUT_COMMAND').AsString;
>>   end else
>>     Result.Found := False;

>>   tr.Commit;

>>   sq.Close;
>>   sq.Free;
>>   tr.Free;

>> end;

Other Threads