performance - single join select vs. multiple simple selects

Question

What is better as far as performance goes?

Answer 1

There is only one way to know: Time it.

In general, I think a single join enables the database to do a lot of optimizations, as it can see all the tables it needs to scan, overhead is reduced, and it can build up the result set locally.

Recently, I had about 100 select-statements which I changed into a JOIN in my code. With a few indexes, I was able to go from 1 minute running time to about 0.6 seconds.

Answer 2

A single join will usually outperform multiple single selects. However, there are too many different cases that fit your question. It isn't wise to lump them together under a single simple rule.

More important, a single join will usually be easier for the next programmer to understand and to revise, provided that you and the next programmer "speak the same language" when you use SQL. I'm talking about the language of sets of tuples.

And equally important is that database physical design and query design need to focus first on the questions that will result in a ten for one speed improvement, not on a 10% speed imporvement. If you were doing thousands of simple selects versus a single join, you might get a ten for one advantage. If you are doing three or four simple selects, you won't see a big improvement one way or the other.

Answer 3

It all depends on how the database will optimize the joins, and the use of indexes.

I had a slow and complex query with lots of joins. Then i subdivided it into 2 or 3 less complex querys. The performance gain was astonishing.

But in the end, "it depends", you have to know where´s the bottleneck.

Answer 4

Do not try to write your own join loop as a bunch of selects. Your database server has many clever algorithms for doing joins. Further, your database server can use statistics and estimated cost of access to dynamically pick a join algorithm.

The database server's join algorithm is -- usually -- better than anything you might concoct. They know more about physical I/O, caching and what-not.

This allows you to focus on your problem domain.

Answer 5

If you are using SQL Server (I am not sure if this is available with other RDBMSs) I would suggest that you bundle an execution plan with you query results. This will give you the ability to see exactly how your query(s) are being executed and what is causing any bottlenecks.

Until you know what SQL Server is actually doing I wouldn't hazard a guess about which query is better.

Answer 6

If your database has lots of data .... and there are multiple joins then please use indexing for better performance.

If there are left/right outer joins in this case , then use multiple selects.

It all depends on your db size, your query, the indexes (which include primary and foreign keys also) ... One cannot reach on conclusion with yes/no on your question.

Answer 7

As has been said before, there is no right answer without context.

The answer to this is dependent on (from the top of my head):

• the amount of joining
• the type of joining
• indexing
• the amount of re-use you could have for any of the separate pieces to be joined
• the amount of data to be processed
• the server setup
• etc.

Answer 8

One thing to consider besides what has been said, is that the selects will return more data through the network than the joins probably will. If the network connection is already a bottleneck, this could make it much worse, especially if this is done frequently. That said, your best bet in any performacne situation is to test, test, test.