Help Optimizing View
Any advice would be appreciated.
– you are using scalar functions (fnIMRoundQtyWrk / fnGetUserBusinessDate) -> rewrite them as inline table value functions (should be possible for both) -> plan could go parallel
– consider to aggregate the timWhseBin before running the query. You read this table many times, so a temp table (or permanent table refreshed by triggers or regurlar executed procedures) may help to minimize the reads
I will get to work on your suggestions. In the meantime, I am adding the code here.link text
Thanks again
Beside of the points mentioned above:
NEVER use COALESCE, when one of the parameters is a subselect or a function call or everything that could be slow to get / calculated. COALESCE (and CASE) will run the subselect twice (e.g. SELECT COALESCE((SELECT COUNT(*) FROM bigtbl), 0). Replace it with ISNULL()
Instead of doing many subselects in the SELECT-Part of the main query you should try do only a single JOIN to a aggregated subselect oft the detail table(s).
So instead of
SELECT (SELECT SUM(f1) FROM tbl_a a where a.id = m.id and a.typ = 1), (SELECT SUM(f2) FROM tbl_a a where a.id = m.id and a.typ = 2), (SELECT SUM(f3) FROM tbl_a a where a.id = m.id and a.typ = 3) from maintbl m
write something as
SELECT sub.sum1, sub.sum2, sub.sum3 from maintbl m inner join (select sum(case when a.typ = 1 then f1 else 0 end) sum1, sum(case when a.typ = 2 then f2 else 0 end) sum2, sum(case when a.typ = 3 then f3 else 0 end) sum3 from tbl_a a ) sub
SELECT b.CompanyID, b.ItemID, a.ItemKey, d.WhseID, a.WhseKey, c.ShortDesc, e.UnitMeasID, b.StockUnitMeasKey, QuantityONHand.QtyOnHand
FROM dbo.timInventory AS a INNER JOIN dbo.timItem AS b ON a.ItemKey = b.ItemKey INNER JOIN dbo.timItemDescription AS c ON a.ItemKey = c.ItemKey INNER JOIN dbo.timWarehouse AS d ON a.WhseKey = d.WhseKey INNER JOIN dbo.tciUnitMeasure AS e ON b.StockUnitMeasKey = e.UnitMeasKey INNER JOIN dbo.timOptions AS f ON d.CompanyID = f.CompanyID INNER JOIN dbo.timItemUnitOfMeas AS IUOM ON IUOM.ItemKey = b.ItemKey AND IUOM.TargetUnitMeasKey = b.StockUnitMeasKey INNER JOIN dbo.tciOptions AS CIO ON CIO.CompanyID = b.CompanyID INNER JOIN (Select m.QtyOnHand AS QtyOnHand FROM timWhseBinInvt m JOIN timWhseBin n ON m.WhseBinKey = n.WhseBinKey JOIN timInventory a on m.ItemKey = a.ItemKey WHERE m.ItemKey = a.ItemKey AND n.WhseKey = a.WhseKey) QuantityONHand
I am getting a "An expression of non-boolean type specified…" error. I am pretty sure it is the 'AND' operator that is causing it but I am not certain how to solve it.
Suggestions?
ie you need something like QuantityONHand on a.col1 = b.col1