huge diffence direct query vs query view

  • 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

  • Primarely you missed posting the actual execution plans.
    😎

    Can you also post the DDL for the table involved?

  • Eirikur Eiriksson - Wednesday, September 6, 2017 7:08 AM

    Primarely 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

  • ALZDBA - Wednesday, September 6, 2017 7:22 AM

    Eirikur Eiriksson - Wednesday, September 6, 2017 7:08 AM

    Primarely 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

  • Grant Fritchey - Wednesday, September 6, 2017 7:34 AM

    ALZDBA - Wednesday, September 6, 2017 7:22 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

  • ALZDBA - Wednesday, September 6, 2017 7:47 AM

    Grant Fritchey - Wednesday, September 6, 2017 7:34 AM

    ALZDBA - Wednesday, September 6, 2017 7:22 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.

    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 πŸ˜‰
    😎

  • Eirikur Eiriksson - Wednesday, September 6, 2017 8:07 AM

    ALZDBA - Wednesday, September 6, 2017 7:47 AM

    Grant Fritchey - Wednesday, September 6, 2017 7:34 AM

    ALZDBA - Wednesday, September 6, 2017 7:22 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

  • ALZDBA - Wednesday, September 6, 2017 8:13 AM

    Eirikur Eiriksson - Wednesday, September 6, 2017 8:07 AM

    ALZDBA - Wednesday, September 6, 2017 7:47 AM

    Grant Fritchey - Wednesday, September 6, 2017 7:34 AM

    ALZDBA - Wednesday, September 6, 2017 7:22 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 )

    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?

  • 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

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • Jeff Moden - Wednesday, September 6, 2017 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.

    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

  • Eirikur Eiriksson - Wednesday, September 6, 2017 8:23 AM

    ALZDBA - Wednesday, September 6, 2017 8:13 AM

    Eirikur Eiriksson - Wednesday, September 6, 2017 8:07 AM

    ALZDBA - Wednesday, September 6, 2017 7:47 AM

    Grant Fritchey - Wednesday, September 6, 2017 7:34 AM

    ALZDBA - Wednesday, September 6, 2017 7:22 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 )

    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?

    Eirikur Eiriksson - Wednesday, September 6, 2017 8:23 AM

    ALZDBA - Wednesday, September 6, 2017 8:13 AM

    Eirikur Eiriksson - Wednesday, September 6, 2017 8:07 AM

    ALZDBA - Wednesday, September 6, 2017 7:47 AM

    Grant Fritchey - Wednesday, September 6, 2017 7:34 AM

    ALZDBA - Wednesday, September 6, 2017 7:22 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 )

    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

  • ALZDBA - Thursday, September 7, 2017 1:16 AM

    Jeff Moden - Wednesday, September 6, 2017 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.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eirikur Eiriksson - Wednesday, September 6, 2017 8:23 AM

    ALZDBA - Wednesday, September 6, 2017 8:13 AM

    Eirikur Eiriksson - Wednesday, September 6, 2017 8:07 AM

    ALZDBA - Wednesday, September 6, 2017 7:47 AM

    Grant Fritchey - Wednesday, September 6, 2017 7:34 AM

    ALZDBA - Wednesday, September 6, 2017 7:22 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 )

    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