Slow First Query

[doug4]

The first query after connecting to a database is very slow. All queries on the same table after the first query are much faster. In the example below the first query takes about 10 seconds on a WAN. The second query only takes about 0.3 seconds. I am using Firebird 2.1.3, ZEOSlib 7-dev with Delphi 2009. The DataSource for ZReadOnlyQuery1 is assigned to a DBGrid.

I have seen others posts in the forums with a similar problem but I could not find a solution. How can the speed for the first query be improved?

procedure TForm1.OpenQueryButtonClick(Sender: TObject);
begin
StartTime := Now;

with ZReadOnlyQuery1 do
begin
Close;
Sql.Clear;
Sql.Text := 'select field1 from table1';
ParamCheck := true;
Filtered := true

if not Prepared then
Prepare;

FetchRow := 30;

Open;
end;

ElapsedTime := MilliSecondSpan(Now, StartTime) / 1000;
end;

Thanks for your help,
doug4

[guidoaerts]

I have no idea, but,
what do you get when you start timing after
FetchRow := 30;
just before
Open;
?
Guido

[doug4]

The times are almost identical. It looks like everything up to the Open statement takes about 0.125 seconds on my WAN. On a LAN the difference is only about 0.01 seconds. I just need to find a way to make the first query on a WAN faster. On a LAN the first query is fast enough. On a WAN the second query is fast enough, but the first query is not.

First Query after connecting to database
ElapsedTime: 10.141 seconds
ElapsedTime2: 10.016

Second Query
ElapsedTime: 0.250 seconds
ElapsedTime2: 0.125 seconds

procedure TForm1.OpenQueryButtonClick(Sender: TObject);
begin
StartTime := Now;

with ZReadOnlyQuery1 do
begin
Close;
Sql.Clear;
Sql.Text := 'select field1 from table1';
ParamCheck := true;
Filtered := true

if not Prepared then
Prepare;

FetchRow := 30;

StartTime2 := Now;
Open;
end;

EndTime := Now;
ElapsedTime := MilliSecondSpan(EndTime, StartTime) / 1000;
ElapsedTime2 := MilliSecondSpan(EndTime, StartTime2) / 1000;

end;

I would appreciate more suggestions. Thanks for the feedback.
doug4

[guidoaerts]

I have not yet timed my WAN sql's , but i am surely gonna do that. I will get back with my results.
Guido

[guidoaerts]

connecting : local pc : 31-60 msec, lan : 31-46 msec, wan : 230-600 msec
first query: local pc : 31-46 msec, lan :61 msec, wan : 203-264msec
second query: local pc : 16 msec, lan : 31 msec, wan : 78-94 msec

It is a small table, 250 rows, selecting 4 colums into a dbgrid with a TZquery.
(maybe there is a difference with TZReadOnlyQuery, I will try)
WinXp, Lazarus 0.9.26.2, fpc 2.2.2, FB 2.1.2, Zeos 6.6.5-stable, STunnel 4.29, Openssl 0.9.8

So far, it seems there is some delay, due to internet traffic, and, indeed, the first query is slower. But not that much. Maybe this is something to ask on the firebird forum?
Guido

[doug4]

I reinstalled ZEOSlib with the compiler directive "FOSNOMETA" enabled (changed the line "{.$DEFINE FOSNOMETA}" to {$DEFINE FOSNOMETA} in "...\src\Zeos.inc". This fixed the slow first query on a WAN. Now the first query is about the same as the second query.

Did you enable the compiler directive "FOSNOMETA" when you installed ZEOSlib in Lazarus?

There is a warning about using this compiler directive "UNTESTED HIGH RISK". If you have been using the compiler directive, have you run into any problems? Does anyone know if this has been tested extensively yet?

- doug4

[guidoaerts]

I have no experience with "FOSNOMETA", because so far I didn't have problems with slow querys.
Guido

[mdaems]

The difference is in the metadata fetching. On the first query zeoslib searches information on the fields that are available in your resultset. My impression is this metadata is mainly needed for updatable resultsets so zeoslib can post back the changes to the database automatically.
The directive just disables retrieving this metadata. Probably the only effect is that the automatic posting of dataset changes doesn't work correctly.

Mark