It is called when (re)building the index for a search provider, if that is any help.

I did a trick back in old days - open whole bin/ dir in dotPeek ir similar tool and use "go to string" feature. it finds all occurrences of particular string (simialr as find usages for code). that was nice.

One dirty trick is to remove that SP and see what code called it from the stacktrace.

If it is slow, just reachout for us and we will look into it.

Nice, thanks for the tips! Will likely create a bug report if we find a "missing" index :) 

I think I know what Clustered Index Scan you're mentioning. The problem is that it's very ineffective to have that index. Also ecf_CatalogEntrySearch_Init  should only be called when you (re)build the index, so it's once every day (or even less if you have the eventual indexing processor on)

That procedure is called every time the commerce indexer is run, for both full and incremental indexing jobs, also it is called once for every catalog in your commerce database.

Yes, as I said, when you build/rebuild the index. I don't expect you to build the index all the time? Normally it's done via a scheduled job daily. 

But if you have the eventual indexing that would not be a problem.

(I remember that if the catalog is reindexed then it does not use this SP - because there is no point searching for "changed" entries if you are indexing everything. I updated my posts to reflect that)

Hm, that doesn't look to be the case for us, given the number of times it's run during a day and its parameters. The SP also runs once per catalog, so every time it's scheduled to run, it runs X many times, all of which are as expensive, regardless of catalog size

If you can grab the parameters being passed in, the number of calls, or even better, the execution plans, we can perhaps take a look.

How many catalog do you have? Yes it is run per catalog, and it looks into several 'shared tables' like ApplicationLog, CatalogEntry and CatalogContentEx so the catalog size might play a smaller role here.

One way to reduce the time is to archive the ApplicationLog table, so if you don't care about auditing changes which are more than X days, you can move them to another table and let that ApplicationLog small

I'm off topic here, again: I think this is a quite common issue, and we'll look into improving it. I have some ideas of how can it be done, but it will take time to implement (and we have other works with high priority as well) - so it'll not be available soon-ish. But it'll eventually be addressed.