Multiple Joins or Simple Readable Database Calls

Question

Curious how other developers feel about this. I come across some pretty nasty sql joins, as we all do I'm sure. I would prefer making more database calls for the sake of readability and simplicity rather than have a large SQL statement to maintain; about 3 - 4 joins. For the performance argument, I will give up a little performance for readability.

For example: make the first call, filter the data, and then query the second table, and so on

What is your preference?

Answer 1

I often see facepalm-worthy code that fetches a result set and loops over it, executing another SQL query against a referenced table for each row. Why? "Because joins are bad."

For example (pseudocode):

SELECT * FROM Users;
for each user row {
    SELECT * FROM Orders WHERE Orders.user = $user;
    for each order row {
         SELECT * FROM LineItems WHERE LineItems.order = $order;
         for each lineitem row {
             SELECT * FROM Products WHERE Products.id = $lineitem;
         }
    }
}

If you design tables and indexes well, joins are an efficient way of filtering data within the RDBMS, and returning a small result set. Certainly more efficient than doing the equivalent work in application code.

Answer 2

In my experience, larger queries give SQL Server more opportunities to get it wrong. Using smaller queries can help SQL Server use a good query plan. For example, in your development environment, SQL Server might perform a huge query exactly in the order you wrote it. Later, when the code migrates to production, suddenly the data is much different. SQL Server might make a wrong assumption and choose to start the query in the middle, and suddenly you end up with crazy joins that create a billion temporary records that get spooled to tempdb.

Performance of complicated queries against huge databases varies. Sometimes, having a complicated query performs better, because it processes the data only once. Sometimes, separating steps is both faster and more readable, because you can ensure the results of the first query limit the data to a small set of rows before performing the rest of the steps.

I try to use separate queries for separate logical steps. I'll document each step with comments so I can more easily remember what it was trying to accomplish. For example:

1. Do some pre-processing to figure out what the user wants
2. Gather the data, filtering to the user's selection
3. Fill in some date-based data from some pricing table
4. Drop a few records that are not relevant because of some rare condition
5. Fill in some fields for reporting purposes
6. Return the results

If you are joining to multiple levels of sub-queries, as in Bill's example, then breaking apart the sub-queries into separate steps can improve performance.

Also note that your plan may incur more I/O as you create, populate, index, and select from temporary tables.

And finally, if it ain't broke-- don't fix it!

Answer 3

If it's just 3-4 simple joins, I probably wouldn't fret it too much; that isn't much at all in any reasonably large database. I've seen SQL queries that approach that number of dozens of joins, with subjoins to subjoins - now that is when it starts getting unwieldy! Usually there is a good reason for almost each and every one of them when taken in isolation, but at some point it went from being reasonable to unreasonable to sheer madness with "just one more" condition or field needed. At some point you need to consider refactoring the query (when you find that in order to achieve reasonable performance, the join ordering becomes significant or query hints are needed, that's usually a big warning sign), and breaking it into multiple smaller queries, but unless those joins are truly horrendous, I don't think you are at that point just yet.

The single most important rule though is to actually try it. Look at the execution plan. Is the database server making the most efficient use possible of indexes, or can you perhaps add a filtered index on some table that will help performance without hurting too much in other places? Rewrite the query and compare the execution time, I/O requirement and execution plan. Does it actually help? Also do keep in mind that in any non-trivial query, by rewriting it (even if it seems "simple"), you run the risk of introducing a bug.

Answer 4

Pro tip : SQL queries can be commented to help understand how they work. The same tip applies with regular expressions.

If you think multiple queries are better than one big (well written) one, you should try it with a RDMS server which is not running on the same machine than your application server.