I have have been assigned to resolve Performance issue of a Stored Procedure.
Select TableTwo.Column1, DatabaseTwo..TableThree.Column2, Sum(TableTwo.Column3) As Column3 --- Section 1 from TableOne inner join TableTwo on TableOne.Column4 = TableTwo.Column4 inner join DatabaseTwo..TableThree on TableTwo.Column1 = DatabaseTwo..TableThree.Column5 inner join TableFour on TableOne.Column6 = TableFour.Column6 inner join TableFive on TableFour.Column7 = TableFive.Column7 inner join TableSix on TableFour.Column8 = TableSix.Column8 inner join TableSeven on TableSeven.Column7 = TableFive.Column7 inner join TableEight on TableOne.Column9 = TableEight.Column9 inner join TableNine on TableFour.Column10 = TableNine.Column10 inner join TableTen on TableFive.Column11 = TableTen.Column11 inner join TableEleven on TableFive.Column12 = TableEleven.Column12 inner join TableTwelve on TableTwelve.Column7 = TableFive.Column7 and TableTwelve.Column9 = TableOne.Column9 inner join TableThirteen on TableThirteen.Column13 = TableFour.Column13 inner join TableSeventeen on TableSeventeen.Column14 = TableOne.Column14 where TableFive.Column15 = 3 --- Section 2 and TableOne.Column6 in (Select TableOne.Column6 from TableOne, TableTwo, TableFour Where TableOne.Column4 = TableTwo.Column4 and TableOne.Column6 = TableFour.Column6 and TableFour.Column16 >= @StartDate and TableFour.Column16< @EndDate ) --- Section 3 and TableFour.Column6 In (Select TableFour.Column6 from TableFour,TableEightteen,TableNineTeen Where TableFour.Column7 = TableNineTeen.Column7 and TableEightteen.Column17 = TableNineTeen.Column17 and TableFour.Column10 = TableNineTeen.Column10 and TableEightteen.Column18 > TableFour.Column16 ) --- Section 4 and TableFour.Column7 Not In (SELECT Column7 FROM TableFour GROUP BY Column7, Column10 HAVING ( COUNT(Column7) > 1 ) and Column7 = TableFour.Column7 and Column10 = TableFour.Column10 ) --- Section 5 and TableOne.Column6 in (Select TableOne.Column6 from TableOne, TableTwo, TableFour , TableFive Where TableOne.Column4 = TableTwo.Column4 and TableOne.Column6 = TableFour.Column6 and TableFive.Column7 = TableFour.Column7 and TableFive.Column12=@Column12 ) Group By TableTwo.Column1, DatabaseTwo..TableThree.[Column2] Order By TableTwo.Column1, DatabaseTwo..TableThree.[Column2]
My Question is as per my understanding
Since Section2 has all tables that Section1 has with same join conditions therefore filtering condition
and TableFour.Column16 >= @StartDate and TableFour.Column16< @EndDateof section2 should be transferred to Section1 and this will result in same query. AM I right?
Same scenario is with section6 which has all tables that Section1 but with one extra filter statement
and by removing section2 and section6 we can avoid extra conditions