Hi Bruno!

For this query to perform decently, you need an index on CallerID.RecordId. Without such an index, all records have to be checked for every row in Operation, that should be 20000*20000 = 400 000 000 checks(*)! Other indexes (including primary keys) would be nice to have (although you would ideally want to avoid using an index for Operation.LastOpId, since all records match that criteria, such an index will slow down this particular query), but an index on CallerID.RecordId is the one that will speed up your query a lot.

This helps,
Set

(*) This is probably not the way things are done, just the way my simple mind always have thought it is done. The thing that is correct in thinking like this, is that having to scan two tables using NATURAL (i.e. no index), is that it is extremely slow if both tables have more than a handful of records.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Bruno Marx
Sent: 8. november 2008 23:57
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Very slow SELECT statement issue

I am writing a client application that performs a SELECT statement to
a Firebird 2.1.1 server. The problem is that the SELECT statement
takes extremely long to execute (7-8 minutes). So I ran the same
SELECT statement from the isql utility, and it took about 7.5 minutes
to execute. Therefore it appears that it is not the client application
that is wrong since it takes very long in both cases. I am wondering
if this is normal, or if this is a bug with Firebird? I was expecting
the SELECT statement to take a few seconds to execute, not a 7-8
minutes.

--------------------------------------------
Setup details:

1- Firebird 2.1.1 server (Superserver)
2- The database has 2 tables:

CREATE TABLE CallerID
(
RecordId BIGINT NOT NULL,
Name CHAR(16) NOT NULL,
Type CHAR(16) NOT NULL,
Status CHAR(25) NOT NULL,
Channel INTEGER NOT NULL
);

CREATE TABLE Operation
(
RecordId BIGINT NOT NULL,
LastOpId BIGINT NOT NULL,
LastOpType INTEGER NOT NULL,
LastOpTimestamp TIMESTAMP NOT NULL
);

3- Each table hosts 20,000 records exactly.
--------------------------------------------

This is my very slow SQL statement:

SELECT Operation.RecordId,
CallerID.Name,
CallerID.Type,
CallerID.Status,
CallerID.Channel,
Operation.LastOpId,
Operation.LastOpType
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?

My client application uses IBPP and with the debugger, I was able to
see that the call to isc_dsql_fetch() often takes up to 10 seconds
before it returns. This doesn't seem to make sense to me either.

Thanks for your help!

Bruno