> True, but not all applications are "interactive browsers". If you

want

> such optimization, then define required indices. Server has no
> information what's preferred behavior for you, so it's pointless to
> complicate the server's code to give you "optimizations" you don't

want

> and that in fact would hurt performance in some cases. Anyway, your
> proposal to build temporary indices on the fly is flawed, as it

might

> work only for simple queries from single table.
>
> best regards
> Pavel Cisar
> IBPhoenix
>

I run the following code many times on FDB 2 (upgraded on ODS 11.0
and now has 30351 recs on the test table) on the Varchar(100) field.
(I forgot to mention that the server has the cache set up at 10.000
pages and the FDB 2 has 2335 pages allocated, but isn't the only FDB
attached there, if this has any importance).

The code:

procedure TForm2.Button1Click(Sender: TObject);
var
i: Integer;
t, nExec, nStop: cardinal;

begin
with dmoMain.ibdMain do
begin
Disconnect;
DatabaseName:=cboFDB.Text;
Connect;
end;

t:=GetTickCount;

with dmoMain.ibtMain do
begin
for I := 0 to memExec.Lines.Count - 1 do
begin
ExecuteImmediate(memExec.Lines[i], nil); //for creating
index...
end;

SQL.Text:=memSQL.Text; //the query
Open;
nExec:=GetTickCount;

if cbxFetchAll.Checked then
FetchAll;
nStop:=GetTickCount;

Close;
end;
ShowMessage(Format('Exec in: %d msec. Fetch in: %d msec', [nExec-t,
nStop-t]));
end;


The results: (are average values in msec)
1. Without the index:

- First run (run the program, first time when I pressed the button):
Exec: 328
Fetch: 1391

- Following runs (I pressed the button many times):
Exec: 297
Fetch: 1375

2. With the index:

- First run (including index creation):
Exec: 313
Fetch: 1344

- Following runs (no drop/create - the index is already there):
Exec: 62
Fetch: 1125
(Sometimes Exec: 47, Fetch: 1063)

Any comments?

In fact, I'm amazed because:
1. In the index variant is included the creation of the index with
all the overhead there (checks, scans, stats, saving the structure in
RDB$ tables a.s.o.) things which for a internal, sorted structure
aren't needed. (But for a normal index of course...) Also the index
code has to deal with the (possible) table updates which in the case
of a temporary sorted list isn't a problem. But with all of these
overheads the engine is performing very well.

2. In the 'fetch all' case, also the index is faster (I wonder why? -
the page cache perhaps?) knowing that the on-disk natural scan order
is the fastest as the Pavel pointed out. (IIRC, also Craig Stuntz has
an article on this...). Also is remarcable that I used the IBOQuery's
FetchAll method. I think that any 'while not Eof do..begin ..Next
end' cycle would be exercited smaller pressure on the server. Or
perhaps I'm wrong?

3. Perhaps we need to take in consideration that today the memory is
much more cheaper than in the days in which FB (read: IB) was made
and we need to adapt?... (Sorry if its a bad presumption, as I stated
in another message I don't saw the code...)

HTH,
m.Th.