InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:50:10 GMT20http://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspx[quote][b]Gazareth (1/23/2013)[/b][hr]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 ) goI think you'll see this view being used in place of the table.Cheers[/quote]You should take this advice.Or, better, merge YEAR and MONTH columns together:[code="sql"]DATEADD(mm, DATEDIFF(mm, 0, hhdatetime), 0) MonthOfYear[/code]And make this column the first one in the clustered index.Then you may just select WHERE MonthOfYear between @StartOfThePeriod AND @EndOfThePeriodThan you may just use Thu, 24 Jan 2013 21:38:44 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxFirst, I assume you've all met all the oodles of restrictions for an indexed view; you might want to verify that using Books Online "CREATE INDEX", "Remarks", "Indexed Views" as a guied.Also, GROUP BY in the base query in exactly the same order as the clus index will be:[code="sql"]create view dbo.withClusteredIdxwith schemabindingasselect c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yy...[b]group by YEAR(hhdatetime),MONTH(hhdatetime),c.Reference, mc.MPAN,ElementID[/b]CREATE UNIQUE CLUSTERED INDEX [idxTest] ON [dbo].[withClusteredIdx] ([yy] ASC,[mm] ASC,[Reference] ASC,[MPAN] ASC,[ElementID] ASC)goSELECT ...FROM dbo.withClusteredIdxWHERE yy >= @YEAR ... AND mm ...[/code]Thu, 24 Jan 2013 09:09:43 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxI was going to suggest the NOEXPAND hint! I couldn't see anywhere in the thread to see what edition of SQL Server the OP is using. Unless it's enterprise edition the optimiser won't consider the index on the view when it's costing the query. Unless i've misunderstood the way the optimiser works with indexed views.Thanks,SimonWed, 23 Jan 2013 08:43:21 GMTs_osborne2http://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxCan't see the plan. I'm getting encoding errors. If you save the plan as a .sqlplan file, you can attach it. Then it can be downloaded.Just on what I can see though, it looks like it's expanding the view. For giggles, try the noexpand hint when you create the indexed view. Since the query is clearly going to lead to scans (functions on the GROUP BY are a problem), you want to avoid that. This is a place where a hint might work well.Wed, 23 Jan 2013 07:03:20 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspx[quote][b]ChrisM@Work (1/23/2013)[/b][hr]Have you tried figuring out the filters and applying to the inner query? Something like this:[code="sql"]DECLARE @YEAR INT, @MONTH INTSELECT @YEAR = 2005, @MONTH = 6DECLARE @StartDate DATETIME, @EndDate DATETIMESET @StartDate = CONVERT(DATETIME,CAST(@YEAR AS CHAR(4))+RIGHT('0'+CAST(@MONTH AS VARCHAR(2)),2)+'01',112)SET @EndDate = DATEADD(mm,13,@StartDate)SELECT @StartDate, @EndDate -- eyeballSELECT c.Reference, mc.MPAN--, --ElementID, --mm = MONTH(hhdatetime), --yy = YEAR(hhdatetime) FROM CONTRACTS.MPAN_Contract mcjoin CONTRACTS.ContractElementUnitInstance ui on mc.ContractID = ui.ContractIDjoin CONTRACTS.Contract c on ui.ContractID = c.id and ui.ElementID in (2,3,4)join CONTRACTS.CalendarAllHH cal on cal.HHDatetime between ui.Startdate and ui.Enddate and cal.HHDatetime between mc.startdate and mc.enddate WHERE cal.HHDatetime >= @StartDate AND cal.HHDatetime < @EndDateGROUP BY c.Reference, mc.MPAN, ElementID, MONTH(hhdatetime), YEAR(hhdatetime)[/code][/quote]I did wonder if the view was created to allow direct use of the @Month & @Year parameters.I also assume the group by clause is there to remove duplicates - any ideas on if that or distinct performs better?Wed, 23 Jan 2013 06:51:16 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxHave you tried figuring out the filters and applying to the inner query? Something like this:[code="sql"]DECLARE @YEAR INT, @MONTH INTSELECT @YEAR = 2005, @MONTH = 6DECLARE @StartDate DATETIME, @EndDate DATETIMESET @StartDate = CONVERT(DATETIME,CAST(@YEAR AS CHAR(4))+RIGHT('0'+CAST(@MONTH AS VARCHAR(2)),2)+'01',112)SET @EndDate = DATEADD(mm,13,@StartDate)SELECT @StartDate, @EndDate -- eyeballSELECT c.Reference, mc.MPAN--, --ElementID, --mm = MONTH(hhdatetime), --yy = YEAR(hhdatetime) FROM CONTRACTS.MPAN_Contract mcjoin CONTRACTS.ContractElementUnitInstance ui on mc.ContractID = ui.ContractIDjoin CONTRACTS.Contract c on ui.ContractID = c.id and ui.ElementID in (2,3,4)join CONTRACTS.CalendarAllHH cal on cal.HHDatetime between ui.Startdate and ui.Enddate and cal.HHDatetime between mc.startdate and mc.enddate WHERE cal.HHDatetime >= @StartDate AND cal.HHDatetime < @EndDateGROUP BY c.Reference, mc.MPAN, ElementID, MONTH(hhdatetime), YEAR(hhdatetime)[/code]Wed, 23 Jan 2013 06:38:47 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxhere is the execution plan in XML, seems to be doing a hash match, can you view this in ssmsquery 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>Wed, 23 Jan 2013 06:05:56 GMTlestatf4http://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxHave 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.Wed, 23 Jan 2013 05:40:22 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspx[quote][b]lestatf4 (1/23/2013)[/b][hr]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 withClusteredIdxwith schemabindingasselect c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yyfrom CONTRACTS.MPAN_Contract mcjoin CONTRACTS.ContractElementUnitInstance uion mc.ContractID = ui.ContractIDjoin CONTRACTS.Contract c on ui.ContractID = c.idand ui.ElementID in (2,3,4)join CONTRACTS.CalendarAllHH calon cal.HHDatetime between ui.Startdate and ui.Enddateand cal.HHDatetime between mc.startdate and mc.enddategroup 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) goselect Reference,mpanfrom mpanContractMMyyCachewhere yy between @YEAR and @YEAR + 1and ((mm >= @MONTH and yy = @YEAR) or (mm <= @MONTH and yy = @YEAR + 1))[/quote]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.Wed, 23 Jan 2013 05:35:28 GMTmichael.higginshttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspx[quote][b]lestatf4 (1/23/2013)[/b][hr]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 withClusteredIdxwith schemabindingasselect c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yyfrom CONTRACTS.MPAN_Contract mcjoin CONTRACTS.ContractElementUnitInstance uion mc.ContractID = ui.ContractIDjoin CONTRACTS.Contract c on ui.ContractID = c.idand ui.ElementID in (2,3,4)join CONTRACTS.CalendarAllHH calon cal.HHDatetime between ui.Startdate and ui.Enddateand cal.HHDatetime between mc.startdate and mc.enddategroup 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) goselect Reference,mpanfrom mpanContractMMyyCachewhere yy between @YEAR and @YEAR + 1and ((mm >= @MONTH and yy = @YEAR) or (mm <= @MONTH and yy = @YEAR + 1))[/quote]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 ) goI think you'll see this view being used in place of the table.CheersWed, 23 Jan 2013 05:27:28 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxThanks 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!Wed, 23 Jan 2013 04:44:04 GMTlestatf4http://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspx[quote][b]lestatf4 (1/23/2013)[/b][hr]select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yyfrom CONTRACTS.MPAN_Contract mcjoin CONTRACTS.ContractElementUnitInstance uion mc.ContractID = ui.ContractIDjoin CONTRACTS.Contract c on ui.ContractID = c.idand ui.ElementID in (2,3,4)join CONTRACTS.CalendarAllHH calon cal.HHDatetime between ui.Startdate and ui.Enddateand cal.HHDatetime between mc.startdate and mc.enddategroup by c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime),YEAR(hhdatetime)[/quote] 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 ?Wed, 23 Jan 2013 04:28:11 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxHere 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 withClusteredIdxwith schemabindingasselect c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yyfrom CONTRACTS.MPAN_Contract mcjoin CONTRACTS.ContractElementUnitInstance uion mc.ContractID = ui.ContractIDjoin CONTRACTS.Contract c on ui.ContractID = c.idand ui.ElementID in (2,3,4)join CONTRACTS.CalendarAllHH calon cal.HHDatetime between ui.Startdate and ui.Enddateand cal.HHDatetime between mc.startdate and mc.enddategroup 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) goselect Reference,mpanfrom mpanContractMMyyCachewhere yy between @YEAR and @YEAR + 1and ((mm >= @MONTH and yy = @YEAR) or (mm <= @MONTH and yy = @YEAR + 1))Wed, 23 Jan 2013 04:17:59 GMTlestatf4http://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxAre 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.CheersGazWed, 23 Jan 2013 04:10:36 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxI meant *no performance benefitWed, 23 Jan 2013 04:08:41 GMTlestatf4http://www.sqlservercentral.com/Forums/Topic1410452-392-1.aspxI 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?Wed, 23 Jan 2013 03:50:19 GMTlestatf4