the +0 is great :)

I keep forgetting this.

Thank you.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Hi Michael!
>
> >I have a table containing some 33.879.139 records.
> >
> >If I do a simple select like this:
> >
> >
> >Select Dato from Transaktioner
> >Where
> > VareFrvStrNr='090179'
> > AND Art=11
> > And Transaktioner.Afdeling_ID<>'99999'
> >
> >it fecthes 11 records within 0.3 seconds.
> >It will use the index on VareFrvStrNr according to the plan.
> >PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))
> >
> >If I instead uses this SQL
> >
> >Select Min(Dato)As MinimuSTG from Transaktioner
> >Where
> > VareFrvStrNr='090179'
> > AND Art=11
> > And Transaktioner.Afdeling_ID<>'99999'
> >
> >It takes at least 3 seconds. Often more.
> >Now the plan is like this:
> >PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))
> >
> >The TRANS_DATO index has almost no duplicates.
> >The TRANS_VAREFRVSTRNR can have some dublicates.
> >
> >Why is there such a big difference is the performance?
>
> I don't know, though I guess 090179 isn't the VareFrvStrNr with the highest number of duplicates and since Firebird doesn't (yet) use histograms (I think), the optimizer doesn't know that using TRANS_DATO will slow down this query. If you used a common VareFrvStrNr (in this sense, NULL IS a value :o), then I guess the slow query might be quicker than the quick one to fetch all rows.
>
> >can I do anything to optimize this?
>
> Well, I would guess a variant of the old +0 trick would help:
>
> Select Min(Dato+0) As MinimuSTG from Transaktioner Where ...
>
> HTH,
> Set
>