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.
Best Answer
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 / DMLexecuting a query against a test view with a window function and an
INNER JOIN
Resulting in about 4.5s of cpu time and 3.2s elapsed time
When we add the sweet embrace of
OPTION(RECOMPILE)
It is all good.
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 operatorNot 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:
Again, the optimizer is not pushing down the predicate before applying the window function.
When omitting the ROW_NUMBER()
All is well
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:
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
Resulting in the expected seek predicates
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:
There should be other creative ways around this issue, the important part is knowing what causes it.
Addendum #1
Addendum #2