November 20, 2017 at 7:27 am
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.
November 20, 2017 at 7:30 am
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 recoverability
November 20, 2017 at 7:46 am
GilaMonster - Monday, November 20, 2017 7:30 AMPlan 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.
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?
November 20, 2017 at 8:03 am
GilaMonster - Monday, November 20, 2017 7:30 AMPlan 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.
Aren't plan guides a new feature not available on 2012?
Plan guides are available in 2005+.
-- Itzik Ben-Gan 2001
November 20, 2017 at 8:09 am
Alan.B - Monday, November 20, 2017 8:03 AMPlan guides are available in 2005+.
Great! I'll have to learn something new today. 🙂
November 20, 2017 at 8:26 am
Luis Cazares - Monday, November 20, 2017 7:46 AMAm I attached to another object by an incline plane wrapped helically around an axis?
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 recoverability
November 20, 2017 at 9:12 am
GilaMonster - Monday, November 20, 2017 8:26 AMLuis Cazares - Monday, November 20, 2017 7:46 AMAm I attached to another object by an incline plane wrapped helically around an axis?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.
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.
November 20, 2017 at 9:14 am
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 recoverability
November 20, 2017 at 9:18 am
GilaMonster - Monday, November 20, 2017 9:14 AMNot queries. Just that one that has the problem.
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.
November 21, 2017 at 10:30 am
Luis Cazares - Monday, November 20, 2017 9:18 AMGilaMonster - Monday, November 20, 2017 9:14 AMNot queries. Just that one that has the problem.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.
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)
November 21, 2017 at 11:08 am
sgmunson - Tuesday, November 21, 2017 10:30 AMHow 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.
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.
November 21, 2017 at 8:53 pm
Put the IDENTITY column back on the table and call it a day. It wasn't as irrelevant as everyone thought.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2017 at 9:10 pm
Hmmm... I haven't tried it but perhaps a BIGINT Sequence would work here.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2017 at 9:22 pm
<removed>
November 21, 2017 at 9:55 pm
Luis Cazares - Monday, November 20, 2017 7:27 AMI'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.
I think I have it by doing a test on a different table. Name the individual columns instead of using "*"
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply