Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

enter image description here

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;      &#xD;&#xA;DECLARE @lastweekstart DateTime;      &#xD;&#xA;DECLARE @lastweekend DateTime;      &#xD;&#xA;    &#xD;&#xA;      &#xD;&#xA;SET @now = getDate();      &#xD;" StatementType="ASSIGN" />
      </Statements>
      <Statements>
        <StmtSimple StatementCompId="2" StatementId="2" StatementText="&#xA;SET @lastweekstart = DATEADD(      &#xD;&#xA;                 DD,      &#xD;&#xA;                 -(DATEPART(DW, @now - 14) - 1),      &#xD;&#xA;                 @now - 14);       &#xD;" StatementType="ASSIGN" />
      </Statements>
      <Statements>
        <StmtSimple StatementCompId="3" StatementId="3" StatementText="&#xA;SET @lastweekend = DATEADD(      &#xD;&#xA;               DD,      &#xD;&#xA;               7 - (DATEPART(DW, @now - 14)),      &#xD;&#xA;               @now - 14);      &#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;" StatementType="ASSIGN" />
      </Statements>
      <Statements>
        <StmtSimple StatementCompId="4" StatementEstRows="97.4587" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="0.341237" StatementText="&#xA;SELECT Distinct CHINFO.CHILDID,&#xD;&#xA;      COUNT(*)  as PREVIOUSWEEKSACCIDENTS       &#xD;&#xA;       FROM  BKA.CHILDINFORMATION CHINFO       &#xD;&#xA;       JOIN  BKA.CHILDEVENTS CHE       &#xD;&#xA;       ON CHE.CHILDID = CHINFO.CHILDID        &#xD;&#xA;       JOIN BKA.CHILDEVENTPROPERITIES CHEP       &#xD;&#xA;        ON  CHE.EVENTID = CHEP.EVENTID           &#xD;&#xA;        WHERE (CHE.TYPE = 'ACCIDENT' OR (CHE.TYPE = 'POTTYBREAK' AND CHEP.PROPERTY = 'SUCCESS'       &#xD;&#xA;        AND CHEP.PROPERTYVALUE = 'FALSE'))           &#xD;&#xA;        AND CHE.ADDDATE BETWEEN @lastweekstart AND @lastweekend       &#xD;&#xA;        GROUP BY CHINFO.CHILDID&#xD;&#xA;        &#xD;&#xA;        " 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]&gt;=[@lastweekstart] AND [xxxxxxx].[BKA].[ChildEvents].[AddDate] as [CHE].[AddDate]&lt;=[@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>
share|improve this question
1  
What is the structure of the existing indexes? –  Mark Sinkinson May 20 at 8:06
    
Provide the primary keys of the tables, the foreign keys between them and the existing indexes - as Mark has already asked. It would also help to explain what the query does, SELECT DISTINCT and GROUP BY are rarely useful together. –  ypercube May 20 at 8:20
    
@ypercube Look at my edit –  bala3569 May 20 at 9:08
    
Please provide the execution plan in xml format. –  vonPryz May 20 at 9:15
    
I think the distinct can be safely removed. An index on (Type, AddDate) might help but not sure, the OR may be a problem. Wait a few hours, I'm sure someone will provide more help or an answer. –  ypercube May 20 at 9:15

1 Answer 1

up vote 1 down vote accepted

Try this:

  1. CHILDEVENTPROPERITIES - Make a nonclustered index:

    • Key columns: Property, PropertyValue
    • Included column: EventId
  2. CHILDEVENTS - Make a nonclustered index:

    • Key columns: AddDate, Type
    • Included column: ChildID, EventID
  3. CHILDINFORMATION - Make a nonclustered index:

    • Key columns: ChildID

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.

SELECT
   CHILDID
   ,SUM(PREVIOUSWEEKSACCIDENTS) AS PREVIOUSWEEKSACCIDENTS
FROM
   (SELECT
      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'
      AND CHE.ADDDATE BETWEEN @lastweekstart AND @lastweekend
   GROUP BY
      CHINFO.CHILDID

   UNION ALL

   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 = 'POTTYBREAK'
      AND CHEP.PROPERTY = 'SUCCESS'
      AND CHEP.PROPERTYVALUE = 'FALSE'
      AND CHE.ADDDATE BETWEEN @lastweekstart AND @lastweekend
   GROUP BY
      CHINFO.CHILDID) AS t
GROUP BY
   CHILDID
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.