Juan Jose Ochoa wrote:

> I've had this issue for long time and I haven't found a better
> solution than to seperate it in several statements in a SP like this:
>
> FOR Select ...
>>From P INNER JOIN Z
> group by...
> into :...
> do begin
> FOR SELECT ...
> from T
> where PK = :...
> into :...;
>
> FOR SELECT ...
> from M
> where PK = :...
> into :...;
>
> suspend;
> end
>
> I suppose that all the result set is defined by "P INNER JOIN Z" the
> others are only "useful fields". So you go into T and M only the
> minimal necessary times.
>
> I've been evaluating FB2. and I know this issuse is going to be
> solved by using derived tables and by using the execute block
> statement. I already tested and works fine, just as S.P.
>
> Anyways I still think that the optimizer should detect this scenario
> and "optimeze" for it.
>
> ---
>
> other approach:
>
>>P: 590210 out of 8 mill
>>Z: 339244 out of 4 mill
>>T: 590210
>>M: 334822
>
>
> How many records and reads do you get when you do a:
> SELECT 1
> FROM Z
> WHERE
> DATASPRZEDAZY BETWEEN '2005-02-01' AND '2005-2-28' AND
> ((Z.GODZINA BETWEEN 8 AND 22) OR Z.GODZINA=0))

339244 read and almost the same count of records

PLAN (Z INDEX (SPRZEDAZ_IDX_DATA))

Execution/Fetch time : 1888/34437 ms


Whe I try this query:

SELECT 1
FROM SPRZEDAZ Z
INNER JOIN POZSPRZEDAZ P
ON (P.IDSPR=Z.ID)
WHERE
(Z.DATASPRZEDAZY BETWEEN '2005-02-01' AND '2005-02-28') AND (Z.GODZINA
BETWEEN 8 AND 22 OR Z.GODZINA=0)

I have also addtional 590210 reads of POZSPRZEDAZ

Execution/Fetch time : 2836/163898 ms

I think now that's all because no aggregation of data and no
normalization on that tables. But I have no idea how should I do that.

> Depending on the results, you could separate P from Z.
>
> ok!
> I hope this help, and that this kind of tune won't be necessary in
> future versions of FB.
>
> Juan Jose.

Thank You.
Regards
Boguslaw Brandys