🚀 go-pugleaf

=============================================================
Gerarld,

What you are experiencing is correct and could in all cases good
results. It is that you are comparing apples to oranges (or at least
Macintoshes to Golden Delicious')? It is worth checking.

SQL should normally be faster than a FILTER [or Filter( )]. SQL can use
Indexes to its advantage, while FILTER cannot. So SQL can internally do
something equivalent to the dBASE programmer's SET KEY TO x while the
FILTER must plough one by one through all the rows (records) to come up
with the correct choices. When no useful indexes exist for SQL to use,
then it too must go one by one and it's performance will be the same as
FILTER .

Yes, a JOIN is going to take longer. You are asking the computer to
match up all the rows (records) in one child table (not "database"
anymore :-) with your parent table and present the result as a new
single virtual table (rowset). This is a much bigger task than looking
up /one/ group of child rows that match a particular field in one row of
the parent. The WHERE clause can greatly help to limit the size of the
returned rowset and the speed at which it is calculated. But, there is a
string attached. That is the existence of Indexes in the target tables
that are useful for getting the job done. The SQL you submit is
interpreted (which is not always done perfectly) and it will do its best
to carry out your request. However, if for example your parent table is
indexed on the field used to make the join and the child table is not,
then SQL will have to resort to one-by-one searching.

The SQL interpretation is not always perfect and there can be bugs. If
you examine the Plan the SQL interpreter produces to see where it might
be going wrong, you can often find a way to reword your query to result
in a more effective Plan. I cannot yet comment on the quality of the SQL
interpretation done by VdB 7.01/BDE, but if it borrows from Interbase (a
Borland subsidiary) then it should be good.

If your testing really is an apples-to-apples comparison (i.e. the SQL
with WHERE clause is identical to a old style SET KEY TO or old style
LOOKUP) then you should point the specific case out to Borland so they
can tune their SQL interpretation. In a real apples-to-apples scenario,
the differences should be neglible.



Geoff Wass
Kuala Lumpur, Malaysia




Triumph is in the first syllable.
=============================================================