Select * from View takes 4 minutes

I am running in to an issue where when I run a query against a view it takes 4+ minutes. However, when I run the guts of the query it finishes in like 1 second.

The only thing I am not sure about is the tables being joined are both temporal tables.

Ad hoc query plan:
https://www.brentozar.com/pastetheplan/?id=BykohB2p4

View query plan:
https://www.brentozar.com/pastetheplan/?id=SkIfTHh6E

Any suggestions on where to try and figure this out?

View code:

ALTER VIEW [dbo].[vwDealHistoryPITA]
AS
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.LastUpdateDate) AS Deal_HistoryID,
       cm.CodeMasterID,
       cm.ProjectName,
       cm.[Status],
       d.CompanyID,
       d.DealTypeMasterID,
       cm.[Description],
       d.PassiveInd,
       d.ApproxTPGOwnership,
       d.NumberBoardSeats,
       d.FollowonInvestmentInd,
       d.SocialImpactInd,
       d.EquityInd,
       d.DebtInd,
       d.RealEstateInd,
       d.TargetPctgReturn,
       d.ApproxTotalDealSize,
       cm.CurrencyCode,
       d.ConflictCheck,
       cm.CreatedDate,
       cm.CreatedBy,
       cm.LastUpdateDate,
       cm.LastUpdateBy,
       d.ExpensesExceedThresholdDate,
       d.CurrentTPGCheckSize,
       d.PreferredEquityInd,
       d.ConvertibleDebtInd,
       d.OtherRealAssetsInd,
       d.InitialTPGCheckSize,
       d.DirectLendingInd,
       cm.NameApproved,
       cm.FolderID,
       cm.CodaProcessedDateTime,
       cm.DeadDate,
       d.SectorMasterID,
       d.DTODataCompleteDate,
       cm.ValidFrom AS CodeMasterValidFrom,
       cm.ValidTo   AS CodeMasterValidTo,
       d.validFrom  AS DealValidFrom,
       d.validTo    AS DealValidTo
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm 
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID;
GO

Added the Partition by and get similar results to the ad hoc query.

The main performance differences

The main differences here are that the better performing query is pushing down the seek predicate on CodeMasterID on all 4 tables (2 temporal tables (actual & history)) where the select on the view appears to not do that until the end (filter operator).

TL DR;

The issue is due to to parameters not pushing down to window functions in certain cases such as views. The easiest solution is adding OPTION(RECOMPILE) to the view call as to make the optimizer 'see' the params at runtime if that is a possibility. If it is too expensive to recompile the execution plan for each query call, using an inline table valued function that expects a parameter could be a solution.

See The Problem with Window Functions and Views by Paul White.

For a more detailed way on finding and resolving your particular issue, keep on reading.

The better performing query

Codemaster table

Deal table

I love the smell of seek predicates in the morning

The big bad query

Codemaster table

This is a predicate only zone

The Deal table

But the optimizer did not read 'The art of the deal™"

...and does not learn from the past

Until all that data reaches the filter operator

So, what gives?

The main problem here is the optimizer not 'seeing' the parameters at runtime due to the window functions in the view and not being able to use the SelOnSeqPrj (select on sequence project, further down in this post for reference).

I was able to replicate the same results with a test sample and using SP_EXECUTESQL to parameterize the call to the view. See addendum for the DDL / DML

executing a query against a test view with a window function and an INNER JOIN

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155;

Resulting in about 4.5s of cpu time and 3.2s elapsed time

SQL Server Execution Times:
   CPU time = 4595 ms,  elapsed time = 3209 ms.

When we add the sweet embrace of OPTION(RECOMPILE)

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1 OPTION(RECOMPILE)',N'@P1 INT',@P1 = 37155;

It is all good.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 98 ms.

Why

This all again supports the point of not being able to apply the @P1 predicate to the tables because of the window function & parameterization resulting in the filter operator

Not only an issue for temporal tables

See addendum 2

Even when not using temporal tables, this happens:

The same result is seen when writing the query like this:

DECLARE @P1 int = 37155
SELECT * FROM  dbo.Bad2
Where CodeMasterID = @P1;

Again, the optimizer is not pushing down the predicate before applying the window function.

When omitting the ROW_NUMBER()

CREATE VIEW dbo.Bad3
as
SELECT
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm 
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID;

All is well

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad3
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 33 ms.

so where does all that leave us?

The ROW_NUMBER() is calculated before the filter is applied on the bad queries.

And all this leads us to this blogpost from 2013 by Paul White
on window functions and views.

One of the important parts for our example is this statement:

Unfortunately, the SelOnSeqPrj simplification rule only works when the
predicate performs a comparison with a constant. For that reason, the
following query produces the sub-optimal plan on SQL Server 2008 and
later:

DECLARE @ProductID INT = 878;
 
SELECT
    mrt.ProductID,
    mrt.TransactionID,
    mrt.ReferenceOrderID,
    mrt.TransactionDate,
    mrt.Quantity
FROM dbo.MostRecentTransactionsPerProduct AS mrt 
WHERE
    mrt.ProductID = @ProductID;

This part corresponds to what we have seen when declaring the parameter ourselves / using SP_EXECUTESQL on the view.

The actual solutions

1: OPTION(RECOMPILE)

We know that OPTION(RECOMPILE) to 'see' the value at runtime is a possibility. When recompiling the execution plan for each query call is too expensive, there are other solutions.

2: Inline table valued function with a parameter

CREATE FUNCTION dbo.BlaBla
(
    @P1 INT
)  
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    (
     SELECT 
     ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID) AS Deal_HistoryID,
     cm.CodeMasterID,CM.ManagerID,
     cm.ParentDeptID,d.DealID,
     d.CodeMasterID as dealcodemaster,
     d.EvenMoreBlaID
    FROM dbo.CodeMaster2  cm 
    INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID
    Where cm.CodeMasterID = @P1
    ) 
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.BlaBLa(@P1)',N'@P1 INT',@P1 = 37155

Resulting in the expected seek predicates

`

StackExchange Database Administrators Q#239370, answer score: 19