Help Optimizing View

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.

avatar image By BWGSDIT 1 asked Feb 11, 2016 at 07:51 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first
  • 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

avatar image By Thomas Franz 16 answered Feb 12, 2016 at 11:01 AM
more ▼
(comments are locked)
avatar image Thomas Franz Feb 12, 2016 at 02:02 PM

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

10|10000 characters needed characters left

Thanks for the advice Franz,

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

Thanks again

avatar image By BWGSDIT 1 answered Feb 12, 2016 at 01:36 PM
more ▼
(comments are locked)
avatar image Thomas Franz Feb 12, 2016 at 01:59 PM

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 = and a.typ = 1),
        (SELECT SUM(f2) FROM tbl_a a where = and a.typ = 2),
        (SELECT SUM(f3) FROM tbl_a a where = 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
avatar image BWGSDIT Feb 17, 2016 at 09:02 PM

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.


avatar image Matak Feb 17, 2016 at 10:09 PM

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

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Feb 11, 2016 at 07:51 PM

Seen: 423 times

Last Updated: Feb 17, 2016 at 10:09 PM