Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Indexed Views - why don't they improve performance?!!!! Expand / Collapse
Author
Message
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
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
Posted Wednesday, January 23, 2013 4:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 1,855, Visits: 2,962
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
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
Posted Wednesday, January 23, 2013 4:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1410468
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
Posted Wednesday, January 23, 2013 5:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 1,855, Visits: 2,962
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
Posted Wednesday, January 23, 2013 5:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 368, Visits: 1,224
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
Posted Wednesday, January 23, 2013 5:40 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 15,516, Visits: 27,892
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
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 12»»

Permissions Expand / Collapse