September 6, 2017 at 6:51 am
Engine: SQL Server 2008 R2 Service Pack 2 CU12 (I know, but I'm stuck with it)
When I copy the code in the view into the query, the select only needs 0sec elaps time.
Querying the view takes 20sec because it adds 2 worker tables !! ??
What am I missing?
Direct query (using the views code)
SQL Server parse and compile time:
CPU time = 47 ms, elapsed time = 62 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(2 row(s) affected)
Table 'ContainerRoutingSteps'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ResourceDef'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzRoutingStep'. Scan count 2, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employee'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzCoilComment'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Container'. Scan count 0, logical reads 7, 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 = 15 ms, elapsed time = 19 ms.
Query using the view
(2 row(s) affected)
Table 'Employee'. Scan count 5, logical reads 126, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ResourceDef'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContainerRoutingSteps'. Scan count 5, logical reads 12215, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzRoutingStep'. Scan count 5, logical reads 61114, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 60296, logical reads 1725938, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzCoilComment'. Scan count 5, logical reads 3419, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Container'. Scan count 0, logical reads 1705894, 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 = 54800 ms, elapsed time = 32546 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW [CuttingPlan].[V_CoilComments]
AS
SELECT insite.alzcoilcomment.alzcoilcommentid
, Coil.containername AS alzRoutingStep__RoutingStepsId__ContainerId_ContainerName
, Resource.resourcename AS alzRoutingStep__ResourceId_ResourceName
, insite.alzcoilcomment.executionsequence
, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY insite.alzcoilcomment.creationdatelocal ORDER BY alzcoilcomment.alzCoilCommentId ASC), insite.alzcoilcomment.creationdatelocal) AS creationdatelocal
, insite.alzcoilcomment.notes
, insite.employee.employeename
FROM insite.alzroutingstep AS RoutingStep
INNER JOIN insite.containerroutingsteps AS ContainerRoutingSteps
ON RoutingStep.alzroutingstepid = containerroutingsteps.routingstepsid
INNER JOIN insite.container AS Coil
ON RoutingStep.parentid = Coil.containerid
INNER JOIN insite.alzcoilcomment
ON insite.alzcoilcomment.parentid = Coil.containerid
AND RoutingStep.executionsequence = insite.alzcoilcomment.executionsequence
INNER JOIN insite.resourcedef AS Resource
ON Resource.resourceid = RoutingStep.resourceid
INNER JOIN insite.employee
ON insite.alzcoilcomment.employeeid = insite.employee.employeeid;
GO
DECLARE @p__linq__0 NVARCHAR(30);
SET @p__linq__0 = N'CK20164456886000000';
SELECT insite.alzcoilcomment.alzcoilcommentid
, Coil.containername AS alzRoutingStep__RoutingStepsId__ContainerId_ContainerName
, Resource.resourcename AS alzRoutingStep__ResourceId_ResourceName
, insite.alzcoilcomment.executionsequence
, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY insite.alzcoilcomment.creationdatelocal ORDER BY alzcoilcomment.alzCoilCommentId ASC), insite.alzcoilcomment.creationdatelocal) AS creationdatelocal
, insite.alzcoilcomment.notes
, insite.employee.employeename
FROM insite.alzroutingstep AS RoutingStep
INNER JOIN insite.containerroutingsteps AS ContainerRoutingSteps
ON RoutingStep.alzroutingstepid = containerroutingsteps.routingstepsid
INNER JOIN insite.container AS Coil
ON RoutingStep.parentid = Coil.containerid
INNER JOIN insite.alzcoilcomment
ON insite.alzcoilcomment.parentid = Coil.containerid
AND RoutingStep.executionsequence = insite.alzcoilcomment.executionsequence
INNER JOIN insite.resourcedef AS Resource
ON Resource.resourceid = RoutingStep.resourceid
INNER JOIN insite.employee
ON insite.alzcoilcomment.employeeid = insite.employee.employeeid
WHERE Coil.containername = @p__linq__0
ORDER BY insite.alzcoilcomment.executionsequence DESC
, creationdatelocal DESC ;
----using the view-----------------------------------
SELECT [V_CoilComments].[alzCoilCommentId] AS [alzCoilCommentId]
, [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] AS [alzRoutingStep__RoutingStepsId__ContainerId_ContainerName]
, [V_CoilComments].[alzRoutingStep__ResourceId_ResourceName] AS [alzRoutingStep__ResourceId_ResourceName]
, [V_CoilComments].[ExecutionSequence] AS [ExecutionSequence]
, [V_CoilComments].[CreationDateLocal] AS [CreationDateLocal]
, [V_CoilComments].[Notes] AS [Notes]
, [V_CoilComments].[EmployeeName] AS [EmployeeName]
FROM [CuttingPlan].[V_CoilComments] AS [V_CoilComments]
WHERE [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] = @p__linq__0
ORDER BY executionsequence DESC
, creationdatelocal DESC;
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
September 6, 2017 at 7:08 am
Primarely you missed posting the actual execution plans.
😎
Can you also post the DDL for the table involved?
September 6, 2017 at 7:22 am
Eirikur Eiriksson - Wednesday, September 6, 2017 7:08 AMPrimarely you missed posting the actual execution plans.
😎Can you also post the DDL for the table involved?
Ha, right #FacePalm
I'll edit the Q and add the sqlplan.
As it concerns view usage vs direct query without extras, I guess the actual table ddl in not needed.
The question is not how to optimize the query, the question is why is there this difference.
Bottom line the exact query should generate the same sqlplan. The windowing function is messing with me, but why?
Thanks
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
September 6, 2017 at 7:34 am
ALZDBA - Wednesday, September 6, 2017 7:22 AMEirikur Eiriksson - Wednesday, September 6, 2017 7:08 AMPrimarely you missed posting the actual execution plans.
😎Can you also post the DDL for the table involved?
Ha, right #FacePalm
I'll edit the Q and add the sqlplan.As it concerns view usage vs direct query without extras, I guess the actual table ddl in not needed.
The question is not how to optimize the query, the question is why is there this difference.
Bottom line the exact query should generate the same sqlplan. The windowing function is messing with me, but why?Thanks
It has to be the differences cause by the execution plan generation. It is a different process when dealing with a view than when simply dealing with a query. If nothing else, the placement of the WHERE clause and the variable can make a difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2017 at 7:47 am
Eirikur Eiriksson - Wednesday, September 6, 2017 7:08 AM
...
It has to be the differences cause by the execution plan generation. It is a different process when dealing with a view than when simply dealing with a query. If nothing else, the placement of the WHERE clause and the variable can make a difference.
and apparently it acts up the same way when using it in a CTE.----- using a CTE ---
With cteV_CoilComments as
( SELECT insite.alzcoilcomment.alzcoilcommentid
, Coil.containername AS alzRoutingStep__RoutingStepsId__ContainerId_ContainerName
, Resource.resourcename AS alzRoutingStep__ResourceId_ResourceName
, insite.alzcoilcomment.executionsequence
, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY insite.alzcoilcomment.creationdatelocal ORDER BY alzcoilcomment.alzCoilCommentId ASC), insite.alzcoilcomment.creationdatelocal) AS creationdatelocal
, insite.alzcoilcomment.notes
, insite.employee.employeename
FROM insite.alzroutingstep AS RoutingStep
INNER JOIN insite.containerroutingsteps AS ContainerRoutingSteps
ON RoutingStep.alzroutingstepid = containerroutingsteps.routingstepsid
INNER JOIN insite.container AS Coil
ON RoutingStep.parentid = Coil.containerid
INNER JOIN insite.alzcoilcomment
ON insite.alzcoilcomment.parentid = Coil.containerid
AND RoutingStep.executionsequence = insite.alzcoilcomment.executionsequence
INNER JOIN insite.resourcedef AS Resource
ON Resource.resourceid = RoutingStep.resourceid
INNER JOIN insite.employee
ON insite.alzcoilcomment.employeeid = insite.employee.employeeid
)
SELECT [V_CoilComments].[alzCoilCommentId] AS [alzCoilCommentId]
, [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] AS [alzRoutingStep__RoutingStepsId__ContainerId_ContainerName]
, [V_CoilComments].[alzRoutingStep__ResourceId_ResourceName] AS [alzRoutingStep__ResourceId_ResourceName]
, [V_CoilComments].[ExecutionSequence] AS [ExecutionSequence]
, [V_CoilComments].[CreationDateLocal] AS [CreationDateLocal]
, [V_CoilComments].[Notes] AS [Notes]
, [V_CoilComments].[EmployeeName] AS [EmployeeName]
from cteV_CoilComments as [V_CoilComments]
WHERE [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] = @p__linq__0
ORDER BY executionsequence DESC
, creationdatelocal DESC ;
generates the same execution plan as when using the view.
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
September 6, 2017 at 8:07 am
ALZDBA - Wednesday, September 6, 2017 7:22 AMEirikur Eiriksson - Wednesday, September 6, 2017 7:08 AM...
It has to be the differences cause by the execution plan generation. It is a different process when dealing with a view than when simply dealing with a query. If nothing else, the placement of the WHERE clause and the variable can make a difference.
and apparently it acts up the same way when using it in a CTE.----- using a CTE ---
With cteV_CoilComments as
( SELECT insite.alzcoilcomment.alzcoilcommentid
, Coil.containername AS alzRoutingStep__RoutingStepsId__ContainerId_ContainerName
, Resource.resourcename AS alzRoutingStep__ResourceId_ResourceName
, insite.alzcoilcomment.executionsequence
, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY insite.alzcoilcomment.creationdatelocal ORDER BY alzcoilcomment.alzCoilCommentId ASC), insite.alzcoilcomment.creationdatelocal) AS creationdatelocal
, insite.alzcoilcomment.notes
, insite.employee.employeename
FROM insite.alzroutingstep AS RoutingStep
INNER JOIN insite.containerroutingsteps AS ContainerRoutingSteps
ON RoutingStep.alzroutingstepid = containerroutingsteps.routingstepsid
INNER JOIN insite.container AS Coil
ON RoutingStep.parentid = Coil.containerid
INNER JOIN insite.alzcoilcomment
ON insite.alzcoilcomment.parentid = Coil.containerid
AND RoutingStep.executionsequence = insite.alzcoilcomment.executionsequence
INNER JOIN insite.resourcedef AS Resource
ON Resource.resourceid = RoutingStep.resourceid
INNER JOIN insite.employee
ON insite.alzcoilcomment.employeeid = insite.employee.employeeid
)
SELECT [V_CoilComments].[alzCoilCommentId] AS [alzCoilCommentId]
, [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] AS [alzRoutingStep__RoutingStepsId__ContainerId_ContainerName]
, [V_CoilComments].[alzRoutingStep__ResourceId_ResourceName] AS [alzRoutingStep__ResourceId_ResourceName]
, [V_CoilComments].[ExecutionSequence] AS [ExecutionSequence]
, [V_CoilComments].[CreationDateLocal] AS [CreationDateLocal]
, [V_CoilComments].[Notes] AS [Notes]
, [V_CoilComments].[EmployeeName] AS [EmployeeName]
from cteV_CoilComments as [V_CoilComments]
WHERE [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] = @p__linq__0
ORDER BY executionsequence DESC
, creationdatelocal DESC ;
generates the same execution plan as when using the view.
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 😉
😎
September 6, 2017 at 8:13 am
Eirikur 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 )
No, no, the solution is to put the windowing function in the final query in stead of in the view !
The hard part about that is bringing that message to the LinQ-using-programmers.
With cteV_CoilComments as
( SELECT insite.alzcoilcomment.alzcoilcommentid
, Coil.containername AS alzRoutingStep__RoutingStepsId__ContainerId_ContainerName
, Resource.resourcename AS alzRoutingStep__ResourceId_ResourceName
, insite.alzcoilcomment.executionsequence
/* , DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY insite.alzcoilcomment.creationdatelocal ORDER BY alzcoilcomment.alzCoilCommentId ASC), insite.alzcoilcomment.creationdatelocal) AS creationdatelocal */
, insite.alzcoilcomment.creationdatelocal
, insite.alzcoilcomment.notes
, insite.employee.employeename
FROM insite.alzroutingstep AS RoutingStep
INNER JOIN insite.containerroutingsteps AS ContainerRoutingSteps
ON RoutingStep.alzroutingstepid = containerroutingsteps.routingstepsid
INNER JOIN insite.container AS Coil
ON RoutingStep.parentid = Coil.containerid
INNER JOIN insite.alzcoilcomment
ON insite.alzcoilcomment.parentid = Coil.containerid
AND RoutingStep.executionsequence = insite.alzcoilcomment.executionsequence
INNER JOIN insite.resourcedef AS Resource
ON Resource.resourceid = RoutingStep.resourceid
INNER JOIN insite.employee
ON insite.alzcoilcomment.employeeid = insite.employee.employeeid
)
SELECT [V_CoilComments].[alzCoilCommentId] AS [alzCoilCommentId]
, [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] AS [alzRoutingStep__RoutingStepsId__ContainerId_ContainerName]
, [V_CoilComments].[alzRoutingStep__ResourceId_ResourceName] AS [alzRoutingStep__ResourceId_ResourceName]
, [V_CoilComments].[ExecutionSequence] AS [ExecutionSequence]
/*, [V_CoilComments].[CreationDateLocal] AS [CreationDateLocal]*/
, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY [V_CoilComments].[CreationDateLocal] ORDER BY [V_CoilComments].[alzCoilCommentId] ASC), [V_CoilComments].[CreationDateLocal]) AS creationdatelocal
, [V_CoilComments].[Notes] AS [Notes]
, [V_CoilComments].[EmployeeName] AS [EmployeeName]
from cteV_CoilComments as [V_CoilComments]
WHERE [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] = @p__linq__0
ORDER BY executionsequence DESC
, creationdatelocal DESC
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
September 6, 2017 at 8:23 am
ALZDBA - 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?
September 6, 2017 at 9:05 am
Hey Johan, the plain query shows an optimiser timeout - odd, because it's quicker than the plan which doesn't timeout. Here are a couple of variants of your CTE version, with the FROMlist tweaked somewhat. The first is to test if it generates the correct results. The second has a join hint in what I'd expect to be the correct place to force join order, which might be sufficient to a) eliminate the timeout and b) facilitate generation of a more appropriate plan. Wanna give 'em a whirl?
With cteV_CoilComments as (
SELECT insite.alzcoilcomment.alzcoilcommentid
, Coil.containername AS alzRoutingStep__RoutingStepsId__ContainerId_ContainerName
, Resource.resourcename AS alzRoutingStep__ResourceId_ResourceName
, insite.alzcoilcomment.executionsequence
, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY insite.alzcoilcomment.creationdatelocal ORDER BY alzcoilcomment.alzCoilCommentId ASC), insite.alzcoilcomment.creationdatelocal) AS creationdatelocal
, insite.alzcoilcomment.creationdatelocal
, insite.alzcoilcomment.notes
, insite.employee.employeename
FROM insite.container AS Coil
INNER JOIN insite.alzcoilcomment
ON insite.alzcoilcomment.parentid = Coil.containerid
INNER JOIN insite.alzroutingstep AS RoutingStep
ON RoutingStep.parentid = Coil.containerid
AND RoutingStep.executionsequence = insite.alzcoilcomment.executionsequence
INNER JOIN insite.containerroutingsteps AS ContainerRoutingSteps
ON RoutingStep.alzroutingstepid = containerroutingsteps.routingstepsid
INNER JOIN insite.resourcedef AS Resource
ON Resource.resourceid = RoutingStep.resourceid
INNER JOIN insite.employee
ON insite.alzcoilcomment.employeeid = insite.employee.employeeid
)
SELECT [V_CoilComments].[alzCoilCommentId] AS [alzCoilCommentId]
, [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] AS [alzRoutingStep__RoutingStepsId__ContainerId_ContainerName]
, [V_CoilComments].[alzRoutingStep__ResourceId_ResourceName] AS [alzRoutingStep__ResourceId_ResourceName]
, [V_CoilComments].[ExecutionSequence] AS [ExecutionSequence]
, [V_CoilComments].[CreationDateLocal] AS [CreationDateLocal]
--, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY [V_CoilComments].[CreationDateLocal] ORDER BY [V_CoilComments].[alzCoilCommentId] ASC), [V_CoilComments].[CreationDateLocal]) AS creationdatelocal
, [V_CoilComments].[Notes] AS [Notes]
, [V_CoilComments].[EmployeeName] AS [EmployeeName]
from cteV_CoilComments as [V_CoilComments]
WHERE [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] = @p__linq__0
ORDER BY executionsequence DESC
, creationdatelocal DESC
------------------------------------------------------------------------------------------------
With cteV_CoilComments as (
SELECT insite.alzcoilcomment.alzcoilcommentid
, Coil.containername AS alzRoutingStep__RoutingStepsId__ContainerId_ContainerName
, Resource.resourcename AS alzRoutingStep__ResourceId_ResourceName
, insite.alzcoilcomment.executionsequence
, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY insite.alzcoilcomment.creationdatelocal ORDER BY alzcoilcomment.alzCoilCommentId ASC), insite.alzcoilcomment.creationdatelocal) AS creationdatelocal
, insite.alzcoilcomment.creationdatelocal
, insite.alzcoilcomment.notes
, insite.employee.employeename
FROM insite.container AS Coil
INNER loop JOIN insite.alzcoilcomment
ON insite.alzcoilcomment.parentid = Coil.containerid
INNER JOIN insite.alzroutingstep AS RoutingStep
ON RoutingStep.parentid = Coil.containerid
AND RoutingStep.executionsequence = insite.alzcoilcomment.executionsequence
INNER JOIN insite.containerroutingsteps AS ContainerRoutingSteps
ON RoutingStep.alzroutingstepid = containerroutingsteps.routingstepsid
INNER JOIN insite.resourcedef AS Resource
ON Resource.resourceid = RoutingStep.resourceid
INNER JOIN insite.employee
ON insite.alzcoilcomment.employeeid = insite.employee.employeeid
)
SELECT [V_CoilComments].[alzCoilCommentId] AS [alzCoilCommentId]
, [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] AS [alzRoutingStep__RoutingStepsId__ContainerId_ContainerName]
, [V_CoilComments].[alzRoutingStep__ResourceId_ResourceName] AS [alzRoutingStep__ResourceId_ResourceName]
, [V_CoilComments].[ExecutionSequence] AS [ExecutionSequence]
, [V_CoilComments].[CreationDateLocal] AS [CreationDateLocal]
--, DATEADD(millisecond, -10 * ROW_NUMBER() OVER(PARTITION BY [V_CoilComments].[CreationDateLocal] ORDER BY [V_CoilComments].[alzCoilCommentId] ASC), [V_CoilComments].[CreationDateLocal]) AS creationdatelocal
, [V_CoilComments].[Notes] AS [Notes]
, [V_CoilComments].[EmployeeName] AS [EmployeeName]
from cteV_CoilComments as [V_CoilComments]
WHERE [V_CoilComments].[alzRoutingStep__RoutingStepsId__ContainerId_ContainerName] = @p__linq__0
ORDER BY executionsequence DESC
, creationdatelocal DESC
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 6, 2017 at 1:31 pm
Try adding an Option (RECOMPILE) to the code that calls the view. I've seen it help before (slightly different circumstance involving minimal logging that wouldn't minimally log without it) just because of the variable involved.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2017 at 1:14 am
ChrisM@Work - Wednesday, September 6, 2017 9:05 AM
The results:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Warning: The join order has been enforced because a local join hint is used.
SQL Server parse and compile time:
CPU time = 889 ms, elapsed time = 893 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(2 row(s) affected)
Table 'Employee'. Scan count 5, logical reads 131, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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 5, logical reads 15930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzRoutingStep'. Scan count 5, logical reads 68895, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 91433, logical reads 2618284, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Container'. Scan count 5, logical reads 20881, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzCoilComment'. Scan count 5, logical reads 5263, 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 = 49496 ms, elapsed time = 16457 ms.
(2 row(s) affected)
Table 'alzCoilComment'. Scan count 1479816, logical reads 4725141, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Container'. Scan count 5, logical reads 20566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'alzRoutingStep'. Scan count 5, logical reads 69520, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employee'. Scan count 0, logical reads 1114148, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ResourceDef'. Scan count 0, logical reads 1253662, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContainerRoutingSteps'. Scan count 363942, logical reads 1164061, physical reads 0, read-ahead reads 11, 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 '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 = 20030 ms, elapsed time = 5938 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
As you can see, both efforts do make a difference, but don't come near the performance having moved the windowing function to the final select clause.
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
September 7, 2017 at 1:16 am
Jeff Moden - Wednesday, September 6, 2017 1:31 PMTry adding an Option (RECOMPILE) to the code that calls the view. I've seen it help before (slightly different circumstance involving minimal logging that wouldn't minimally log without it) just because of the variable involved.
I did test the classical fast fixes Recompile, maxdop, fast n, ... using the view, none even came near the performance having moved the windowing function to the final select clause.
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
September 7, 2017 at 1:39 am
Grant 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?
Grant 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?
I'll try to test that later today ..
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
September 7, 2017 at 11:40 am
ALZDBA - Thursday, September 7, 2017 1:16 AMJeff Moden - Wednesday, September 6, 2017 1:31 PMTry adding an Option (RECOMPILE) to the code that calls the view. I've seen it help before (slightly different circumstance involving minimal logging that wouldn't minimally log without it) just because of the variable involved.I did test the classical fast fixes Recompile, maxdop, fast n, ... using the view, none even came near the performance having moved the windowing function to the final select clause.
I figured you'd be on top of such things. Thanks for the feedback, Johan.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2017 at 1:44 am
Grant 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.
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 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply