rewrite a query

Question

Post reply

rewrite a query

Hadrian

Hall of Fame

Points: 3742
More actions
April 29, 2016 at 2:49 am

#326053

Hello Guys,
I have this view and I wish to create indexed view wuth one index on it ,but because use a correlated query inside I cannot. May you help me rewrite the query to avoid derived table?
CREATE VIEW [dbo].[vSde] AS
SELECT
a.[dMeasureId] AS [dMeasureId]
, a.[TimeUnit] AS [TimeUnit]
, a.[GeoLevel] AS [GeoLevel]
, a.[dTimeId] AS [dTimeId]
, a.[dGeoId] AS [dGeoId]
, b.[dGeoMnemonic] AS [dGeoMnemonic]
, b.[Shape_PLG] AS [Shape]
, a.[fResultId] AS [UID]
, a.[YValue] AS [NumValue]
, a.[XLabel] AS [TxtValue]
, a.[XValue] AS [XValue]
, a.[XLabel] AS [XLabel]
, a.[YValue] AS [YValue]
, a.[YLabel] AS [YLabel]
, a.[ZValue] AS [ZValue]
, a.[ZLabel] AS [ZLabel]
, c.[dTimeMnemonic] AS [dTimeMnemonic]
, c.[dTimeMnemonicFirstDay] AS [DateFirstDay]
, c.[dTimeMnemonicLastDay] AS [DateLastDay]
FROM
[dbd].[fResult] a
INNER JOIN
[ref].[fGeometry] b
ON
a.[dGeoId] = b.[dGeoId]
INNER JOIN
[ref].[dTime] c
ON
a.[dTimeId] = c.[dTimeId]
INNER JOIN
[dbd].[dMeasure] d
ON
a.[dMeasureId] = d.[dMeasureId]
INNER JOIN
[dbd].[dSubDataset] e
ON
d.[dSubdatasetId] = e.[dSubdatasetId]
INNER JOIN
(
SELECT
[dSubDatasetId]
, [VisibilityStatus]
, ROW_NUMBER() OVER (PARTITION BY [dSubDatasetId] ORDER BY [ModifiedDatetime] DESC) AS [RowNumber]
FROM
[mgt].[SubDatasetVisibility]
) f
ON
e.[dSubdatasetId] = f.[dSubdatasetId]
WHERE
a.[TimeUnit] = 'M'
AND a.[GeoLevel] = 2
AND b.[dGeometrySetId] = 1
AND f.[RowNumber] = 1
AND (d.[MeasureType] = 'I' OR d.[MeasureType] = 'Q')
AND d.[DbdVisibilityStatus] = 'PUBLIC'
AND f.[VisibilityStatus] = 'PUBLIC'
GO
Thanks,
Hadrian

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Hadrian

There's no correlated subquery there, is there? Do you get an error message when you attempt to create the view?

John

Hadrian Hall of Fame Points: 3742 More actions · Answer 2

not the for indexed view, but when I try to create an index on this view created with schemabinding) I cannot because it use a correlated subquery.

Thanks,

Gabriel

Hadrian Hall of Fame Points: 3742 More actions · Answer 3

Hadrian

Hall of Fame

Points: 3742

April 29, 2016 at 3:19 am

#1875497

sorry a derived table was in message error

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I don't think there's any easy way round this. As you've seen, there are quite a few constructs that aren't allowed in indexed views. Consider whether you need the view to be indexed. Could you get away with creating indexes on one or more of the underlying tables, for example?

John

Hadrian Hall of Fame Points: 3742 More actions · Answer 5

initial request was to replace views with indexes views with clustered index on it in order to improve performance. indeed I will check the index policy if this is last resort-maybe we move to sql 2014 to use columnstore ind?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Don't look at indexed views as panacea to improve performance. If you have a high proportion of write activity to reads, it could make things worse. Instead, consider each one on its merits.

Column store indexes will only give performance gains for very large amounts of data and have their own set of restrictions, which you'll want to investigate before deciding to implement them.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Hadrian (4/29/2016)
initial request was to replace views with indexes views with clustered index on it in order to improve performance. indeed I will check the index policy if this is last resort-maybe we move to sql 2014 to use columnstore ind?

Have you checked the execution plan of this query for tuning opportunities?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Hadrian Hall of Fame Points: 3742 More actions · Answer 8

Hi ,

I attaché execution plan for the view ,query returns more than 7 millions rows .

Thanks,

Hadrian

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Thanks.

There's plenty of scope for tuning here, few of the indexes are fully supportive.

You have a residual predicate

[SurveillanceDashboardRepository].[dbd].[fResult].[GeoLevel] as [a].[GeoLevel]=(2)

on a 27 million row table. An "actual" execution plan with runtime figures would show how painful that filter is. Can you post one? You could always run the results into a #temp table then drop it afterwards.

Are you at liberty to create / amend indexes?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden