Subject RE: [firebird-support] View takes longer than select, FB 1.5
Author Rick Debay
I'm using FB 1.5. Both queries are identical, having identical WHERE
and HAVING clauses.

-----Original Message-----
[] On Behalf Of Alexandre Benson
Sent: Wednesday, June 20, 2007 2:27 PM
Subject: Re: [firebird-support] View takes longer than select, FB 1.5

Rick Debay wrote:
> I ran a select statement that took 18.552 seconds to return 45 rows.
> A view created from the same select statement takes 669.411 seconds to

> retrieve 45 rows.
> When I prepare them they both return the same plan, except for one
> I ran the select statement first, so that should have loaded the cache

> and the view should have taken less time.
> The problem seems to be that the optimizer isn't applying the
> conditional to the select inside the view, instead it's generating a
> plan based on the view only. The select statement will generate the
> same plan, if it has no additional conditionals.

What FB Version ?

If FB 1.5, then there is a simple answer:
Views that uses aggregates on FB 1.5 move the WHERE clause to the HAVING
clause, so you will filter after all data has been scanned. FB 2.0 uses
the WHERE clause when possible, making it behaves exactly as it is a
simple select.

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil


Visit and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at


Yahoo! Groups Links

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.