the +0 is great :) I keep forgetting this.
Thank you. --- In [email protected], 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 >
