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?
It seems that you can benefit from this blogpost: http://vimvq1987.com/2017/01/speed-catalog-entries-indexing/
Yes, it's mine :)
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%.
How many rows do you have in ApplicationLog table?
Can you profile the site during the indexing? Which action takes the most of time?
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.
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.
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.
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!
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.