A better way (performance wise) could be:

if (ZU is null) then
begin
for
select KA, sum(ValNet)
from BU
where KG = :KeyValue
group by KA
into :KA, :SumNet
do
suspend;
end
else
begin
for
select KA, sum(ValNet)
from BU
where KG = :KeyValue and Zu = :ZU
group by KA
into :KA, :SumNet
do
suspend;
end;

The problem with your original approach, is that the plan is determined
at prepare time, whereas parameter values are applied later. If ZU has a
value, then the query would benefit from using an index, if not, no
index can be used. Using your approach forces Firebird to use no index
for ZU, and there's no general answer for the best answer to a question
like this: Sometimes it is OK just to accept the slowness (and use
queries like yours), sometimes it is better to split up the query as
I've indicated above, sometimes it may be a mix of these two approaches
(when there's more than one parameter involved), and sometimes setting
the query manually and use EXECUTE STATEMENT may be sensible.

As for KG, the index for this field should be used by both your original
query and my alternative. However, when an index is used for both WHERE
and GROUP BY (or ORDER BY), then Firebird 1.5 only shows the latest one
of these to be applied. I think (don't know) Firebird 2.1 shows both,
whereas I'm uncertain about Firebird 2.0. If KG is selective by itself,
you may have a good query already, even though the plan doesn't show up
as ideal.

HTH,
Set

Josef Gschwendtner wrote:

> Hi,
>
> we have the following query within a stored procedure.
> ZU is a input parameter which can be NULL.
> The meaning of NULL is --> don't pay attention to ZU
>
> KA, KG and ZU are foreignkey-fields and have an index.
> Table BU has about 100k records.
>
> This is the way we solve this:
>
> if (ZU is null) then ZU = -1;
> for
> select KA, sum(ValNet)
> from BU
> where KG = :KeyValue and (:ZU = -1 or Zu = :ZU)
> group by KA
> into :KA, :SumNet
> do
> suspend;
>
> This solution works but the plan is not optimal:
> PLAN (BU ORDER RDB$FOREIGN104) --> which is field KA --> just few
> distinct values
>
> Question:
> Is there a better way doing this?
> The goal is to force Firebird to use index KG and maybe also index ZU
>
> On the other hand IBExpert shows only 16 indexed reads (which is the
> number of KG not KA).
> Why this? Is index KG used without showing in my tool (IBExpert)?
>
> Could anybody enlighten me.
>
>
> Regards,
>
> Josef