As an example for this question, consider a content management system that has a content database table which contains things like the page title and location as well as the content itself which could be quite a large amount of text. There is a second database table with details of resources (such as images and files) that are associated with a content item. Each page in the content management system may have one or many content items, each with it's own resources.
At present I am retrieving a list of content items and then for each content item doing a second query to retrieve the list of related resources. On a page with 10 content items, this means 11 queries.
I could do a join and retrieve all content and related resources in one query, but it concerns me that where there are multiple resources for a content item, the content item will be duplicated and, because the content item might be large, this might be very inefficient with so much duplicate data.
Another option is to have a stored procedure that returns two recordsets. The first recordset contains the content, and the second recordset contains the related resources for all of the content items in the first recordset, which is separated out by the receiving server.
So the question is what is generally the most efficient way to retrieve data in this situation?
I know that performance testing is often the answer but for this question I was looking for best practise type advice or alternatives that I may not have thought of.