Alexandre Benson Smith wrote:

> What version of FB ?
>
> What the code did you use in both (view and table direct access) ?
>
> What the plans for both cases ?
>
> Could it be a cache issue ?
>
> see you !
>
>

Hi

Hope this answers your questions

FB 2.0.1

View


CREATE VIEW vwStocklevel (ItemRef, RegionRef, NumBatches, NumPieces,
ShelfQty, HeldQty, CustOrdQty, RequirdQty, SupOrdrQty, SupPslipQty,
LaybyQty, ConsignQty, StockTakeQ, ShippedQty, JobQty, ParkedQty) AS
SELECT ItemRef, RegionRef, COUNT(BatchRef), COUNT(PieceRef),
SUM(ShelfQty), SUM(HeldQty), SUM(CustOrdQty), SUM(RequirdQty),
SUM(SupOrdrQty), SUM(SupPslipQty), SUM(LaybyQty), SUM(ConsignQty),
SUM(StockTakeQ), SUM(ShippedQty), SUM(JobQty), SUM(ParkedQty)
FROM StockLevel
GROUP BY ItemRef, RegionRef


Test SQL (direct)

SELECT i.ItemCode, itn.name, sl.ShelfQty
FROM Item i
JOIN Itemname itn On itn.ItemRef = i.ITEMREF
JOIN Stocklevel sl ON (sl.ItemRef = i.ItemRef AND sl.RegionRef = 12621)
WHERE i.ItemCode = 'A'

PLAN JOIN (I INDEX (ITEM_ITEMCODE), SL INDEX (STOCKLEVEL_REGIONITEMREF),
ITN INDEX (ITEMNAME_ITEMREF))

Time 0.015

Test SQL view

SELECT i.ItemCode, itn.name, sl.ShelfQty
FROM Item i
JOIN Itemname itn On itn.ItemRef = i.ITEMREF
JOIN vwStocklevel sl ON (sl.ItemRef = i.ItemRef AND sl.RegionRef = 12621)
WHERE i.ItemCode = 'A'

PLAN JOIN (SORT (SL STOCKLEVEL INDEX (STOCKLEVEL_REGIONITEMREF)), JOIN
(I INDEX (ITEM_ITEMREF), ITN INDEX (ITEMNAME_ITEMREF)))

0.6s


It makes sense to me as the view needs to group all records before it
can subset down to the requested record.

Rob