3

We have a discussion over SQL Server 2008 and join. One half says the more joins the slower you sql runs. The other half says ihat it does not matter because SQL server takes care of business so you wil not notice any performance loss. What is true?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Patrick de Ruijter
  • 79
  • 4
  • 1
    It affects performance, might be better, might be worse. It all depends on what and how you join and what your alternatives are. – Albin Sunnanbo Feb 14 '11 at 11:11
  • 2
    Are you asking about the benefits of normalisation? – Martin Smith Feb 14 '11 at 11:14
  • 1
    @Martin Good question. Denormalisation, and therefore the reduction of joins, is one method used to speed up processing in data warehousing. But you wouldn't want to run your operational database like that, because it would become a total mess to update and to keep consistent. – Matt Gibson Feb 14 '11 at 11:35

2 Answers2

2

Instead of asking the question the way you have, consider instead:

  • Can I get the data I want without the join?
    • No => You need the join, end of discussion.

It is also a matter of degree. It is impossible for a join not to add additional processing. Even if the Query Optimizer takes it out (e.g. left join with nothing used from the join) - it still costs CPU cycles to parse it.

Now if the question is about comparing joins to another technique, such as one special case of LEFT JOIN + IS NULL vs NOT EXISTS for a record in X not in Y scenario, then let's discuss specifics - table sizes (X vs Y), indexes etc.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 1
    +1 If you need it you need it. Also it's hard to second guess the query optimiser, profile each case and see which is best. – Binary Worrier Feb 14 '11 at 11:16
0

It will slow it down: the more complicated a query, the more work the database server has to do to execute it.

But about that "performance loss": over what? Is there another way to get at the same data? If so, then you can profile the various options against each other to see which is fastest.

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111