Try our conversational search powered by Generative AI!

Build index performance issue


On our qa server there is a problem with index build via Commerce Manager. After starting its progress freezes at 0 % and only after significant amout of time (about 1 hour) progress starts updating and build index completes without any errors.

Logs does not contain any errors. 

Index rebuild works fine. Its progress starts immediately.

Should I make some additional configuration in Commerce Manager?

Feb 01, 2017 10:12


It seems that you can benefit from this blogpost:

Yes, it's mine :)



Edited, Feb 01, 2017 10:42

Thank you for the link. I've added unclustered index to ApplicationLog table as you described in the blog but it does not help. Build index still freezes at 0%.

Feb 01, 2017 14:53

How many rows do you have in ApplicationLog table?

Can you profile the site during the indexing? Which action takes the most of time? 

Feb 01, 2017 14:57

ApplicationLog table contains ~ 1300000 records (after adding index it takes 52 seconds to get all records for this table). Concerning profiling I need some time to setup and run it.

Feb 01, 2017 15:59

I've profiled application and it seems that problem is still with ApplicationLog table. As build index is running it selects 100 records per query sequentially and each such request takes about 2 seconds. So for table with 1 million records it will take about 10000 * 2 seconds ~ 6 hours to complete. Adding non-clustered index did not help a lot.

Edited, Feb 17, 2017 12:31

OK now I see your problem. The most taking time operation for you was to remove the deleted entries - it will try to get the deleted entries from Application and remove those from the index. It's a different problem in my blogpost as that index is for ecf_CatalogEntrySearch_Init - which to build up the list of modified entries. The index was for ecf_CatalogEntrySearch_Init, it does not help ecf_ApplicationLog because that SP needs a lot of columns in the result. Also, you have a lot of deleted entries since the last build, which is a bit unusual I would say.

So the solution can be: Instead of building index incrementally, you can try to reindex, which will not query ApplicationLog at all for deleted entries.

Feb 17, 2017 12:54

I've realized that ApplicationLog contains a lot of old data. After removing this old data build index started working quite fast (less than 1 minute). Thank you Quan Mai!

Feb 20, 2017 10:45

We decided to fix this issue. The fix is under review, but for my testing, removing 5400 deleted entries index, with 43k rows in ApplicationLog is almost instantaneous - so I think it will bring quite an improvement to your case. Stay tuned. 

May 03, 2017 15:18
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.