Force execution plan in a view
-
I'm having an issue right now. We removed an identity column that is mostly irrelevant to a table. However, this identity is used by grids on the UI. To replicate it, I used a ROW_NUMBER to create an ID on the fly for the grid to use.
The view is something like this:
CREATE VIEW vwCustomData
AS
SELECT
CustomData = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,PVID = cd.PVID
,VarcharValue = cd.VarcharValue
,NumericValue = cd.NumericValue
,MoneyValue = cd.MoneyValue
,DateValue = ISNULL(cd.DateValue, CONVERT(DATE, '01-01-1900', 110))
,CustomDataLabel = cdl.CustomDataLabel
,CustomDataDisplay = cdl.CustomDataDisplay
,DataClass = cdc.DataClass
FROM dbo.tblCustomData AS cd(NOLOCK)
LEFT OUTER JOIN dbo.tblCustomDataLabels AS cdl(NOLOCK) ON cdl.CustomDataLabelID = cd.CustomDataLabelID
LEFT OUTER JOIN dbo.tblCustomDataClass AS cdc(NOLOCK) ON cdc.DataClassID = cdl.DataElementClassID
And it's usually called like this:
SELECT * FROM vwCustomData WHERE PVID = @ID;
The problem is that the ROW_NUMBER is being generated before filtering by PVID. Is there anyway to make it filter before generating the row numbers? I get that behavior if I use the query instead of the view.
The developer is not willing to change the usage of the view because it would might introduce problems if they miss one spot. They also don't like the degradation of performance (from >1 milliseconds to 2-3 seconds).
Any ideas?
The main table has several millions of rows. -
Plan guide for the query, if you want to use a hint. Otherwise, make sure there's useful indexes that can be used for that predicate.
You can't force an exec plan for a view, because a view doesn't have an execution plan. The query that uses the view does.Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Monday, November 20, 2017 7:30 AM
Aren't plan guides a new feature not available on 2012? Maybe I'm confused.
Indexes are in place and work perfectly when running the query instead of the view. The developers don't want to change anything that calls the view.Am I attached to another object by an incline plane wrapped helically around an axis?
- Luis Cazares - Monday, November 20, 2017 7:46 AM
Plan guides are available in 2005+.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- Alan.B - Monday, November 20, 2017 8:03 AM
Great! I'll have to learn something new today.
- Luis Cazares - Monday, November 20, 2017 7:46 AM
No, but to use a plan guide you need a hint that gets you the desired behaviour.
Any chance you can change the query that calls the view to not call the view? Should be easier to tune that way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Monday, November 20, 2017 8:26 AM
For the moment, no. I might be able to do it if no other option is available. Developers just don't want to do additional work to change the queries that call the view. That means more testing and more probabilities of introducing new defects.
-
Not queries. Just that one that has the problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Monday, November 20, 2017 9:14 AM
That one is repeated in multiple places along the system. I want to high five (in the face) the fool that didn't make it part of a stored procedure.
This view might also be used in multiple dynamic statements that define the tables/views used depending on the columns that need to be returned. I'm just trying to keep sane and fix this. - Luis Cazares - Monday, November 20, 2017 9:18 AM
How about replacing the view with an inline table-valued function? You might still need to get the SELECT of the ROW_NUMBER to operate as an outer query around the selected data based on the input value, but seems like that might still perform better than the 2 - 3 seconds problem you otherwise get. After all, you select from a view that has 2 to 3 million rows and then sequence it, and then filter it, that's not a good order. Only way I can see to do this is with an inline table-valued function.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, November 21, 2017 10:30 AM
I did this. The lead developer didn't like the idea of changing the code that calls the view. It effectively removes the problem, but I can't use that.
If I could get the option to filter before sequencing it but still using the view, that would be awesome. But I can't find a way to do it. -
Put the IDENTITY column back on the table and call it a day. It wasn't as irrelevant as everyone thought.
--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) -
Hmmm... I haven't tried it but perhaps a BIGINT Sequence would work here.
--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) -
<removed>
- Luis Cazares - Monday, November 20, 2017 7:27 AM
I think I have it by doing a test on a different table. Name the individual columns instead of using "*"
--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)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply