At 05:12 AM 27/11/2004 +0000, you wrote:

> > Do you get the same performance if you change from LIKE to STARTING?
>
>I've never used STARTING - is that a standard SQL keyword? Regardless,
>in the final code we will need to search on magic%, %magic, or %magic%
>(the last one is probably all we'll use).

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.

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.

> > Or, more
> > basic, exactly how are you selecting the records (which
> > program/components)???
>
>I do not understand your question - you mean in Java? If so, the tests
>described are executed via java.sql.Statement object, calling
>executeQuery and processing the returned ResultSet.

I think that's what he meant. Like me, Set is a Delphi programmer and an
IB Objects user. With IBO we can do things to optimise searches that
aren't so easy in the more generic interfaces.

> > Does Windows and Mac have the same memory
> > available?
>
>You mean available to Firebird? Both on windows and the MAC the
>settings are the default (since we haven't changed them). If you are
>talking of the physical memory on both machines, then that is
>something completely different and like comparing apples and oranges.

That was my thought, too. Not many of us have the opportunity to compare
Mac performance with Windows. Quite a few of us can and do compare Windows
and Linux performance, though, and Linux wins by a country mile. I've
always assumed that a Windows server simply eats too many resources just by
being there, whereas I can totally live without X and throw the server's
resources at the database engine.

Apples and oranges it might well be, but if your Mac system is starving the
database server of RAM then memory is an issue.

Did you do your tests with or without the Mac's GUI layer running? Is the
Mac controlling a lot of network services in addition to serving the
database services? And are you comparing Classic on the Mac with
Superserver on Windows.

> > > 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...
>
>No, the same number of records are read in each instance.

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.

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.

./heLen