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

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.