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