November Happy Hour will be moved to Thursday December 5th.
AI OnAI Off
Build index performance issue
Hi,
It seems that you can benefit from this blogpost: http://vimvq1987.com/2017/01/speed-catalog-entries-indexing/
Yes, it's mine :)
Regards,
/Q
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
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.
http://www.screencast.com/t/VeAx3cBV
Logs does not contain any errors.
Index rebuild works fine. Its progress starts immediately.
Should I make some additional configuration in Commerce Manager?