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
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
