--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> LIKE is a dog, as it can't use an index. In v.1.5, the optimizer will
> change LIKE 'anything%' to STARTING WITH, in any case. STARTING

WITH does

> use an index.

Yes, that's what I understood about LIKE. But from what you describe,
because we're using 1.5.1 (and have an index on that column), if the
optimiser has replaced it with STARTING WITH, then I guess we're stuffed.

> If you are going to do like '%magic%' then use CONTAINING instead. It
> doesn't use an index, but it is case-sensitive and (I think) smarter

than

> the wildcard search. Confine LIKE to conditions where you genuinely

need

> the search to be wildcard.

ok, but the search (not mentioned earlier) does need to ignore case,
so what is the overhead with specifying "... where upper(mycolumn)
containing 'MAGIC' " ?

> Apples and oranges it might well be, but if your Mac system is

starving the

> database server of RAM then memory is an issue.

It should not be - the MACs are not heavily loaded.... but to be
honest they are not my forte

> Did you do your tests with or without the Mac's GUI layer running?

With it running. The deployment of this application may be on any
scale - From a site with a dedicated server (linux, mac, windows) to a
small site where the app & db are all on a desktop machine (anything
capable of running the java application). The small installation site
would also reflect a smaller database and less demand on the database.

> Is the Mac controlling a lot of network services in addition to

serving the

> database services?

No, they are just plain desktop machines.

> And are you comparing Classic on the Mac with Superserver on Windows.

We are using Superserver on the MAC (one of my colleagues downloaded
the 1.5.1 source & compiled it). Superserver is faster (that 5 seconds
mentioned earlier was when we had classic on the MACs) - seems
superserver is about 1.0-1.5 seconds faster.

> The point Set is making is that, when you use a Firebird GUI tool on
> Windows, it's almost always IBO or FIBPlus sitting in the data access
> layer. They generally fetch the first n records into a buffer and

display

> them in your GUI, so fetching appears faster - the query has only

returned

> n records so far. While you are looking at this first batch of

records,

> the access layer is standing ready to ask for more if the

application needs

> them; otherwise those records stay buffered on the server.

Yes I realise that tools don't fetch all records at the start (with
large amounts of data this can also be observed when trying to skip to
the bottom of the results). So therefore all my measurements where
taken by running a Java program connecting via JDBC; timing was noted
after doing the query, then after reading all results.

> With isql, on the other hand, the application asks for all of the
> records. The time from when the first record is fetched to when the

last

> is fetched is your query time. So don't compare a GUI tool on

Windows with

> isql on the Mac...because you really are looking at apples and

oranges then.

We haven't tried using isql on the MAC - all our tests have been with
a java client connecting to the Db and reading all results. This will
be our final scenario.
I guess the only value in trying isql in the MAC will be to rule out
any weird JDBC-MAC interaction issues. But I don't expect this to be
the case because running IBexpert and querying a database on the MAC
also exhibits the same delays.

Cheers