0

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

  1. Since Section2 has all tables that Section1 has with same join conditions therefore filtering condition and TableFour.Column16 >= @StartDate and TableFour.Column16< @EndDate of section2 should be transferred to Section1 and this will result in same query. AM I right?

  2. Same scenario is with section6 which has all tables that Section1 but with one extra filter statement and TableFive.Column12=@Column12

and by removing section2 and section6 we can avoid extra conditions

  • Posting an execution plan will help us help you ! – Kin Shah Oct 27 '15 at 20:32
  • @Kin how SQL work internally ? Is 'IN condition with same joins and one extra condition' not equal to apply filter on main outer query instead of using IN with same joins ? or is this depends on Plan? – AA.SC Oct 27 '15 at 20:37
  • 1
  • @Kin, thanks a new thing for me is we can replace table names in execution plan files, ok will be here again with execution plan. – AA.SC Oct 27 '15 at 20:51
0

Let's just look at TableOne, TableTwo, and TableFour
Most of the condition in the "in where" in are already covered in the join
And suggest you use join on for all

Select TableTwo.Column1, DatabaseTwo..
from TableOne  
inner join TableTwo  on TableOne.Column4 = TableTwo.Column4 
inner join TableFour on TableOne.Column6 = TableFour.Column6         
where 1 = 1
--- Section 2
and TableOne.Column6 in  (Select TableOne.Column6 
                            from TableOne, TableTwo, TableFour  
                            Where TableOne.Column4 = TableTwo.Column4  -- covered in base join 
                            and TableOne.Column6 = TableFour.Column6   -- covered in base join
                            and TableFour.Column16 >= @StartDate 
                            and TableFour.Column16 <  @EndDate )

Pretty sure this is the same query
Not sure if it is faster but I doubt is is slower
The query optimizer has a better chance of filtering early

Select TableTwo.Column1, DatabaseTwo..
from TableOne  
inner join TableTwo  on  TableOne.Column4 = TableTwo.Column4 
inner join TableFour on  TableOne.Column6 = TableFour.Column6   
                     and TableFour.Column16 >= @StartDate 
                     and TableFour.Column16 <  @EndDate  

as for

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
                                ) 

pretty sure that can be

join TableEightteen on  TableEightteen.Column18 > TableFour.Column16 
join TableNineTeen  on  TableFour.Column7  = TableNineTeen.Column7  
                    and TableFour.Column10 = TableNineTeen.Column10  
                    and TableEightteen.Column17 = TableNineTeen.Column17
  • Actually yes this is what i am trying to conclude both statements will return same result set or is there any scenario which is hidden from me and will return different result? – AA.SC Oct 28 '15 at 6:10
  • "Pretty sure this is the same query" – paparazzo Oct 28 '15 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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