Help Optimizing View

BWGSDIT 2016-02-11 19:51:18

This view is used extensively in the company and I need help refining it to be more efficient (less bloat).

Any advice would be appreciated.

/storage/attachments/1477-tmpCB45.queryanalysis

Thomas Franz 2016-02-12 11:01:29

– it would be useful, if you would provide the views source code (a select top 1000 from view does not help very much)
– 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

Thomas Franz 2016-02-12 14:02:20

PS: inline table value functions must not contain a begin / end or any declares / sets and are a single SELECT statement. You have to CROSS APPLY them in the FROM part of your query

BWGSDIT 2016-02-12 13:36:02

Thanks for the advice Franz,

I will get to work on your suggestions. In the meantime, I am adding the code here.link text

Thanks again

/storage/attachments/1478-tmpa671.queryanalysis

Thomas Franz 2016-02-12 13:59:54

Ok, thsi is a really ugly view (and uncomplete), that could not perform fast.

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

BWGSDIT 2016-02-17 21:02:11

Here is what I have so far:

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?

Matak 2016-02-17 22:09:17

Is that all the query ? Your last inner join to the subselect has no ON clause
ie you need something like QuantityONHand on a.col1 = b.col1

February 11, 2016

No Comments