Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 INNER JOIN with 3 tables

Author  Topic 

ferrethouse
Constraint Violating Yak Guru

352 Posts
Posted - 2014-12-11 : 10:53:55
I'm getting poor performance with the query below due to an index scan on a subscription table index since it is joining on 2 different tables. The activity table does have a courseid but apparently it is not 100% reliable thus the need to join with the lesson table. The query takes over 15 seconds and it won't seek on the index I've created. Any solutions?


select count(*) from Activity
                     where exists
                     (
                          select null from Activity a
                          INNER JOIN Lessons l on l.LessonID = a.LessonID
                          INNER JOIN Subscription s ON l.CourseId = s.CourseId AND a.StudentsID = s.StudentsID
                          INNER JOIN Courses c ON a.CourseId = c.CourseId
                          where ClientID = 1008 AND c.Type IN (0,1,2) AND Activity.ActivityID = a.ActivityID
                     )

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-12-11 : 12:08:01
It's not because "it is joining on 2 different tables", it's because the join columns are the columns in the where clause are not covered. Or it could be that the subscription table is really small and SQL figured that was easiest.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2014-12-11 : 12:31:49
We've got stored procedures with 10+ joins in them and no issue with performance. Like gbritton said, it is not because of the joins.

Show us the showplan xml for it and the indexes on those tables. You likely need to add a covering index.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

352 Posts
Posted - 2014-12-11 : 18:59:44
quote:
Originally posted by tkizer

We've got stored procedures with 10+ joins in them and no issue with performance. Like gbritton said, it is not because of the joins.


To clarify, I wasn't referring to the number of inner joins but the fact that one of the inner joins is "ON" two separate tables which causes an index scan...

INNER JOIN Subscription s ON l.CourseId = s.CourseId AND a.StudentsID = s.StudentsID

If I change this line to...

INNER JOIN Subscription s ON a.CourseId = s.CourseId AND a.StudentsID = s.StudentsID

The query is very fast because it does a seek on the index. Sadly, the courseid from the activity table is not reliable.

The subscription table has 30 million records in it.

Here is the 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.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="250.728" StatementText="select count(*) from Activity#xD;#xA;                     where exists#xD;#xA;                     (#xD;#xA;                          select null from Activity a#xD;#xA;                          INNER JOIN Lessons l on l.LessonID = a.LessonID#xD;#xA;                          INNER JOIN Subscription s ON l.CourseId = s.CourseId AND a.StudentsID = s.StudentsID#xD;#xA;                          INNER JOIN Courses c ON a.CourseId = c.CourseId#xD;#xA;                          where ClientID = 1008 AND c.Type IN (0,1,2) AND Activity.ActivityID = a.ActivityID#xD;#xA;                     )" StatementType="SELECT" QueryHash="0x1A9F9DD06D0894B0" QueryPlanHash="0xB72AF803A8D6264D" RetrievedFromCache="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" MemoryGrant="86376" CachedPlanSize="104" CompileTime="66" CompileCPU="63" CompileMemory="1752">
            <MemoryGrantInfo SerialRequiredMemory="3072" SerialDesiredMemory="86376" RequiredMemory="3072" DesiredMemory="86376" RequestedMemory="86376" GrantWaitTime="0" GrantedMemory="86376" MaxUsedMemory="2904" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="393206" EstimatedPagesCached="98301" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="11" EstimateCPU="0.00251754" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="250.728">
              <OutputList>
                <ColumnReference Column="Expr1007" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1007" />
                    <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1012],0)">
                      <Convert DataType="int" Style="0" Implicit="true">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1012" />
                          </Identifier>
                        </ScalarOperator>
                      </Convert>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="11" EstimateCPU="0.00251754" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="250.728">
                  <OutputList>
                    <ColumnReference Column="Expr1012" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <StreamAggregate>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1012" />
                        <ScalarOperator ScalarString="Count(*)">
                          <Aggregate AggType="countstar" Distinct="false" />
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="9" EstimateCPU="0.0810578" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4195.07" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="250.726">
                      <OutputList />
                      <MemoryFractions Input="0.0071065" Output="1" />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1807" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <Hash>
                        <DefinedValues />
                        <HashKeysBuild>
                          <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Column="ActivityID" />
                        </HashKeysBuild>
                        <RelOp AvgRowSize="11" EstimateCPU="0.0175354" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4195.07" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="250.645">
                          <OutputList>
                            <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Column="ActivityID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="1807" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
                            <OuterReferences>
                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="ActivityID" />
                              <ColumnReference Column="Expr1011" />
                            </OuterReferences>
                            <RelOp AvgRowSize="11" EstimateCPU="59.942" EstimateIO="1.31272" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4195.07" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="236.863">
                              <OutputList>
                                <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="ActivityID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="1807" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <Merge ManyToMany="true">
                                <InnerSideJoinColumns>
                                  <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Alias="[s]" Column="StudentsID" />
                                  <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Alias="[s]" Column="CourseID" />
                                </InnerSideJoinColumns>
                                <OuterSideJoinColumns>
                                  <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="StudentsID" />
                                  <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="CourseID" />
                                </OuterSideJoinColumns>
                                <Residual>
                                  <ScalarOperator ScalarString="[Absorb_20141202].[dbo].[Lessons].[CourseID] as [l].[CourseID]=[Absorb_20141202].[dbo].[Subscription].[CourseID] as [s].[CourseID] AND [Absorb_20141202].[dbo].[Activity].[StudentsID] as [a].[StudentsID]=[Absorb_20141202].[dbo].[Subscription].[StudentsID] as [s].[StudentsID]">
                                    <Logical Operation="AND">
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="CourseID" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Alias="[s]" Column="CourseID" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="StudentsID" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Alias="[s]" Column="StudentsID" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </Residual>
                                <RelOp AvgRowSize="19" EstimateCPU="24.9562" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="298914" LogicalOp="Sort" NodeId="7" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="38.9902">
                                  <OutputList>
                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="ActivityID" />
                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="StudentsID" />
                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="CourseID" />
                                  </OutputList>
                                  <MemoryFractions Input="0.262518" Output="0.987708" />
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1813" ActualEndOfScans="1" ActualExecutions="1" />
                                  </RunTimeInformation>
                                  <Sort Distinct="false">
                                    <OrderBy>
                                      <OrderByColumn Ascending="true">
                                        <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="StudentsID" />
                                      </OrderByColumn>
                                      <OrderByColumn Ascending="true">
                                        <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="CourseID" />
                                      </OrderByColumn>
                                    </OrderBy>
                                    <RelOp AvgRowSize="19" EstimateCPU="9.04467" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="298914" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="14.0227">
                                      <OutputList>
                                        <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="ActivityID" />
                                        <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="StudentsID" />
                                        <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="CourseID" />
                                      </OutputList>
                                      <MemoryFractions Input="0.999473" Output="0.737482" />
                                      <RunTimeInformation>
                                        <RunTimeCountersPerThread Thread="0" ActualRows="1813" ActualEndOfScans="1" ActualExecutions="1" />
                                      </RunTimeInformation>
                                      <Hash>
                                        <DefinedValues />
                                        <HashKeysBuild>
                                          <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="LessonID" />
                                        </HashKeysBuild>
                                        <HashKeysProbe>
                                          <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="LessonID" />
                                        </HashKeysProbe>
                                        <ProbeResidual>
                                          <ScalarOperator ScalarString="[Absorb_20141202].[dbo].[Lessons].[LessonID] as [l].[LessonID]=[Absorb_20141202].[dbo].[Activity].[LessonID] as [a].[LessonID]">
                                            <Compare CompareOp="EQ">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="LessonID" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="LessonID" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </ProbeResidual>
                                        <RelOp AvgRowSize="19" EstimateCPU="1.27644" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="305369" LogicalOp="Inner Join" NodeId="9" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3.20098">
                                          <OutputList>
                                            <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="ActivityID" />
                                            <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="StudentsID" />
                                            <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="LessonID" />
                                          </OutputList>
                                          <RunTimeInformation>
                                            <RunTimeCountersPerThread Thread="0" ActualRows="1890" ActualEndOfScans="1" ActualExecutions="1" />
                                          </RunTimeInformation>
                                          <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
                                            <OuterReferences>
                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Alias="[c]" Column="CourseID" />
                                              <ColumnReference Column="Expr1010" />
                                            </OuterReferences>
                                            <RelOp AvgRowSize="15" EstimateCPU="0.000325066" EstimateIO="0.00430291" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="123.675" LogicalOp="Index Seek" NodeId="12" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00462797" TableCardinality="278361">
                                              <OutputList>
                                                <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Alias="[c]" Column="CourseID" />
                                              </OutputList>
                                              <RunTimeInformation>
                                                <RunTimeCountersPerThread Thread="0" ActualRows="67" ActualEndOfScans="1" ActualExecutions="1" />
                                              </RunTimeInformation>
                                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                                <DefinedValues>
                                                  <DefinedValue>
                                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Alias="[c]" Column="CourseID" />
                                                  </DefinedValue>
                                                </DefinedValues>
                                                <Object Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Index="[_dta_index_Courses_9_919010355__K2_K49_K13_K3_K1_K6]" Alias="[c]" IndexKind="NonClustered" Storage="RowStore" />
                                                <SeekPredicates>
                                                  <SeekPredicateNew>
                                                    <SeekKeys>
                                                      <Prefix ScanType="EQ">
                                                        <RangeColumns>
                                                          <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Alias="[c]" Column="ClientID" />
                                                        </RangeColumns>
                                                        <RangeExpressions>
                                                          <ScalarOperator ScalarString="(1008)">
                                                            <Const ConstValue="(1008)" />
                                                          </ScalarOperator>
                                                        </RangeExpressions>
                                                      </Prefix>
                                                    </SeekKeys>
                                                  </SeekPredicateNew>
                                                </SeekPredicates>
                                                <Predicate>
                                                  <ScalarOperator ScalarString="[Absorb_20141202].[dbo].[Courses].[Type] as [c].[Type]=(0) OR [Absorb_20141202].[dbo].[Courses].[Type] as [c].[Type]=(1) OR [Absorb_20141202].[dbo].[Courses].[Type] as [c].[Type]=(2)">
                                                    <Logical Operation="OR">
                                                      <ScalarOperator>
                                                        <Compare CompareOp="EQ">
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Alias="[c]" Column="Type" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Const ConstValue="(0)" />
                                                          </ScalarOperator>
                                                        </Compare>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Compare CompareOp="EQ">
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Alias="[c]" Column="Type" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Const ConstValue="(1)" />
                                                          </ScalarOperator>
                                                        </Compare>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Compare CompareOp="EQ">
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Alias="[c]" Column="Type" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Const ConstValue="(2)" />
                                                          </ScalarOperator>
                                                        </Compare>
                                                      </ScalarOperator>
                                                    </Logical>
                                                  </ScalarOperator>
                                                </Predicate>
                                              </IndexScan>
                                            </RelOp>
                                            <RelOp AvgRowSize="19" EstimateCPU="0.00287304" EstimateIO="0.0127546" EstimateRebinds="122.675" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2469.13" LogicalOp="Index Seek" NodeId="13" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="1.91973" TableCardinality="69965100">
                                              <OutputList>
                                                <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="ActivityID" />
                                                <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="StudentsID" />
                                                <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="LessonID" />
                                              </OutputList>
                                              <RunTimeInformation>
                                                <RunTimeCountersPerThread Thread="0" ActualRows="1890" ActualEndOfScans="67" ActualExecutions="67" />
                                              </RunTimeInformation>
                                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                                <DefinedValues>
                                                  <DefinedValue>
                                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="ActivityID" />
                                                  </DefinedValue>
                                                  <DefinedValue>
                                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="StudentsID" />
                                                  </DefinedValue>
                                                  <DefinedValue>
                                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="LessonID" />
                                                  </DefinedValue>
                                                </DefinedValues>
                                                <Object Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Index="[cds_new2_Activity_CourseID_ActivityFinished_incl]" Alias="[a]" IndexKind="NonClustered" Storage="RowStore" />
                                                <SeekPredicates>
                                                  <SeekPredicateNew>
                                                    <SeekKeys>
                                                      <Prefix ScanType="EQ">
                                                        <RangeColumns>
                                                          <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="CourseID" />
                                                        </RangeColumns>
                                                        <RangeExpressions>
                                                          <ScalarOperator ScalarString="[Absorb_20141202].[dbo].[Courses].[CourseID] as [c].[CourseID]">
                                                            <Identifier>
                                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Courses]" Alias="[c]" Column="CourseID" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                        </RangeExpressions>
                                                      </Prefix>
                                                    </SeekKeys>
                                                  </SeekPredicateNew>
                                                </SeekPredicates>
                                              </IndexScan>
                                            </RelOp>
                                          </NestedLoops>
                                        </RelOp>
                                        <RelOp AvgRowSize="15" EstimateCPU="0.472459" EstimateIO="1.30461" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="429365" LogicalOp="Index Scan" NodeId="14" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.77706" TableCardinality="429365">
                                          <OutputList>
                                            <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="LessonID" />
                                            <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="CourseID" />
                                          </OutputList>
                                          <RunTimeInformation>
                                            <RunTimeCountersPerThread Thread="0" ActualRows="429365" ActualEndOfScans="1" ActualExecutions="1" />
                                          </RunTimeInformation>
                                          <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="LessonID" />
                                              </DefinedValue>
                                              <DefinedValue>
                                                <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Alias="[l]" Column="CourseID" />
                                              </DefinedValue>
                                            </DefinedValues>
                                            <Object Database="[Absorb_20141202]" Schema="[dbo]" Table="[Lessons]" Index="[missing_index_8_7_Lessons]" Alias="[l]" IndexKind="NonClustered" Storage="RowStore" />
                                          </IndexScan>
                                        </RelOp>
                                      </Hash>
                                    </RelOp>
                                  </Sort>
                                </RelOp>
                                <RelOp AvgRowSize="15" EstimateCPU="30.9393" EstimateIO="105.679" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="28126500" LogicalOp="Index Scan" NodeId="16" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="136.618" TableCardinality="28126500">
                                  <OutputList>
                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Alias="[s]" Column="StudentsID" />
                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Alias="[s]" Column="CourseID" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="28094477" ActualEndOfScans="0" ActualExecutions="1" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Alias="[s]" Column="StudentsID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Alias="[s]" Column="CourseID" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[Absorb_20141202]" Schema="[dbo]" Table="[Subscription]" Index="[cds_Subscription_StudentsID_CourseID_LessonID_Absent]" Alias="[s]" IndexKind="NonClustered" Storage="RowStore" />
                                  </IndexScan>
                                </RelOp>
                              </Merge>
                            </RelOp>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="4194.07" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="18" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="13.7641" TableCardinality="69965100">
                              <OutputList>
                                <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Column="ActivityID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="1807" ActualEndOfScans="0" ActualExecutions="1807" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Column="ActivityID" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Index="[PK_activity]" IndexKind="Clustered" Storage="RowStore" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Column="ActivityID" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="[Absorb_20141202].[dbo].[Activity].[ActivityID] as [a].[ActivityID]">
                                            <Identifier>
                                              <ColumnReference Database="[Absorb_20141202]" Schema="[dbo]" Table="[Activity]" Alias="[a]" Column="ActivityID" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                              </IndexScan>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                      </Hash>
                    </RelOp>
                  </StreamAggregate>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2014-12-11 : 19:18:43
Your stats are out-of-date on Activity and Lessons tables. Actual vs Estimated is way off for those two.

Try updating stats on those two. You should get a new plan. Repost the plan xml.

For the Courses table, can't you use: INNER JOIN Courses c ON s.CourseId = c.CourseId?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

352 Posts
Posted - 2014-12-12 : 11:16:37
quote:
For the Courses table, can't you use: INNER JOIN Courses c ON s.CourseId = c.CourseId?


This change made a huge difference. It blows my mind how you knew to do that.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2014-12-12 : 12:51:26
I analyzed the execution plan using SQL Sentry's free Plan Explorer and looked at the indexes in use.

The Subscriptions table's index in use for a different part had CourseId in it,
so the idea was to grab the data from that index rather than needing to find the data in another index.



Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site