Friday, December 16, 2016

Check Sitecore Items having more than 100 child Item through Sitecore Database Query

As Sitecore recommendation, the number of items under any given node, 100 or less is considered the best for performance and usability. If items are increasing periodically then we should plan accordingly. 

For example, a Blog folder where Blog item increasing on a daily basis, we should create day/month/year format folder structure or use an item bucketing to store the large number of items.

But in real life scenario, sometimes we don't follow the Sitecore best practice and dump large number of items under one single node.

I involved one of the performance tuning task in my project and want to generate the report to get all the Sitecore items that having more than 100 child items, The Quick solution came in my mind is a direct database query in the Sitecore database, however there are also other good option to generate this report like Sitecore API, Powershell script, Content search APIs Etc. 

But I didn’t dig into these approaches, as I need to provide this report quickly, and I appreciate if somebody provides the other way to get this report. 

Below is the direct SQL query to get all the Sitecore items that have more than 100 immediate child items,

Select Master or web database,

And below is the result.

OMG, I shocked to see this magical figure, this is not the item bucketing folder, this is normal Sitecore item that has more than 40k Immediate Child Item,

You should also run this script in your Sitecore database and find some magical figure. :)

I ran the same query in the vanilla Sitecore 8.2 version and found only one entry that has more than 100 child item, that’s why we love #sitecore.

I hope this article will help you.

Happy sitecoring J