>
> My usual queries are something like
>
> SELECT ENTRYPROD.*, CLASS, MARCA, MODEL
> FROM ENTRYPROD
> LEFT JOIN COMPMODEL ON COMP=MODELID
> LEFT JOIN COMPMARCA ON MARCANO=MARCAID
> LEFT JOIN COMPCLASS ON CLASSNO=CLASSID
>

Here you force your plan to run through the tables in the following
order. EntryProd, CompModel, CompMarca, CompClass. This query will
also include any records in EntryProd where no matching record exists
in CompModel

> This query seems the logical one, but for the sake of testing I was
> doing something like
>
> SELECT ENTRYPROD.*, CLASS, MARCA, MODEL
> FROM ENTRYPROD
> JOIN COMPMODEL ON COMP=MODELID
> JOIN COMPMARCA ON MARCANO=MARCAID
> JOIN COMPCLASS ON CLASSNO=CLASSID
>
> which return the same amount of records (the actual tables have
> thousands of records).

In this case, you give Firebird the choice of whichever table it wants
to run through first. This query will NOT include any records in
EntryProd where no matching record exists in CompModel etc. In your
actual data, this may not be an issue, but there IS a difference in
the resulting data.

> From the test I made, the query execution is fairly the same,

I disagree!

The execution time is (apparently) twice as quick when you use the
inner join. The prepare time is the time it takes to decide how the
query will be executed. Now your left join forced the join order to be
decided, so it had a lot less to decide, hence it would be quicker to
prepare the left join query.

But once the plan had been calculated the inner join leaves the left
join at the starting line!

> however
> the JOIN makes faster fetches.
>
> LEFT JOIN (48662 records)
> -------------
> Prepare time = 0ms
> Execute time = 20ms
> Avg fetch time = 0.95 ms
> Current memory = 2,746,816
> Max memory = 2,846,608
> Memory buffers = 10,240
> Reads from disk to cache = 0
> Writes from cache to disk = 0
> Fetches from cache = 6,215
>
>
> JOIN (48662 records)
> -------------------
> Prepare time = 10ms
> Execute time = 10ms
> Avg fetch time = 0.48 ms
> Current memory = 2,746,476
> Max memory = 2,846,608
> Memory buffers = 10,240
> Reads from disk to cache = 0
> Writes from cache to disk = 0
> Fetches from cache = 6,215
>
>
> What is your opinion about these?

There are no reads or writes to disk, which tells me this query is
using cached data. Expect variations in single tests such as this.
Some other program may have kicked in at an unfortunate time for the
slower query, so you really need to perform the tests several times to
be sure you are not seeing something unrelated skewing the results.

It would be interesting to see the plan for both. Most FB/IB tools
will tell you the plan, even iSQL will tell you if you use the Set
Plan; command.

Remember, the more logical join is the join which deals with the
records with no matching records in the other table in the way you
require it. They are either excluded (join) or they are included from
one table but not the other (left join / right join) or they are
included in both tables (full outer join). Which join to use is NOT a
question of performance, but of resultsets.

Adam