Indexed Views - why don't they improve performance?!!!!

Question

Post reply

Indexed Views - why don't they improve performance?!!!!

AlexF1

Old Hand

Points: 384
More actions
January 23, 2013 at 3:50 am

#287931

I have a query that takes quite a while to run even after optimization. DML statement on the underlying table are in-frequent but must be included in the results whenever the query is run (or very shortly after). I thought to myself, ‘I know, what about an indexed view’. Went ahead and create one, the query returns five columns, collectively unique so I created a clustered index to include them all. When running a query the selects the included columns I would have expected the results to be returned quite quickly, I was wrong. On review of the execution plan I noticed the query was hitting the underlying tables, and offering to real performance benefit. Any ideas?

Viewing 15 posts - 1 through 15 (of 15 total)

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

AlexF1 Old Hand Points: 384 More actions · Answer 1

AlexF1

Old Hand

Points: 384

January 23, 2013 at 4:08 am

#1579991

I meant *no performance benefit

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Are all the columns included in the where clause of the query? Could be that the order of columns in the index means the optimiser is disregarding it.

If you could post DDL, sample data & the query might be able to help further.

Cheers

Gaz

AlexF1 Old Hand Points: 384 More actions · Answer 3

Here is the script, is should return a years worth of data from @month and @year.

Surely the column order in the query is irrelevant to the optimizer?

create view withClusteredIdx

with schemabinding

as

select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yy

from CONTRACTS.MPAN_Contract mc

join CONTRACTS.ContractElementUnitInstance ui

on mc.ContractID = ui.ContractID

join CONTRACTS.Contract c

on ui.ContractID = c.id

and ui.ElementID in (2,3,4)

join CONTRACTS.CalendarAllHH cal

on cal.HHDatetime between ui.Startdate and ui.Enddate

and cal.HHDatetime between mc.startdate and mc.enddate

group by c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime),YEAR(hhdatetime)

CREATE UNIQUE CLUSTERED INDEX [idxTest] ON [dbo].[withClusteredIdx]

(

[ElementID] ASC,

[mm] ASC,

[MPAN] ASC,

[Reference] ASC,

[yy] ASC

) go

select Reference,mpan

from mpanContractMMyyCache

where yy between @YEAR and @YEAR + 1

and ((mm >= @MONTH and yy = @YEAR) or (mm <= @MONTH and yy = @YEAR + 1))

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

lestatf4 (1/23/2013)
select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yy
from CONTRACTS.MPAN_Contract mc
join CONTRACTS.ContractElementUnitInstance ui
on mc.ContractID = ui.ContractID
join CONTRACTS.Contract c
on ui.ContractID = c.id
and ui.ElementID in (2,3,4)
join CONTRACTS.CalendarAllHH cal
on cal.HHDatetime between ui.Startdate and ui.Enddate
and cal.HHDatetime between mc.startdate and mc.enddate
group by c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime),YEAR(hhdatetime)

Do you have appropriate indxes on all tables (ON and AND clause involve ) in above query ?

Have you seen the exec plan of this query too ? also there is less chance that there would be optimal use of indexes as LEFT based column "ElementID" is not involve on your view Where clause.

There will be scan, NO seek ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

AlexF1 Old Hand Points: 384 More actions · Answer 5

Thanks for your reply, surely the performance of the query defining the index view shouldn't have an impact on a query referencing the view if the index covers all column in the query?

Guess this is hard to work out remotely!

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

lestatf4 (1/23/2013)
Here is the script, is should return a years worth of data from @month and @year.
Surely the column order in the query is irrelevant to the optimizer?
create view withClusteredIdx
with schemabinding
as
select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yy
from CONTRACTS.MPAN_Contract mc
join CONTRACTS.ContractElementUnitInstance ui
on mc.ContractID = ui.ContractID
join CONTRACTS.Contract c
on ui.ContractID = c.id
and ui.ElementID in (2,3,4)
join CONTRACTS.CalendarAllHH cal
on cal.HHDatetime between ui.Startdate and ui.Enddate
and cal.HHDatetime between mc.startdate and mc.enddate
group by c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime),YEAR(hhdatetime)
CREATE UNIQUE CLUSTERED INDEX [idxTest] ON [dbo].[withClusteredIdx]
(
[ElementID] ASC,
[mm] ASC,
[MPAN] ASC,
[Reference] ASC,
[yy] ASC
) go
select Reference,mpan
from mpanContractMMyyCache
where yy between @YEAR and @YEAR + 1
and ((mm >= @MONTH and yy = @YEAR) or (mm <= @MONTH and yy = @YEAR + 1))

Column order of the index, not the query. There's no way that index would be used for that query.

If you change your indexed view definition to:

CREATE UNIQUE CLUSTERED INDEX [idxTest] ON [dbo].[withClusteredIdx]

(

[yy] ASC,

[mm] ASC,

[Reference] ASC,

[MPAN] ASC,

[ElementID] ASC

) go

I think you'll see this view being used in place of the table.

Cheers

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 7

lestatf4 (1/23/2013)
Here is the script, is should return a years worth of data from @month and @year.
Surely the column order in the query is irrelevant to the optimizer?
create view withClusteredIdx
with schemabinding
as
select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yy
from CONTRACTS.MPAN_Contract mc
join CONTRACTS.ContractElementUnitInstance ui
on mc.ContractID = ui.ContractID
join CONTRACTS.Contract c
on ui.ContractID = c.id
and ui.ElementID in (2,3,4)
join CONTRACTS.CalendarAllHH cal
on cal.HHDatetime between ui.Startdate and ui.Enddate
and cal.HHDatetime between mc.startdate and mc.enddate
group by c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime),YEAR(hhdatetime)
CREATE UNIQUE CLUSTERED INDEX [idxTest] ON [dbo].[withClusteredIdx]
(
[ElementID] ASC,
[mm] ASC,
[MPAN] ASC,
[Reference] ASC,
[yy] ASC
) go
select Reference,mpan
from mpanContractMMyyCache
where yy between @YEAR and @YEAR + 1
and ((mm >= @MONTH and yy = @YEAR) or (mm <= @MONTH and yy = @YEAR + 1))

Have you tried the following in the from part of your query:

from mpanContractMMyyCache with (NOEXPAND)

This may force the use of your clustered index on the schemabound view.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 8

Have you checked the execution plan to see what is being referenced from the query? It might not be using the view at all. Or, it's possible that, despite the construct of the view, the filtering just won't work within the view. You can tell all this from the execution plan rather than trying to guess from the code.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

AlexF1 Old Hand Points: 384 More actions · Answer 9

here is the execution plan in XML, seems to be doing a hash match, can you view this in ssms

query is

select [ElementID] ,

[mm] ,

[MPAN] ,

[Reference] ,

[yy]

from withClusteredIdx

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</OutputList>

</OutputList>

<Hash>

</HashKeysBuild>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</BuildResidual>

</OutputList>

</PartitionColumns>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

<ScalarOperator ScalarString="[HalfHourDataV2dev].[CONTRACTS].[MPAN_Contract].[ContractID] as [mc].[ContractID]=[HalfHourDataV2dev].[CONTRACTS].[Contract].[ID] as [c].[ID] AND [HalfHourDataV2dev].[CONTRACTS].[CalendarAllHH].[HHDatetime] as [cal].[HHDatetime]>=[HalfHourDataV2dev].[CONTRACTS].[MPAN_Contract].[startdate] as [mc].[startdate] AND [HalfHourDataV2dev].[CONTRACTS].[CalendarAllHH].[HHDatetime] as [cal].[HHDatetime]<=[HalfHourDataV2dev].[CONTRACTS].[MPAN_Contract].[enddate] as [mc].[enddate]">

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</PartitionColumns>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</OutputList>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</DefinedValue>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</PartitionColumns>

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</StartRange>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</EndRange>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Parallelism>

</RelOp>

</ComputeScalar>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</Hash>

</RelOp>

</Parallelism>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

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

Have you tried figuring out the filters and applying to the inner query? Something like this:

DECLARE @YEAR INT, @MONTH INT

SELECT @YEAR = 2005, @MONTH = 6

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = CONVERT(DATETIME,CAST(@YEAR AS CHAR(4))+RIGHT('0'+CAST(@MONTH AS VARCHAR(2)),2)+'01',112)

SET @EndDate = DATEADD(mm,13,@StartDate)

SELECT @StartDate, @EndDate -- eyeball

SELECT

c.Reference,

mc.MPAN--,

--ElementID,

--mm = MONTH(hhdatetime),

--yy = YEAR(hhdatetime)

FROM CONTRACTS.MPAN_Contract mc

join CONTRACTS.ContractElementUnitInstance ui

on mc.ContractID = ui.ContractID

join CONTRACTS.Contract c

on ui.ContractID = c.id

and ui.ElementID in (2,3,4)

join CONTRACTS.CalendarAllHH cal

on cal.HHDatetime between ui.Startdate and ui.Enddate

and cal.HHDatetime between mc.startdate and mc.enddate

WHERE cal.HHDatetime >= @StartDate AND cal.HHDatetime < @EndDate

GROUP BY c.Reference, mc.MPAN, ElementID, MONTH(hhdatetime), YEAR(hhdatetime)

^{“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

Gazareth One Orange Chip Points: 27737 More actions · Answer 11

ChrisM@Work (1/23/2013)
Have you tried figuring out the filters and applying to the inner query? Something like this:
DECLARE @YEAR INT, @MONTH INT
SELECT @YEAR = 2005, @MONTH = 6
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = CONVERT(DATETIME,CAST(@YEAR AS CHAR(4))+RIGHT('0'+CAST(@MONTH AS VARCHAR(2)),2)+'01',112)
SET @EndDate = DATEADD(mm,13,@StartDate)
SELECT @StartDate, @EndDate -- eyeball
SELECT
c.Reference,
mc.MPAN--,
--ElementID,
--mm = MONTH(hhdatetime),
--yy = YEAR(hhdatetime)
FROM CONTRACTS.MPAN_Contract mc
join CONTRACTS.ContractElementUnitInstance ui
on mc.ContractID = ui.ContractID
join CONTRACTS.Contract c
on ui.ContractID = c.id
and ui.ElementID in (2,3,4)
join CONTRACTS.CalendarAllHH cal
on cal.HHDatetime between ui.Startdate and ui.Enddate
and cal.HHDatetime between mc.startdate and mc.enddate
WHERE cal.HHDatetime >= @StartDate AND cal.HHDatetime < @EndDate
GROUP BY c.Reference, mc.MPAN, ElementID, MONTH(hhdatetime), YEAR(hhdatetime)

I did wonder if the view was created to allow direct use of the @Month & @Year parameters.

I also assume the group by clause is there to remove duplicates - any ideas on if that or distinct performs better?

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 12

Can't see the plan. I'm getting encoding errors. If you save the plan as a .sqlplan file, you can attach it. Then it can be downloaded.

Just on what I can see though, it looks like it's expanding the view. For giggles, try the noexpand hint when you create the indexed view. Since the query is clearly going to lead to scans (functions on the GROUP BY are a problem), you want to avoid that. This is a place where a hint might work well.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 13

I was going to suggest the NOEXPAND hint! I couldn't see anywhere in the thread to see what edition of SQL Server the OP is using. Unless it's enterprise edition the optimiser won't consider the index on the view when it's costing the query. Unless i've misunderstood the way the optimiser works with indexed views.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

ScottPletcher SSC Guru Points: 100940 More actions · Answer 14

First, I assume you've all met all the oodles of restrictions for an indexed view; you might want to verify that using Books Online "CREATE INDEX", "Remarks", "Indexed Views" as a guied.

Also, GROUP BY in the base query in exactly the same order as the clus index will be:

create view dbo.withClusteredIdx

with schemabinding

as

select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yy

...

group by YEAR(hhdatetime),MONTH(hhdatetime),c.Reference, mc.MPAN,ElementID

CREATE UNIQUE CLUSTERED INDEX [idxTest] ON [dbo].[withClusteredIdx]

(

[yy] ASC,

[mm] ASC,

[Reference] ASC,

[MPAN] ASC,

[ElementID] ASC

)

go

SELECT ...

FROM dbo.withClusteredIdx

WHERE

yy >= @YEAR ... AND

mm ...

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.