I agree, Michael, it is better to use BETWEEN (or >= and <=) than the 'hack' addition +0 (or ||''). Moreover, if I remember correctly, InterBase changed their optimizer so that in certain situations it understands that indexes can still be used with some of the ways I normally use to prevent the use of indexes - I don't remember if it was +0, ||'' or (OR <ConstantConditionAlwaysEvaluatingToFalse>), thus using 'intelligence' to optimize away manual optimization attempts (but, to be fair, it also makes it possible to use an index for B in situations like WHERE A = B+1). Your BETWEEN solution should work OK with both Firebird and InterBase.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of mivi71dk
Sent: 22. november 2010 15:23
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: SQL and PLAN

Well Set

Thank for this explanation.
I solved the problem by adding another line to the where clause as I stated earlier.

But the || '' also worked, but I like the other approach more.

As a matter of fact this report is now back to normal regarding this customer.
That will say it takes around 12 seconds, whereas it took some 50 minuttes this morning :)

Micahel

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> > I would prefer this any day.
> >
> > PLAN SORT (JOIN (TRANSAKTIONER INDEX (TRANS_DATO), VARER INDEX (RDB$PRIMARY26)))
> >
> > What seems strange to me is that the same Firebird server on different Databases chooses differently.
> > Or not - this must have to be with the Selectivity of the indices on TRANSAKTIONER?
>
> Sure it has to do with selectivity, the optimizer has (at least) two options that it considers to be about equally good.
>
> > Adding
> >
> > and transaktioner.Dato<='2010-12-01 23:59:59'
> >
> > to the where clauses also makes firebird pick what plan I like.
>
> Sure, as I said there are no histograms - the optimizer has no clue about what values this field contains. Adding that line makes the optimizer realize you're only interested in a very small range of dates (2 Nov - 1 Dec 2010 = 30 days), which the optimizer probably recognizes to be an awful lot more specific than any date between 2 Nov 2010 and infinity. In my work, I often have to hint the optimizer regarding dates, I know that we generally use dates from 1953 until today, but Fb 1.5 never understands that the index is useful if I specify a 1 year interval, but not if I use a 50 year interval. Without the histograms, there's no way for the optimizer to understand that.
>
> > INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR+0 = VARER.PLU_NR
> >
> > This actually did the job :)
> >
> > But - Doing a +0 on a varchar field. What happens if it contains anything but numbers?
> > My table should only contain numbers, but I can't be sure.
>
> Sorry, I thought it was a number, change to
>
> INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR||'' = VARER.PLU_NR
>
> >And - If my customer is not lying, this problem has comes forward the last few days, which means Firebird has started
> >to guess another way tan before.
>
> I don't think your customer is lying this time (after all, he doesn't state that he didn't touch anything). Selectivity changes with changes to tables, normally not enough to affect the choices done by the optimizer, but sometimes it is. Your customer was just a bit unfortunate, now it is up to you to fix.
>
> HTH,
> Set
>




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links