Speeding up item loading in Sitecore

Execution plan analysis

An execution plan for selecting an item by ID would have:

1. IndexSeek for field-related tables, and further RID lookup ( or DAC_Clustered Key lookup).
2. An ugly execution plan =\
3. A long SQL statement ( at least 1000 characters )
4. A sort operation (could be moved to Sitecore) costs ~10 %

Taking into account:

1. Sitecore fetches items by ID only ( neither language, nor version is applied as a filter )
2. Sitecore issues far more SQL read than write statements
3. Relational database resources are typically more expensive in terms of scalability

There seems to be some room for improvements.

Possible improvements

• Changing index types to clustered for Unversioned, Versioned, and Shared fields tables
• NonClustered Index Seek with Key lookup inner join would be replaced with clustered index seek
• Execution plan becomes much straightforward
• Reworking items table indexes
• Making ParentID index clustered to order data
• OR making ID clustered index to enable fastest item fetch
• Moving sort operation to Sitecore side
• Using View instead of firing long ugly SQL all the time

Query plan after modifying indexes

Concerns

• Inserts would take more time
• The difference in single item save operation would not be noticeable for Content Editors, since a vast majority of total item save time is spend in 'item:saved' event handlers.
• Publishing would be slower
• Publish reads an item from both source and target databases first before making a decision whether to insert/update/remove item version. 
• Having much faster reads could even improve the overall publishing speed.

Conclusion

A series of tests are required to get the understanding if it is worth to introduce those improvements in particular case.

In the next chapter we`ll take a look on basic technique how to perform SQL Profiling of Sitecore GetItem opearition.