When you look at a simple page in our world of TNG, you see lots of contents. The articles themselves, the header image, the results of a query, everything is a content. Even a simple page can come with a several dozen of contents and sometimes a plugin has to work with hundreds, maybe thousands of contents in a single request. The latter is not a common scenario, but we should prepare for that, too. Of course we made strong efforts to design a really good cache but in some cases - for example handling newsletter subscribers - loading lots of nodes just can't be avoided.
I've made a simple test: generated 25000 nodes into a single folder. Cleared the cache and loaded them back again: it took 12 minutes...terrible.
After some debugging I realised that when we load nodes, in the SQL stored procedure we use table variables. It works fine since we have a couple of live portals using TNG but in this unusual case the performance is very poor. So I changed that procedure: in SQL Server 2005 I could use the XML type for passing the Node IDs that we should load, and query directly that XML variable, instead of using temp table or table variable. The result is impressing.
| COUNT OF NODES |
| LOAD DURATION |
| 25000 nodes with SystemAccount and table variable |
| 12 minutes |
| 25000 nodes with XML select |
| 21 sec |
| 25000 nodes with SystemAccount and XML select || 9 sec |