Hi here i have a query and i need to improve the performance of this query.Can anyone suggest me like how to convert the clustered index scan to index seek.
DECLARE @now DateTime;
DECLARE @lastweekstart DateTime;
DECLARE @lastweekend DateTime;
SET @now = getDate();
SET @lastweekstart = DATEADD(
DD,
-(DATEPART(DW, @now - 14) - 1),
@now - 14);
SET @lastweekend = DATEADD(
DD,
7 - (DATEPART(DW, @now - 14)),
@now - 14);
SELECT Distinct CHINFO.CHILDID,
COUNT(*) as PREVIOUSWEEKSACCIDENTS
FROM BKA.CHILDINFORMATION CHINFO
JOIN BKA.CHILDEVENTS CHE
ON CHE.CHILDID = CHINFO.CHILDID
JOIN BKA.CHILDEVENTPROPERITIES CHEP
ON CHE.EVENTID = CHEP.EVENTID
WHERE (CHE.TYPE = 'ACCIDENT' OR (CHE.TYPE = 'POTTYBREAK' AND CHEP.PROPERTY = 'SUCCESS'
AND CHEP.PROPERTYVALUE = 'FALSE'))
AND CHE.ADDDATE BETWEEN @lastweekstart AND @lastweekend
GROUP BY CHINFO.CHILDID
Any suggestion?
EDIT:
Primary Keys:
TableName --> ColumnName
CHILDINFORMATION-->ChildId
CHILDEVENTS-->EventId
CHILDEVENTPROPERITIES-->EventPropertyId
Foreign Keys:
CHILDEVENTPROPERITIES-->Here EventId is the foreign key reference of CHILDEVENTS table
CHILDEVENTS-->Here ChildId is the foreign key reference of CHILDINFORMATION table
NonClustered Indexes:
CHILDINFORMATION-->No nonclustered indexes
ChildEventProperities-->IX_CHILDEVENTproperities_EventId-->EventID
ChildEvents-->IX_ChildEvents_Type-->Type
ChildEvents-->IX_ChildEvents_Type_AddDate-->Type,AddDate
Execution plan:
<?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.0.4000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementId="1" StatementText="DECLARE @now DateTime; 
DECLARE @lastweekstart DateTime; 
DECLARE @lastweekend DateTime; 
 
 
SET @now = getDate(); 
" StatementType="ASSIGN" />
</Statements>
<Statements>
<StmtSimple StatementCompId="2" StatementId="2" StatementText="
SET @lastweekstart = DATEADD( 
 DD, 
 -(DATEPART(DW, @now - 14) - 1), 
 @now - 14); 
" StatementType="ASSIGN" />
</Statements>
<Statements>
<StmtSimple StatementCompId="3" StatementId="3" StatementText="
SET @lastweekend = DATEADD( 
 DD, 
 7 - (DATEPART(DW, @now - 14)), 
 @now - 14); 



" StatementType="ASSIGN" />
</Statements>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="97.4587" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="0.341237" StatementText="
SELECT Distinct CHINFO.CHILDID,
 COUNT(*) as PREVIOUSWEEKSACCIDENTS 
 FROM BKA.CHILDINFORMATION CHINFO 
 JOIN BKA.CHILDEVENTS CHE 
 ON CHE.CHILDID = CHINFO.CHILDID 
 JOIN BKA.CHILDEVENTPROPERITIES CHEP 
 ON CHE.EVENTID = CHEP.EVENTID 
 WHERE (CHE.TYPE = 'ACCIDENT' OR (CHE.TYPE = 'POTTYBREAK' AND CHEP.PROPERTY = 'SUCCESS' 
 AND CHEP.PROPERTYVALUE = 'FALSE')) 
 AND CHE.ADDDATE BETWEEN @lastweekstart AND @lastweekend 
 GROUP BY CHINFO.CHILDID
 
 " StatementType="SELECT" QueryHash="0x70B70441B34EA558" QueryPlanHash="0xC205D0EFB4F972DC">
<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="64" CompileTime="13" CompileCPU="13" CompileMemory="856">
<RelOp AvgRowSize="15" EstimateCPU="0.000197192" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="97.4587" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.341237">
<OutputList>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1007],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="15" EstimateCPU="0.000197192" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="97.4587" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.341237">
<OutputList>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
<ColumnReference Column="Expr1007" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<GroupBy>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
</GroupBy>
<RelOp AvgRowSize="11" EstimateCPU="0.0031691" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="247.439" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.34104">
<OutputList>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="11" EstimateCPU="0.0215873" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="247.439" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.326609">
<OutputList>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="ChildID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
</HashKeysProbe>
<RelOp AvgRowSize="11" EstimateCPU="0.0985595" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="247.439" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.300699">
<OutputList>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="ChildID" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="EventID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="EventID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[xxxxxxx].[BKA].[ChildEvents].[EventID] as [CHE].[EventID]=[xxxxxxx].[BKA].[ChildEventProperities].[EventID] as [CHEP].[EventID] AND ([xxxxxxx].[BKA].[ChildEvents].[Type] as [CHE].[Type]='ACCIDENT' OR [xxxxxxx].[BKA].[ChildEvents].[Type] as [CHE].[Type]='POTTYBREAK' AND [xxxxxxx].[BKA].[ChildEventProperities].[Property] as [CHEP].[Property]='SUCCESS' AND [xxxxxxx].[BKA].[ChildEventProperities].[PropertyValue] as [CHEP].[PropertyValue]='FALSE')">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="EventID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="EventID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'ACCIDENT'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'POTTYBREAK'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="Property" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'SUCCESS'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="PropertyValue" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'FALSE'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="34" EstimateCPU="0.0145505" EstimateIO="0.0749769" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="187.29" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0895274" TableCardinality="13085">
<OutputList>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="EventID" />
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="ChildID" />
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="Type" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="EventID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="ChildID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="Type" />
</DefinedValue>
</DefinedValues>
<Object Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Index="[PK_ChildEvents_1]" Alias="[CHE]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[xxxxxxx].[BKA].[ChildEvents].[AddDate] as [CHE].[AddDate]>=[@lastweekstart] AND [xxxxxxx].[BKA].[ChildEvents].[AddDate] as [CHE].[AddDate]<=[@lastweekend] AND ([xxxxxxx].[BKA].[ChildEvents].[Type] as [CHE].[Type]='ACCIDENT' OR [xxxxxxx].[BKA].[ChildEvents].[Type] as [CHE].[Type]='POTTYBREAK')">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="GE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="AddDate" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@lastweekstart" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="AddDate" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@lastweekend" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'ACCIDENT'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEvents]" Alias="[CHE]" Column="Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'POTTYBREAK'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="32" EstimateCPU="0.0184247" EstimateIO="0.0734954" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16607" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0919201" TableCardinality="16607">
<OutputList>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="EventID" />
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="Property" />
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="PropertyValue" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="EventID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="Property" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Alias="[CHEP]" Column="PropertyValue" />
</DefinedValue>
</DefinedValues>
<Object Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildEventProperities]" Index="[PK_ChildEventProperities]" Alias="[CHEP]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.000454" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="270" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00431974" TableCardinality="270">
<OutputList>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Alias="[CHINFO]" Column="ChildID" />
</DefinedValue>
</DefinedValues>
<Object Database="[xxxxxxx]" Schema="[BKA]" Table="[ChildInformation]" Index="[PK_ChildInformation_1]" Alias="[CHINFO]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Sort>
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Best Answer
Try this:
CHILDEVENTPROPERITIES - Make a nonclustered index:
CHILDEVENTS - Make a nonclustered index:
CHILDINFORMATION - Make a nonclustered index:
In cases 1 and 2, check what is the optimal key column order (optimal leading column). Also evaluate if you need to keep existing nonclustered indexes, depending on your workload. This specific query doesn't need them.
One additional thing to try is breaking the query into a union, to get rid of the OR in the WHERE clause - see how it affects the plan.