Measuring performance for Single or multiple queries when you have large number of bridge tables

Question

i asked this question around a single join or multiple (select n + 1) queries

i wanted to find out if this was the same if you had many to many relationship and a lot of bridge tables

for example, here are my tables:

Table: People (id, first, last, age, phone, etc . .)
Table: Roles (id, name)
Table: PeopleRoles (id, personID, roleID)
Table: Skills (id, name)
Table: PeopleSkills (id, personID, skillID)

so if i did a join, i would get multiple rows for each person (assuming a person has many roles or multiple skills).

assuming there are many more tables like this with many relationships, which is faster:

Option 1:

1. Select * from applications
2. then loop through each application and run a Select * from Roles where applicationID = id inner join

Option 2:

or trying to create one massive query that returns a large result set and i then need to normalize it when i translate this into data structures (as i will get the same application in multiple rows of course.

Answer 1

Option 2 is almost always going to be faster, as long as you don't have that many duplicate values. It really depends on the size of the redundant data -- but having to do multiple select statements really is the kiss of death when you have a large number of rows because it has to do looping and can't do any more sophisticated type of join on the server side, but perhaps more importantly -- if your logic is making many database calls, those are happening across process/network boundaries which in and of itself is an order of magnitude slower.

There are other ways you could optimize the results if it really, really mattered -- you could have the database create some XML that could be more efficiently serialized between the data tier and your logic tier, but that's a LOT of work and is hardly what anyone would call cross-platform or generic.

Of course IMO this all begs the question... Why not just use an ORM like Linq to Entities or Linq to SQL or (N)Hibernate? Why reinvent this wheel?