Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Aug 30 '11 at 4:57

This question came from our site for professional and enthusiast programmers.

10  
3 - 4 joins is by no means a large SQL Statement. Let the RDBMS choose the join strategy and do the filtering. See Joins are for lazy people? –  Martin Smith Aug 29 '11 at 17:36
1  
General good practice: try to use the minimum number of SQL queries in your application. Less queries is always a good thing. If I were in your case, I would stick with the joins.Better performance, less time communicating with the database. –  Jon Martin Aug 29 '11 at 17:37
    
Depending who looks at which pieces of code, you may be able to improve client-side readability by defining views for long SELECT queries. –  Andrew Lazarus Aug 29 '11 at 18:15
    
Actually, I just threw 3 - 4 out there. Purely hypothetical. Just thinking if we should refactor queries into small steps, just like we should/do with methods. –  Scott Radcliff Aug 30 '11 at 12:29

4 Answers 4

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.

share|improve this answer
    
Yeah, I hear ya. Inheriting an old code base can be frustrating and extremely difficult to wrap my head around sometimes. Looking for ways to make it more readable without refactoring too much. Very time consuming. –  Scott Radcliff Aug 29 '11 at 19:49
1  
...and then in the SELECT * FROM Products, you get a truly silly WHERE condition that could just as well have been evaluated way back in the Users SELECT. –  Michael Kjörling Aug 30 '11 at 8:50

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!

share|improve this answer
    
This is more a case of know your dbms. I don't know SQL Server, but if I found that with Oracle, I would say your statistics are off and you should focus your energy there. –  jasonk May 13 '12 at 22:51

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.