Subject Re: [firebird-support] View takes longer than select, FB 1.5
Author Alexandre Benson Smith
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
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil