Setting primary keys fixed the issue. Now the query takes a few
seconds as expected...

Thanks everyone who responded including Hugo, Ann, and Svein (Set)

Regards,

Bruno :)

On Sat, Nov 8, 2008 at 6:21 PM, HugoHiasl <hugohiasl@...> wrote:
> 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]
>
>