I'm using FB 1.5. Both queries are identical, having identical WHERE
and HAVING clauses.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Alexandre Benson
Smith
Sent: Wednesday, June 20, 2007 2:27 PM
To: firebird-support@yahoogroups.com
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

item.

> 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
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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.