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.
2
“Don’t worry. Be happy.” Pretty much any way you approach this problem is going to have adequate performance for small through medium sized CMSs.
Diving In
Do you really need to query for each content item? CMSs like WordPress often store their pages in HTML format. Pages may reference images, audio, video, and other multimedia, but they usually do so from the HTML, which means that when displaying the page, one never really needs to query for the associated references.
In the naïve case, content items are simply loaded, based on independent HTTP requests, from the local file system. In more sophisticated setups, multimedia is loaded from a separate “static” content server or (better yet), a content delivery network.
Probably every web server on the planet has features or add-ons to support this kind of “split serving,” and more performance-oriented servers like nginx eagerly support this model (along with extensive in-the-field experience being deployed alongside other accelerators such as caching servers like Squid and Varnish.
When editing a page, there is a greater likelihood one would want or need to query all associated content. That is the one case where querying multiple times might make more sense. But, this is much more likely to result in 2 queries than 11–one for the page, and then one omnibus query that returns 10 results to a query for all associated content.
There are a number of schemes for accomplishing this in SQL, including storing a list of content ids in each page, storing the “associated page item” in each content item, or having a page-id to content-id translation table. All of those options are used in practice in one CMS or another. Modern relational / SQL databases will be perfectly performant doing such queries occasionally. Indeed, while 11 hits on every page load might be onerous, doing 11 queries on every page edit (the worst possible case) is probably not going to be that big a deal. How often are edits done? Not very often, even in CMSs of thousands or tens of thousands of entries. If you’re planning for massive (multi-million- or multi-billon-item) CMSs, we could talk about special indexing and optimization–but for even quite large production systems, 2 queries or even 11 queries per edit are not going to “break the bank.”
And the reality is, even in CMSs that try to keep an in-the-database link between pages and content items, the links are usually incomplete. HTML links to external resources are so common, and so few user organizations are truly disciplined about keeping all content links fully described in-the-database. So even in those “worst cases,” the number of in-the-database associated content items is less than you think.
For related sets of information like this a single “jumbo” call to the DB server is preferable. Although the total data returned is the same as for 11 individual calls, all those network packets, connection set up and lock management add up over time.