Another noteworthy consideration for views is that there is no such
"alter view" syntax that works with Firebird. So don't nest views
inside views, or it is a pain if you need to modify the internal view.

But as Alan said, most "simple" queries have an identical plan if used
in a view. As Alexandre said, there are exceptions.

Adam


--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@t...> wrote:

>
> Alan McDonald wrote:
>
> >>Hi all,
> >>
> >>between a query SQL and the same query used for create a view,
> >>about execution time, what's the faster ?
> >>
> >>Thanks all
> >> Alberto
> >>
> >>
> >>
> >
> >identical
> >Alan
> >
> >
> Just to point something...
>
> There are a few cases where the view will be slower.
>
> a select like this:
>
> select
> Sum(Total), CustomerID, CityID
> from
> SalesOrder
> where
> CityID = 1
> group by
> CustomerID, CityID
>
> and a view like this:
>
> Create View vTotalSales (Total, CustomerID, CityID) as
> select
> Sum(Total), CustomerID, CityID
> from
> SalesOrder
> group by
> CustomerID, CityID
>
> and then if you do a query like this:
>
> select * from vTotalSales where CityID = 1
>
> will be executed like this:
> select
> Sum(Total), CustomerID, CityID
> from
> SalesOrder
> group by
> CustomerID, CityID
> having
> CityID = 1
>
> In FB 1.5 and earlier the WHERE clause will be applied as a HAVING
> (since it could be on agregated columns), the HAVING clause is applied
> after the total result set be materialized, so will be much slower.
>
> In FB 2.0 it is better handled and when the where clause of the view

are

> not on an agregate colum it is applied to the WHERE clause, I have
> experiencied a 1000 times improve in some querys (from some seconds to
> miliseconds).
>
> see you !
>
> --
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br
>