Bruno Marx wrote:

>
>
> 1- Firebird 2.1.1 server (Superserver)
> 2- The database has 2 tables:
>
> CREATE TABLE CallerID
> ( RecordId BIGINT NOT NULL,
> ...
> );
>
> CREATE TABLE Operation
> (
> RecordId BIGINT NOT NULL,
> ...
> );
>
> 3- Each table hosts 20,000 records exactly.

> This is my very slow SQL statement:
>
> SELECT Operation.RecordId,
> ...
> FROM CallerID RIGHT JOIN Operation
> ON CallerID.RecordId = Operation.RecordId
> WHERE Operation.LastOpId > 0
> AND Operation.LastOpType <> 2
>
> The query has a RIGHT JOIN and the result set contains 20,000 entries
> because a RecordId with the same value exists in both tables, and
> since Operation.LastOpId > 0 and Operation.LastOpType <> 2 is all
> cases . Now does it make sense this takes 7.5 minutes to execute?

Only if you have no indexes. You should declare a primary key for
each of your tables - some field that is always present and never
null. That will create an index on that field. You should also
create an index on any field in either table that will be use as
a join term, and, generally, any field that's used in a WHERE clause.

In this case, the WHERE clause is useless and just wastes time,
because it's true for every record in the table.

Beyond that, you should use an INNER join, not a RIGHT join. In
this case, it doesn't matter much, but in a three or four way join,
using RIGHT or LEFT restricts what the optimizer can do to improve
the performance of the query.

The rule is: use a LEFT join if you always want a row returned from
the LEFT (i.e. first) table even if there is no matching record from
the next table. Use a RIGHT join if you always want a row returned
from the RIGHT (i.e. second) table, even if there is no matching row
in the first table. Use a FULL join if you want all rows returned
from both tables, even those that aren't matched in the other table.
If you only want matched rows, use an INNER join.

FULL joins perform worst, LEFT and RIGHT are equivalent and perform
reasonable well in simple two-table joins, but can cause performance
problems in multi-way joins, and INNER joins perform best.


But first, create some indexes.

Good luck,

Ann



Consider these tables:

create table people (firstName varchar (20), spouse varchar(20));
create table boats (builder varchar (20), owner varchar(20));

People.firstName
Ann
Jean

Boats.builder Boats.owner
Hinckley Jim
Swiftsure Jean

select p.firstName, b.builder
from people p INNER join boats b on p.firstName = b.owner;

firstName builder
Jean Swiftsure

select p.firstName, b.builder
from people p LEFT OUTER join boats b on p.firstName = b.owner;

firstName builder
Ann <NULL>
Jean Swiftsure

select p.firstName, b.builder
from people p RIGHT OUTER join boats b on p.firstName = b.owner;

firstName builder
<NULL> Hinckley
Jean Swiftsure

select p.firstName, b.builder
from people p FULL OUTER join boats b on p.firstName = b.owner;

firstName builder
Ann <NULL>
Jean Swiftsure
<NULL> Hinckley