Bsrgvty

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. There is an excellent Blogpost by Paul White on this. 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

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

With about 9 logical reads on my test

3: Writing the query without the use of a view.

The other 'solution' could be writing the query entirely without the use of a view.

4: Not keeping the ROW_NUMBER() function in the view, instead specifying it in the call to the view.

An example of this would be:

CREATE VIEW dbo.Bad2
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;

GO
SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT ROW_NUMBER() OVER (PARTITION BY CodeMasterID ORDER BY CodeMasterID) AS Deal_HistoryID,* FROM dbo.Bad2
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155;

There should be other creative ways around this issue, the important part is knowing what causes it.

Addendum #1

CREATE TABLE dbo.Codemaster 
( 
 CodeMasterID int NOT NULL PRIMARY KEY CLUSTERED 
 , ManagerID INT NULL 
 , ParentDeptID int NULL 
 , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL 
 , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 
 , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) 
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Codemaster_History)) 
; 

CREATE TABLE dbo.Deal 
( 
 DealID int NOT NULL PRIMARY KEY CLUSTERED 
 , CodeMasterID INT NULL 
 , EvenMoreBlaID int NULL 
 , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL 
 , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 
 , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) 
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Deal_History)) 
; 

INSERT INTO dbo.Codemaster(CodeMasterID,ManagerID,ParentDeptID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;


INSERT INTO dbo.Deal(DealID,CodeMasterID,EvenMoreBlaID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

CREATE INDEX IX_CodeMasterID
ON dbo.Deal(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Deal_History(CodeMasterId);

CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster_History(CodeMasterId);


SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.SysStartTime) AS Deal_HistoryID,
cm.*, d.* 
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm 
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID
Where cm.CodeMasterID = 37155;

-- Guud
GO
CREATE VIEW dbo.Bad
as
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.SysStartTime) AS Deal_HistoryID,
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm 
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID

GO
EXEC SP_EXECUTESQL
N'SELECT * FROM dbo.Bad
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155

-- Very bad shame on you

Addendum #2

CREATE TABLE dbo.Codemaster2
( 
 CodeMasterID int NOT NULL PRIMARY KEY CLUSTERED 
 , ManagerID INT NULL 
 , ParentDeptID int NULL 

); 

CREATE TABLE dbo.Deal2
( 
 DealID int NOT NULL PRIMARY KEY CLUSTERED 
 , CodeMasterID INT NULL 
 , EvenMoreBlaID int NULL 
); 

INSERT INTO dbo.Codemaster2(CodeMasterID,ManagerID,ParentDeptID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;


INSERT INTO dbo.Deal2(DealID,CodeMasterID,EvenMoreBlaID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

CREATE INDEX IX_CodeMasterID
ON dbo.Deal2(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster2(CodeMasterId);


SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterId) AS Deal_HistoryID,
cm.*, d.* 
FROM dbo.CodeMaster2 cm 
INNER JOIN dbo.Deal2 d ON cm.CodeMasterID = d.CodeMasterID
Where cm.CodeMasterID = 37155;

-- Guud
GO
CREATE VIEW dbo.Bad2
as
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

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