Hi,

At July 15, 2006, 1:16 PM, Rafael Szuminski wrote:

>> Select * from Bills
>> inner join Accounts on BillAccount = AccountID
>>
>> is faster than
>>
>> Select * from Bills
>> where BillAccount in (select AccountID from Accounts where BillAccount
>> = AccountID)

> This is one area where FB is quite um well....pathetic when compared
> to other db's. Once the subselect returns more then a few thousand
> rows, all performance goes to hell. Perhaps instead of adding new
> update syntax, it would help to increase the performance of subselect
> queries and it would solve two issues?

The performance of a subquery can be improved by caching the result
set and using that result set for every rows. But, that would only be
feasible if the subquery doesn't have a direct relation with a table
in the FROM clause, and if the transaction is either consistency or
concurrent. It wouldn't work for read committed transaction, because
the subquery would have to be able to return any newly committed data
as the rows are retrieved, hence the subquery would have to be
executed for every row of the query/DML.

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Consultants Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)