Hi !

Robert martin wrote:

>
> 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))
>

I expected some kind of grouping here, like:

SELECT i.ItemCode, itn.name, sum(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'
GROUP BY i.ItemCode, itn.name

So you could compare apples to apples.

> 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
>
>
>

A side note...
I think it should not give performance gain, but I think sl.RegionRef = 12621 to be a filter criteria and should be moved to the WHERE clause instead of being part of the JOIN condition. (maybe I am wrong)

But I would try these two queries:
SELECT i.ItemCode, itn.name, sum(sl.ShelfQty)
FROM Item i
JOIN Itemname itn On itn.ItemRef = i.ITEMREF
JOIN Stocklevel sl ON (sl.ItemRef = i.ItemRef)
WHERE i.ItemCode = 'A' and sl.RegionRef = 12621
GROUP BY i.ItemCode, itn.name



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)
WHERE i.ItemCode = 'A' AND sl.RegionRef = 12621



another option to force the item table to be the first scanned table (as in you direct table access) would be:

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

pay attention to the LEFT OUTER JOIN added ! The last "AND" is in place to mimic the INNER JOIN behavior, depending on your data structure it's not necessary.

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

In FB 2.0 it tries to move the filter criteria to the WHERE clause of
the underlining SQL of the VIEW, so I think the performance should be
quite similar, let's wait if someone points I am wrong.

From FB 2.0 the release notes page 62
"Distribute HAVING Conjunctions to the WHERE Clause
If a HAVING clause or any outer-level select refers to a field being
grouped by, this conjunct is distributed
deeper in the execution path than the grouping, thus allowing an index
scan to be used. In
other words, it allows the HAVING clause not only be treated as the
WHERE clause in this case, but
also be optimized the same way."

> Rob
>

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br