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.

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?

Feb 01, 2017 10:12
  • 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.

    http://www.screencast.com/t/ffJvJrDE

    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
First   1   Last