November Happy Hour will be moved to Thursday December 5th.
AI OnAI Off
Find out where a store procedure is called
It is called when (re)building the index for a search provider, if that is any help.
Sep 27, 2017 10:59
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.
Sep 27, 2017 11:45
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.
Edited,
Sep 27, 2017 11:50
Nice, thanks for the tips! Will likely create a bug report if we find a "missing" index :)
Sep 27, 2017 11:58
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)
Edited,
Sep 27, 2017 12:24
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.
Sep 27, 2017 13:55
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.
Sep 27, 2017 14:04
(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)
Edited,
Sep 27, 2017 14:04
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
Sep 28, 2017 8:05
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.
Sep 28, 2017 8:25
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
Sep 28, 2017 8:27
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.
Sep 29, 2017 15:27
Hi!
Is there any way of finding out where in the code a stored procedure is used in Commerce?
Background to this is that we've found an expensive store procedure call doing sql server profiling and we want to understand why it is called and if we can maybe migitate the performance impact by adding some index. The store procedure in question is ecf_CatalogEntrySearch_Init and of course we could read it and try to understand where it would be used, but it would be easier if we could somehow see which methods/classes that are calling it. Anyone has any tips?
(The excecution plan in SSMS shows that ~98% of the cost of the queries is in a clustered index scan so we will most likely add an index to it to make it less expensive)