Hei Michael!

Your problem is that the optimizer thinks the debnr index is somewhat selective, but not more so than using the index for dato would be a beneficial if you check more than one debnr (after all, how many dates can be before the end of last month?). The quick-fix way is to change to "and t.dato+0<'2010-11-30';" A better and more permanent solution would be to upgrade to a Firebird version that support the use of histograms, though that might take a little while since it isn't programmed yet (I think it was scheduled for 3.0 and not part of 2.5?)

By the way, I expect this to only be part of your real SQL statement, since I see no good reason for including betalingsvej (well, unless you want to double what customers with two betalingsvej's pay).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of mivi71dk
Sent: 3. desember 2010 12:45
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SQL, Plan and why

Hello

I have this:

select
Sum (
(case when
(t.art IN (3,45))
then
(t.salgkr*-1)
else
(t.salgkr)
end)
) as StartSaldo
from transaktioner t
left join betalingsvej on (
betalingsvej.betvej_navn=t.betvej and
betalingsvej.AFDELING_ID=t.afdeling_ID and
betalingsvej.uafd_Navn=t.UAfd_Navn
and (betalingsvej.betvej_spec_betvej=5)
)
where
(t.debnr='1712' or t.debnr='1712 ') and
(t.art in (3,18,45) or ((art=23)))
and t.dato<'2010-11-30';


with this plan

PLAN JOIN (T INDEX (TRANS_DEBNR, TRANS_DEBNR, TRANS_DATO), BETALINGSVEJ INDEX (PK_BETALINGSVEJ))

Its some 2 seconds to execute.

If I chnage it to this (ommits an OR in the where clause)

select
Sum (
(case when
(t.art IN (3,45))
then
(t.salgkr*-1)
else
(t.salgkr)
end)
) as StartSaldo
from transaktioner t
left join betalingsvej on (
betalingsvej.betvej_navn=t.betvej and
betalingsvej.AFDELING_ID=t.afdeling_ID and
betalingsvej.uafd_Navn=t.UAfd_Navn
and (betalingsvej.betvej_spec_betvej=5)
)
where
(t.debnr='1712') and
(t.art in (3,18,45) or ((art=23)))
and t.dato<'2010-11-30';



I get this PLAN:

PLAN JOIN (T INDEX (TRANS_DEBNR), BETALINGSVEJ INDEX (PK_BETALINGSVEJ))

And instead of 2 seconds it takes 0.059 seconds to execute.

I our cause I have to use the first one. At least for now.
But I would like to use the last one.
The report we are doing is at the moment some 14 minutes to complete.
Could I get the first to be as fast as the second it would only take 25 seconds