September 8, 2017 at 1:54 am
ALZDBA - Wednesday, September 6, 2017 7:47 AMGrant Fritchey - Wednesday, September 6, 2017 7:34 AMALZDBA - Wednesday, September 6, 2017 7:22 AMEirikur Eiriksson - Wednesday, September 6, 2017 7:08 AM....
Suggest you try changing the view into a inline table valued function, the view query goes parallel, waisting lots of resources processing millions of rows whilst the other has a good cardinality estimation. The two plans are very different! Try using a maxdop 1 hint on the view query, you might be surprised 😉
😎
Shoot me now ! ( before I ever turn a view into a function )
You have to understand the difference between an inline tvf and a multi statement tvf, two totally different things, in that sense, a function != a function. What I'm suggesting is to incorporate the filter into an inline table valued function, although the cardinality estimation will be somewhat low, the results are going to be much better than what you are getting from the view.
😎
Can you post the execution plan for the changed view query?
Not that bad, it performs better than the view, but doesn't come near moving the windowing function to the final select
(2 row(s) affected)
Table 'ResourceDef'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContainerRoutingSteps'. Scan count 3, logical reads 14412, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzRoutingStep'. Scan count 3, logical reads 57420, physical reads 0, read-ahead reads 6731, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employee'. Scan count 3, logical reads 131, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Container'. Scan count 3, logical reads 21596, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzCoilComment'. Scan count 3, logical reads 5231, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 6938 ms, elapsed time = 3862 ms.
Hey Johan, I wouldn't expect much of a difference unless you wrote the iTVF such that you could call it like this:
Select *
from CuttingPlan.itvf_CoilComments(@p__linq__0)
ORDER BY executionsequence DESC
, creationdatelocal DESC
option (recompile) ;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 8, 2017 at 2:36 am
No input parameters have been defined for the itvf because of dynamic "where clauses needs" by the dev team
(otherwise I would have transformed it into a stored procedure in the first place)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply