SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
lestatf4
lestatf4
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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?
lestatf4
lestatf4
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 82
I meant *no performance benefit
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7764 Visits: 6045
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
lestatf4
lestatf4
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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))
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13350 Visits: 4077
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;-)
lestatf4
lestatf4
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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!
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7764 Visits: 6045
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
higgim
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 2631
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
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98859 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
lestatf4
lestatf4
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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>
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search