London Dev Meetup Rescheduled! Due to unavoidable reasons, the event has been moved to 21st May. Speakers remain the same—any changes will be communicated. Seats are limited—register here to secure your spot!
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?