Hi Ed

Try using equal to sign = than 'in' since its clear that there is only one record as Max aggregate function returns on one record per group.In is to be used on a result set .

Good Luck

Niegil Thomas

----- Original Message -----
From: Svein Erling Tysvær
To: firebird-support@yahoogroups.com
Sent: Wednesday, 22 August, 2007 12:49 PM
Subject: Re: [firebird-support] Very slow SQL


Hi Ed!

Ed Dressel wrote:
> I have the following SQL statement:
>
> select * from ClientINfo
> where Client_Id in (Select Max(Client_ID) from ClientInfo)
> v
> where Client_ID is the primary key of ClientInfo.

Just adding to your options:

select * from ClientInfo c1
where not exists(select * from ClientInfo c2 where c2.Client_Id > c1.Client_ID)

> But the SQL above is very slow (almost 2 minutes). The plan is nautrual:
>
> PLAN (CLIENTINFO NATURAL)
> PLAN (CLIENTINFO NATURAL)

Two naturals means something like 'records in ClientInfo * records in ClientInfo'

> Oh yeah, FB 1.5
>
> And "Select Max(Client_ID) from ClientInfo" is fast.

This is only 'records in ClientInfo', i.e. much less work for the server.

What I think you haven't understood yet, is that your subselect (Select Max(Client_ID) from ClientInfo) is executed once for each record in ClientInfo, hence if the ClientInfo table contains 10000 records, this subselect is executed 10000 times. Since the plan is natural for both the main select and the subselect, it has to check every record 10000 times, i.e. a total of 100000000 records to look up.

Things will become a lot better with a descending index, but IN <subselect> still ought to be avoided in Firebird 1.5 (I think there were some important changes in this area between 1.5 and 2.0, and that some IN <subselect>s execute decently in Firebird 2.0).

HTH,
Set

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





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