Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Indexed Views - why don't they improve...
Add to briefcase
16 posts, Page 1 of 2
12»»

Indexed Views - why don't they improve performance?!!!! Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
lestatf4
Posted Wednesday, January 23, 2013 3:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:43 AM
Points: 12, Visits: 82
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?
Post #1410452
lestatf4
Posted Wednesday, January 23, 2013 4:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:43 AM
Points: 12, Visits: 82
I meant *no performance benefit
Post #1410460
Gazareth
Posted Wednesday, January 23, 2013 4:10 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344
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
Post #1410461
lestatf4
Posted Wednesday, January 23, 2013 4:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:43 AM
Points: 12, Visits: 82
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))
Post #1410466
Bhuvnesh
Posted Wednesday, January 23, 2013 4:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1410468
lestatf4
Posted Wednesday, January 23, 2013 4:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:43 AM
Points: 12, Visits: 82
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!
Post #1410478
Gazareth
Posted Wednesday, January 23, 2013 5:27 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344
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
Post #1410501
michael.higgins
Posted Wednesday, January 23, 2013 5:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 66, Visits: 498
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.
Post #1410504
Grant Fritchey
Posted Wednesday, January 23, 2013 5:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1410509
lestatf4
Posted Wednesday, January 23, 2013 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:43 AM
Points: 12, Visits: 82
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"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1600.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="16618800" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1499.94" StatementText="select [ElementID] , [mm] , [MPAN] , [Reference] , [yy] from withClusteredIdx" StatementType="SELECT" QueryHash="0xFA217B40BC55621E" QueryPlanHash="0x996A9D2FEFA969A0">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="104" CompileTime="419" CompileCPU="415" CompileMemory="2880">
<RelOp AvgRowSize="89" EstimateCPU="76.3713" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16618800" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1499.94">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
</OutputList>
<Parallelism>
<RelOp AvgRowSize="89" EstimateCPU="719.561" EstimateIO="560.956" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16618800" LogicalOp="Aggregate" NodeId="1" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1423.57">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
</OutputList>
<MemoryFractions Input="0.998894" Output="1" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[HalfHourDataV2dev].[CONTRACTS].[Contract].[Reference] as [c].[Reference] = [HalfHourDataV2dev].[CONTRACTS].[Contract].[Reference] as [c].[Reference] AND [HalfHourDataV2dev].[CONTRACTS].[MPAN_Contract].[MPAN] as [mc].[MPAN] = [HalfHourDataV2dev].[CONTRACTS].[MPAN_Contract].[MPAN] as [mc].[MPAN] AND [HalfHourDataV2dev].[CONTRACTS].[ContractElementUnitInstance].[ElementID] as [ui].[ElementID] = [HalfHourDataV2dev].[CONTRACTS].[ContractElementUnitInstance].[ElementID] as [ui].[ElementID] AND [Expr1009] = [Expr1009] AND [Expr1010] = [Expr1010]">
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1009" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1009" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1010" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1010" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="89" EstimateCPU="24.0906" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16618800" LogicalOp="Repartition Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="143.051">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
</PartitionColumns>
<RelOp AvgRowSize="89" EstimateCPU="36.1559" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16618800" LogicalOp="Inner Join" NodeId="3" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="118.96">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
</OutputList>
<MemoryFractions Input="1" Output="0.00110641" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="ContractID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
</HashKeysProbe>
<ProbeResidual>
<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]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="ContractID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="startdate" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="enddate" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="57" EstimateCPU="0.0308234" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="662" LogicalOp="Repartition Streams" NodeId="4" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0395762">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="ContractID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="startdate" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="enddate" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="ContractID" />
</PartitionColumns>
<RelOp AvgRowSize="57" EstimateCPU="0.0004426" EstimateIO="0.00831019" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="662" LogicalOp="Index Scan" NodeId="5" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00875279" TableCardinality="662">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="ContractID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="startdate" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="enddate" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="MPAN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="ContractID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="startdate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Alias="[mc]" Column="enddate" />
</DefinedValue>
</DefinedValues>
<Object Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[MPAN_Contract]" Index="[_dta_index_MPAN_Contract_7_407672500__K3_K4_K5_K2]" Alias="[mc]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="67" EstimateCPU="0.453533" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9070660" LogicalOp="Compute Scalar" NodeId="6" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="82.7645">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1009" />
<ScalarOperator ScalarString="datepart(month,CONVERT_IMPLICIT(datetime,[HalfHourDataV2dev].[CONTRACTS].[CalendarAllHH].[HHDatetime] as [cal].[HHDatetime],0))">
<Intrinsic FunctionName="datepart">
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
<ScalarOperator>
<Convert DataType="datetime" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1010" />
<ScalarOperator ScalarString="datepart(year,CONVERT_IMPLICIT(datetime,[HalfHourDataV2dev].[CONTRACTS].[CalendarAllHH].[HHDatetime] as [cal].[HHDatetime],0))">
<Intrinsic FunctionName="datepart">
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
<ScalarOperator>
<Convert DataType="datetime" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="59" EstimateCPU="32.489" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9070660" LogicalOp="Repartition Streams" NodeId="7" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="82.3109">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
</PartitionColumns>
<RelOp AvgRowSize="59" EstimateCPU="29.3599" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9070660" LogicalOp="Inner Join" NodeId="8" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="49.822">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</OutputList>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Startdate" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Enddate" />
<ColumnReference Column="Expr1013" />
</OuterReferences>
<RelOp AvgRowSize="63" EstimateCPU="0.000666959" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="310.343" LogicalOp="Inner Join" NodeId="10" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0686455">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Startdate" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Enddate" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
</OutputList>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ContractID" />
<ColumnReference Column="Expr1012" />
</OuterReferences>
<RelOp AvgRowSize="23" EstimateCPU="0.0291623" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="319.119" LogicalOp="Repartition Streams" NodeId="12" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.040438">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ContractID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Startdate" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Enddate" />
</OutputList>
<Parallelism PartitioningType="RoundRobin">
<RelOp AvgRowSize="23" EstimateCPU="0.00075665" EstimateIO="0.00979167" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="319.119" LogicalOp="Index Scan" NodeId="13" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0105483" TableCardinality="1233">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ContractID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Startdate" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Enddate" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ContractID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Startdate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Enddate" />
</DefinedValue>
</DefinedValues>
<Object Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Index="[_dta_index_ContractElementUnitInstance_7_599673184__K5_K4_K8_K7_K3_6]" Alias="[ui]" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[HalfHourDataV2dev].[CONTRACTS].[ContractElementUnitInstance].[ElementID] as [ui].[ElementID]=(2) OR [HalfHourDataV2dev].[CONTRACTS].[ContractElementUnitInstance].[ElementID] as [ui].[ElementID]=(3) OR [HalfHourDataV2dev].[CONTRACTS].[ContractElementUnitInstance].[ElementID] as [ui].[ElementID]=(4)">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(3)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ElementID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="51" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="314.169" EstimateRewinds="3.95076" EstimateRows="1" LogicalOp="Index Seek" NodeId="14" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0567028" TableCardinality="161">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="Reference" />
</DefinedValue>
</DefinedValues>
<Object Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Index="[_dta_index_Contract_7_215671816__K1_K2_5_6]" Alias="[c]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[Contract]" Alias="[c]" Column="ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[HalfHourDataV2dev].[CONTRACTS].[ContractElementUnitInstance].[ContractID] as [ui].[ContractID]">
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="ContractID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.049949" EstimateIO="0.0446065" EstimateRebinds="308.25" EstimateRewinds="1.09267" EstimateRows="45265.4" LogicalOp="Index Seek" NodeId="15" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="20.3934" TableCardinality="192048">
<OutputList>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</DefinedValue>
</DefinedValues>
<Object Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Index="[_dta_index_CalendarAllHH_41_1993774160__K1]" Alias="[cal]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[HalfHourDataV2dev].[CONTRACTS].[ContractElementUnitInstance].[Startdate] as [ui].[Startdate]">
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Startdate" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LE">
<RangeColumns>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[CalendarAllHH]" Alias="[cal]" Column="HHDatetime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[HalfHourDataV2dev].[CONTRACTS].[ContractElementUnitInstance].[Enddate] as [ui].[Enddate]">
<Identifier>
<ColumnReference Database="[HalfHourDataV2dev]" Schema="[CONTRACTS]" Table="[ContractElementUnitInstance]" Alias="[ui]" Column="Enddate" />
</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>
Post #1410523
« Prev Topic | Next Topic »

Add to briefcase
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.