Are there any performance issues if an sql query contains lot of joins?

Question

Are there any performance issues if an sql query contains lot of joins?

Answer 1

One of the best ways to boost JOIN performance is to limit how many rows need to be JOINed.

Read more in this article

Performance Tuning SQL Server Joins

Answer 2

There can be -- but query performance is a sensitive thing affected by lots of factors:

• Number of joins
• Structure of tables
• Size of the database
• Presence if and data type of indexes
• Data types of values being joined
• etc etc

You can get into all sorts of details. But generally the best approach is to write a query that works and then profile your app to see if you actually have a problem. Then, start looking at optimizing your queries.

Answer 3

Yes if you use lot of joins in SQL affect your performance .

Answer 4

Yes.

But the biggest issue is: HOW are the tables joined. Suppose you had a query like:

select book.title, chapter.page_count
from chapter
join book on book.bookid=chapter.bookid
where chapter.subject='penguins'

The query would probably read the Chapter table first looking for matches on 'penguins', then join to Book. If Bookid is the primary key of book, or at least is indexed, this would be very fast. But if not, then we would have to do a full-file sequential read of Book. Depending on the engine and other factors, we might have to re-read the entire Book table for each chapter record found. That could take a long long time.

If you join three tables and both the joins require full file reads, you could be in a world of hurt.

Joins always cost you something. But joins that require full-file reads, especially multiple full-file reads, cost a lot. Some database engines mitigate this cost by recognizing it's happening and can load a table into memory and re-use it, generally doing some kind of hash search against it. This is still expensive, but not quite as bad.

Learn to read an Explain plan. These can help a lot in analyzing your queries, figuring out where they're bad, and cleaning them up. Personally, unless a query is obviously simple, like "select whatever from table where primary_key=whatever", I check out the explain plan just to be sure.

Answer 5

Using lots of joins can slow down retrieval performance (though with proper indexing, the penalty is often a lot less than people think -- measure first).

However, people tend to forget that removing the joins often means 'denormalizing' the data, which then incurs costs when the data must be modified. In particular, enforcing the constraints which a fully normalized schema enforces automatically in a schema which is denormalized can be hard. Because it is hard, it is often not done. But when the constraints are not enforced, the data becomes unreliable, and there's one thing worse than (slightly) slow select operations that return the correct answer, and that is fast select operations that return wrong or confusing answers.

If the DBMS is read-mainly - that is, data is written once and seldom if ever modified, then you can consider whether the performance benefit from denormalization makes the risks of inaccurate data creeping into the database acceptable. If the data is mission critical and often updated, then the risk of inaccurate data is usually too serious to be acceptable.

But, as they say, YMMV.