> Vlad Horsun wrote:
>
> >>Personally, and reasonable people can (and will) differ, that temporary
> >>tables aren't worth the tradeoff necessary to implement them.
> >>
> >>
> >
> > Which tradeoff do you (and "reasonable" people) mean ?
> >
> >
> >
> Your implementation replaces a page number, a 32 long, with a structure
> containing a segment and page number. Every place a page number is
> referenced, hundred of places, maybe more, is going to have to be
> changed to initialize the structure. And every time the structure is
> passed, it's going to require more instructions in the calling
> sequence. There are other possible implementations with a lower cost,
> but the one you suggest rubs up against a subjective judgment: Is the
> runtime cost of a feature that everyone pays all the time fairly
> balanced the the benefit of the feature? My take, and it's just my
> personal opinion, that in this case, benefits don't justify the cost of
> that implementation. This isn't to say that I'm against global
> temporary tables. They're a useful feature, and as you point out, part
> of the standard. I just wish we could figure out an implementation that
> didn't have such a high engineering, maintenance, and runtime cost.

Now i updated my sources with GTT's to current HEAD and can make
comparisons to understand how my GTT's implementation affects performance.
Some time ago we with Oleg LOA has dispute about joins and index access
performance of Firebird. He propose simple but showing test which i have until
now and which i used to measure GTT's drawbacks. So here the test.

CREATE TABLE RND1 (
ID INTEGER NOT NULL,
VAL DOUBLE PRECISION NOT NULL
);
CREATE INDEX IDX1 ON RND1 (ID);

...

CREATE TABLE RND10 (
ID INTEGER NOT NULL,
VAL DOUBLE PRECISION NOT NULL
);
CREATE INDEX IDX10 ON RND10 (ID);

Table RND1 filled by 1 000 000 random records. There are very few duplicates
in field ID. All other tables populated from RND1 i.e. all have the same data.

Test query1 is

SELECT COUNT(*)
FROM RND1 R1
JOIN RND2 R2 ON R1.ID = R2.ID
JOIN RND3 R3 ON R2.ID = R3.ID
JOIN RND4 R4 ON R3.ID = R4.ID
JOIN RND5 R5 ON R4.ID = R5.ID
JOIN RND6 R6 ON R5.ID = R6.ID
JOIN RND7 R7 ON R6.ID = R7.ID
JOIN RND8 R8 ON R7.ID = R8.ID
JOIN RND9 R9 ON R8.ID = R9.ID
JOIN RND10 R10 ON R9.ID = R10.ID
WHERE R1.ID BETWEEN 500000 AND 500150

The query2 is

SELECT COUNT(*)
FROM J5 (500000, 500150)
JOIN RND6 R6 ON J5.ID = R6.ID
JOIN RND7 R7 ON R6.ID = R7.ID
JOIN RND8 R8 ON R7.ID = R8.ID
JOIN RND9 R9 ON R8.ID = R9.ID
JOIN RND10 R10 ON R9.ID = R10.ID

Where procedure J5 is
CREATE PROCEDURE J5(
A INTEGER,
B INTEGER)
RETURNS (
ID INTEGER)
AS
BEGIN
FOR SELECT R5.ID
FROM RND1 R1
JOIN RND2 R2 ON R1.ID = R2.ID
JOIN RND3 R3 ON R2.ID = R3.ID
JOIN RND4 R4 ON R3.ID = R4.ID
JOIN RND5 R5 ON R4.ID = R5.ID
WHERE R1.ID BETWEEN :A AND :B
INTO :ID
DO SUSPEND;
END

Both queries is equal and has result 1 608 747

Is this test good enough to see GTT implementation
runtime cost ?

First time i tested with ODS 10.1 database.
Here the numbers:

FB 1.5.2 26.15 22.8
Yaffil 887 18.3 14.2
FB 2.0.0.10726 (head) 20.9 16.9
FB 2.0.0.10771 (gtt) 20.9 17.2

Than i restored test db with fb2 and tested with ODS 11:

FB 2.0.0.10726 (head) 19.7 15.6
FB 2.0.0.10771 (gtt) 19.8 15.9

So - there are performance penalty by GTT implementation
but very little.

Regards,
Vlad