Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Posts
    1

    Unanswered: Performance joing to same table more than twice

    I find that joining to the same table twice is OK, but as soon as you do it 3 or more times you get a massive performance hit.
    Does anyone know the reason for this? Whats special about 3?
    What's the best approach to do this sort of thing?
    (I've used the SQL Server 2005 Tuning Advisor to add indexes for the query).

    Rather than:
    Select ..., sum(a1.<column>), sum(a2.<column>), sum(a3.<column>) from master_table
    left join table_1 a1 on ...
    left join table_1 a2 on ...
    left join table_1 a3 on ...
    group by ...

    I have to select all the table and filter it using case:
    Select ...,
    sum(case when table_1.<column> = '...') as a1,
    sum(case when table_1.<column> = '...') as a2,
    sum(case when table_1.<column> = '...') as a3,
    from master_table
    left join table_1
    group by ...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    those two queries are not the same

    the first will give you cross join effects (the totals will be wrong)

    and there is nothing special about 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •