tomc7777777 wrote:

>
> The queries below use the new Expression indexes and the good news is
> that they, on their own, seem to work well. However:
>
> Example 1: Execute time=7s 140ms
> --------------------------------
> SELECT co.compname, c.surname, c.forename, c.title, s.siteid, c.clntid
> FROM Company co
> JOIN Site s ON (co.compid=s.compid)
> JOIN Client c ON (s.siteid=c.siteid)
> WHERE (UPPER(surname) STARTING WITH 'SMITH')
> ORDER BY UPPER(surname)
>
> PLAN SORT (JOIN (S NATURAL, CO INDEX (PK_COMPANY), C INDEX
> (IDXCLNTFULLNAME, FK1_CLIENT)))
>
> Example 2: Execute time=62ms, using +0 'trick'
> ----------------------------------------------
> SELECT co.compname, c.surname, c.forename, c.title, s.siteid, c.clntid
> FROM Company co
> JOIN Site s ON (co.compid=s.compid)
> JOIN Client c ON (s.siteid=c.siteid+0)
> WHERE (UPPER(surname) STARTING WITH 'SMITH')
> ORDER BY UPPER(surname)
>
> PLAN JOIN (C ORDER IDXCLNTFULLNAME INDEX (IDXCLNTFULLNAME), S INDEX
> (PK_SITE), CO INDEX (PK_COMPANY))

Execute time is a bad measure here. The former query has already read
all the records and sorted them, so you will be fetching them very fast.
The latter query had read nothing from disk, except maybe the first few
records, and subsequent fetches will be slower. So the question is: what
do you want to return faster: first N rows (to fill the screened part of
the grid) or all rows.

> Can anyone explain why the combining of two client indexes by FB in
> the first query i.e. C INDEX (IDXCLNTFULLNAME, FK1_CLIENT)) should
> cause such a performance hit please?

I believe it's not about indices, but about SORT vs ORDER plans instead.
See above.

> Also, why it doesn't choose the PK_SITE first off?

Because this index cannot be used in the chosen join order.

> This must be quite a problem as I had expected this to be fixed in 2.1

There's no such FB version as 2.1. Much depends on whether you're
actually running 2.1.0, 2.1.1 or 2.1.2.


Dmitry