--- In firebird-support@yahoogroups.com, "Richard Thomas"
<rthomas@...> wrote:

>
> Hi RB:
> I'm new to FireBird Database and just downloaded and installed 1.5.3.
> Over a minute?
> 2 Seconds?
> It sounds like you ran the tests on 2 diferent machines. I'm

wondering if

> that has anything to do with the huge improvement in speed.

While one can't rule out the performance implications of different
hardware, this sort of improvement is not usual for pure hardware or
configuration differences (unless you are jumping from a PII with 32
MB RAM to the most expensive machine you could buy today).

It is no secret that the Firebird 2 optimiser has been improved
significantly. In most queries, the Firebird 1.5 optimiser will choose
an appropriate plan (the best method for solving the query). There are
some cases where Firebird 1.5 will think that an index is useful, but
in the real world the cost estimation is wrong. This usually occurs on
foreign key indices where you have a table with a lot of records with
a foreign key declared to a table with few possible records. This
index has a lot of duplicates which is really slow to garbage collect
until the Firebird 2 index structure is used. It is also often quicker
to just read all data pages containing records in the table and knock
out the ones you are not interested in as you hit them than to read in
the appropriate ind(ex/ices) from disk then realise that 99% of the
data pages need to be read anyway.

I am not suggesting the measurement of this improvement is
particularly accurate, as caching and other hardware factors are also
in play, but even on the same machine and 'all else equal', I would
expect Firebird 2 to outperform Firebird 1.5 in some cases.

As Set has mentioned, most of the time Firebird 1.5 can be coaxed into
working quicker on a query where it has used a suboptimal plan by the
+0 trick (for numeric keys and dates) or the ||'' trick for varchars.

For example, consider the following query that gets the Site for each
employee.

select *
from employee e
join site s on (e.SiteID = s.ID)
where .....

It may happen that there is very few sites in the database, so chances
are the index on e.SiteID is not overly useful. Firebird may be
tricked into using the Site table first then joining across to the
employee table, but if you modify the query slightly,

select *
from employee e
join site s on (e.SiteID+0 = s.ID)
where .....

The optimiser will probably favour the employee table first because if
it went via the site table, it would have to do a natural read of the
employee table (which it doesn't like).

But it should also be noted that Firebird 2 is in release candidate
mode, so it is not considered stable for production use. You should
not assume that a database created under Firebird 2 RC1 will be usable
under Firebird 2 when it is finally released.

> > While converting SQL for a FB 1.5.3 to a FB 2.0 RC1 application I
> > timed three sequential select statements. One follows and the two
> > others are like it. They are used to report on done, pending and
> > overdue tasks that have been scheduled.
> >
> > select
> > USERS.USER_NAME,
> > USERS.USER_ID,
> > (select count(MATTER_EVENTS.MATTER_EVENT_ID)
> > from MATTER_EVENTS
> > join MATTERS on MATTER_EVENTS.MATTER_ID=MATTERS.MATTER_ID
> > where (MATTER_EVENTS.DATE_REQD is not null) and
> > (MATTERS.STATUS_ID=2) and
> > (MATTER_EVENTS.PERFORMER_ID=USERS.USER_ID) and
> > (MATTER_EVENTS.B_IS_ACTIVE=1) and
> > (MATTER_EVENTS.DATE_DONE is null) and
> > (cast(MATTER_EVENTS.DATE_REQD as DATE) >=
> > cast(current_date as DATE))) as theCount
> > from USERS

Do you actually NEED the count, or do you just want to see if such a
matter_events record exists for that user? If the latter, then this
query can be significantly sped up immediately.