Performance of TIBCQuery during select?

[Bjarke_Moholt]

I am working with TIBCQuery to enhance the performance of select statements, having previously used the TQuery of BDE. The thought was to use the FetchAll mode to load the entire query at once, instead of requesting the records one at a time, to minimize the database access overhead.

So far I've experienced that the time required to traverse the query is reduced, but that the time required to retrieve the query is increased, much as expected. However, the net time from the query is sent to data is read is almost the same in both cases

Here's what I do:

Code: Select all

procedure TForm4.Button2Click(Sender: TObject);
var
  Q     : TQuery;
  QIbc  : TIBCQuery;
  Dummy : integer;
  BeginTime   : real;
  EndTIme     : real;
  VT : TVirtualTable;
  Loader : TIBCLoader;
  i : integer;
  Param : TParam;
begin
  if CheckBOx1.Checked then begin
    Q := TQuery.Create(nil);
    try
      Q.DatabaseName := FDatabase.DatabaseName;
      Q.SQL.Add('Select * from MODELQUERY543875505');
      Q.Open;
      Q.First;
      BeginTime := Now;
      while not(Q.Eof) do begin
        Dummy := Q.Fields[0].AsInteger;
        Dummy := Q.Fields[1].AsInteger;
        Dummy := Q.Fields[2].AsInteger;
        Dummy := Q.Fields[3].AsInteger;
        Dummy := Q.Fields[4].AsInteger;
        Dummy := Q.Fields[5].AsInteger;
        Dummy := Q.Fields[6].AsInteger;
        Dummy := Q.Fields[7].AsInteger;
        Dummy := Q.Fields[8].AsInteger;
        Dummy := Q.Fields[9].AsInteger;
        Q.Next;
      end;
      EndTime := NOw;
      Label1.Caption := IntToStr(Round(Q.RecordCount/((EndTime-BeginTime)*86400)));
    finally
      Q.Free;
    end;
  end else begin
    QIBC := TIBCQuery.Create(nil);
    QIbc.FetchAll := True;
    QIBC.UniDirectional := False;
    try
      QIbc.Connection := Self.IBCConnection;
      QIbc.SQL.Add('Select * from MODELQUERY543875505 ');

      QIbc.Open;
      QIbc.First;
      //VT := TVirtualTable.Create(nil);
      //Loader := TIBCLoader.Create(nil);
      //Loader.Connection := Self.IBCConnection;
      //Loader.

      BeginTime := Now;
      I := 1;
      while not(QIBC.Eof) do begin
        Dummy := QIbc.Fields[0].AsInteger;
        Dummy := QIbc.Fields[1].AsInteger;
        Dummy := QIbc.Fields[2].AsInteger;
        Dummy := QIbc.Fields[3].AsInteger;
        Dummy := QIbc.Fields[4].AsInteger;
        Dummy := QIbc.Fields[5].AsInteger;
        Dummy := QIbc.Fields[6].AsInteger;
        Dummy := QIbc.Fields[7].AsInteger;
        Dummy := QIbc.Fields[8].AsInteger;
        Dummy := QIbc.Fields[9].AsInteger;
        QIbc.Next;
      end;
      EndTime := NOw;

Is there a way to get better performance on my reads? As I said earlier, I have tried with FetchAll and uni/bi directional modes, as well as tried a few different values for BlockReadSize using fetchall=false

[AndreyZ]

To improve the performance of data fetching in IBDAC, we suggest you to set the following options:
1. TIBCQuery.Options.DeferredBlobRead and TIBCQuery.Options.DeferredArrayRead to True. The DeferredBlobRead and DeferredArrayRead properties are used for fetching all BLOB and array values correspondingly when they are explicitly requested. This will increase performance in case if your tables have BLOB and array fields.
2. TIBCQuery.ReadOnly to True (in case if you do not need to modify data).
3. TIBCQuery.FetchAll to True. In this case all data will be fetched to the client at once.
4. TIBCQuery.FetchRows to a value that gives the most data fetching speed on your network (it is individual for each network). FetchRows affects only data fetching speed.

[marcodor]

Code: Select all

   QIBC := TIBCQuery.Create(nil);
    QIbc.FetchAll := True;
    QIBC.UniDirectional := False;
    try
      QIbc.Connection := Self.IBCConnection;
      QIbc.SQL.Add('Select * from MODELQUERY543875505 ');
      QIbc.Open;
      QIbc.First;

1. Unidirectional is False by default, you don't need to set it to False. If you need to traverse/load your data once just moving forward as shown in your code it's a good idea to set Unidirectional to True. This will avoid memory cache creation/management on the client and will improve speed.

2. FetchAll makes sense just when Unidirectional is False. It load on client side all records from the server in one batch at Open, but it will take some time to load all records.

3. There is no sense to call First after Open. We already on the first record.

4. If you need just load your data against just display data in a grid, ie user interaction, consider increase FetchRows by experiments, let's say 500 or 1000.

[AndreyZ]

An addition: FetchAll=True and UniDirectional=True are mutually exclusive. It means that if you set FetchAll to True, UniDirectional is set to False automatically. And vice versa, if you set UniDirectional to True, FetchAll is set to False automatically.

[Bjarke_Moholt]

1. Unidirectional is False by default, you don't need to set it to False. If you need to traverse/load your data once just moving forward as shown in your code it's a good idea to set Unidirectional to True. This will avoid memory cache creation/management on the client and will improve speed.

Does this also hold true for an external database, where latency may be an issue? I imagine a lot of back-and-forth communication each time I tap .next on the query, is this a worthwhile tradeoff from the memory speed issues?

AndreyZ wrote:
4. TIBCQuery.FetchRows to a value that gives the most data fetching speed on your network (it is individual for each network).

I have tried to work a little with the FetchRows value for a unidirectional query. The optimal value for my system seems to be 1, is this reasonable?

[PavloP]

1. The Unidirectional value doesn't affect the server traffic. It affects memory consumption on a client machine.

2. Most probably, FetchRows=1 is the optimal solution for your case.