Alex Rivera | Logout

SQL Join vs Separate Query in Code without Join - Performance

Asked 2009-12-10T19:19:31.727
14

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 ?

sql performance join
Edit
Report

1 Answer

14

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.

answered 2009-12-10T19:30:57.553

Your Answer