You should create primary keys on this :-)

CREATE TABLE CallerID (
RecordId BIGINT NOT NULL,
Name........,
primary key(RecordId)
)

This should be done for both tables ...

If it is not a 1:1 relation you should at least create an index on the
referencing field.




_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Bruno Marx
Sent: Samstag, 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






[Non-text portions of this message have been removed]