Hi Phil, I'm answering simply because no-one else seems to have picked
it up...

--- In firebird-support@yahoogroups.com, "phil_hhn" wrote:
> Hi
> We're seeing terrible performance on the MAC (compared to the same
> query under windows), Eg a 0.6 sec query on windows taking approx 5
> seconds on the MAC (OSX).
> It occurred to us that it may be classic server, since we'd heard it
> was slower (classic server was the only recent binary available for
> the MAC). A workmate downloaded the 1.5.1 source today, compiled
> super-server for the MAC, and it does seem better, but probably only
> by 1 second.
>
> Our query is simple (two tables referenced by a cross-reference
> table):
>
> select a.title from TableA a join CrossRef x on x.pkA = a.pkA join
> TableB b on b.pkB = x.pkB where b.keyword like 'magic%'
>
> There are 18,000 keywords in TableB, and selecting keywords starting
> with 'magic' is fast both in windows and on the MAC. Similarly
> performing the above query but without the where clause is fast on
> both platforms. Using the entire query, it is still fast (0.6
> seconds) on windows, but on the MAC it's suddenly taking 5 seconds.
> That's a huge drop in performance.

Are you sure that 0.6 seconds versus 5 seconds is the time it takes to
transfer all records to the client and not just the first few so that
the screen gets filled (i.e. do you read to end-of-file)? Do you get
the same performance if you change from LIKE to STARTING? Or, more
basic, exactly how are you selecting the records (which
program/components)??? Does Windows and Mac have the same memory
available?

> Then this afternoon I found something else weird.
>
> I replaced "select a.title from TableA..." with "select * from
> TableA..." and the query only takes 1.5 - 2.0 seconds!! That's a
> huge improvement, but it doesn't make sense. How could selecting all
> columns from a table be faster than just selecting one of them?

I suppose this could make sense if the 5 seconds were the time
neccessary to transfer all records, whereas the 2 seconds were only
the first few records...

> These findings suggest to me that the query optimisation on the MAC
> has some issues.
> a) Is it just that the MAC build process is pulling in some outdated
> query optimisation code? There was no recent super-server binary for
> the MAC, so I was wondering how often it gets worked on.
> b) Is there an option to allow me to see the query plan that the
> database is using?
> c) Adding an index to TableB.keyword did not help and I gather with
> text searches involving wildcards that this is the case for most
> databases. I've been told however that SQL server supports a 'full
> text index' that makes this type of search much faster - does
> firebird support something similar?

I'm sorry to say that I know nothing about MACs. In Windows, I would
recommend you to run IB_SQL and read the plan, but I know nothing
about neither MAC nor isql (which I expect to be able to produce a
plan). In general LIKE does not use an index, whereas STARTING does
(the exception being LIKE <constant>, where the optimizer at the time
of prepare can see that the first few characters of the string is
known and as such can benefit from using an index).

Set