Recently I worked on a support case where a customer reported deadlocks and timeout exceptions on queries to a specific table – NodeEntryRelation. Yes, it was mentioned in this post. However, there is more to it.
Keeping the indexes healthy definitely help to improve performance and avoid deadlocks and timeout exceptions. However it can only work to a limit, because even if the indexes are in their perfect state (the fragmentation level is 0%), the query will still take time.
Looking in the query we talked about – `ecf_Catalog_GetChildrenEntries` – what does it do. It lists the entries which are direct children of a catalog. So normally entries belong to categories (nodes), but it’s possible (Although not recommended) to have entries that belong directly to a catalog.