SQL Join vs Separate Query in Code without Join - Performance

Question

I would like to know if there's a really performance gain between those two options :

Option 1 :

• I do a SQL Query with a join to select all User and their Ranks.

Option 2 :

• I do one SQL Query to select all User
• I fetch all user and do another SQL Query to get the Ranks of this User.

In code, option two is easier to realize for me. That's only because the way I design my Persistence layer.

So, I would like to know what's the impact on performance. After what limit I should consider to take Option 1 instead of Option 2 ?

Answer 1

Generally speaking, the DB server is always faster at joining than application code. Remember you will have to do an extra query with a network round trip for each join. However, if your first result set is small and your indexes are well tuned, this model can work fine.

If you are only doing this to re-use your ORM solution, then you may be fighting a losing battle. I have invariably found that I need read-only datasets that can only be produced with SQL, so I now use ORM for per-object CRUD operations and regular SQL for searches, reports, aggregates etc.

Answer 2

If ranks are static values, consider caching them in your application.

If you need users frequently and ranks only rarely, consider lazy-loading of ranks. (e.g., separate queries, but the second query gets used only occasionally).

Use the join if you're always going to need both sets of data, and they have to be current copies of the database.

Prototype any likely choices, and run performance tests.

EDIT: Further thoughts on your persistence layer, because I'm facing this one myself. Consider adding "persistence-like" classes that handle joins as their basic query, and are read-only. Whether this fits your particular scenario is for you to decide, but a lot of database access for many apps is based on joins, which can be rather large and complex. If you can handle these in a consistent manner with your persistent, updatable objects, it can be a big win for your overall architecture. Conceptually, it's a lot like having a view in the database, and querying the view instead of writing a join, but you're doing it all in code.