Help Optimizing View

Question

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.

Answer 1

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

By Thomas Franz 16 answered Feb 12, 2016 at 11:01 AM

(comments are locked)

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

0

Answer 2

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

tmpa671.queryanalysis (1.9 MB)

By BWGSDIT 1 answered Feb 12, 2016 at 01:36 PM

(comments are locked)

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

Suggestions?

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

0

Help Optimizing View

Where to?

Follow this question

Related Questions