Find out where a store procedure is called

Member since: 2016

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)

#182650 Sep 27, 2017 10:47
  • Member since: 2008

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

    #182651 Sep 27, 2017 10:59
  • Member since: 2011

    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.

    #182656 Sep 27, 2017 11:45
  • Member since: 2011

    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.

    #182657 Edited, Sep 27, 2017 11:50
  • Member since: 2016

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

    #182658 Sep 27, 2017 11:58
  • Member since: 2011

    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)

    #182659 Edited, Sep 27, 2017 12:24
  • Member since: 2012

    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.

    #182664 Sep 27, 2017 13:55
  • Member since: 2011

    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.

    #182666 Sep 27, 2017 14:04
  • Member since: 2011

    (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)

    #182667 Edited, Sep 27, 2017 14:04
  • Member since: 2016

    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

    #182696 Sep 28, 2017 8:05
  • Member since: 2011

    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.

    #182697 Sep 28, 2017 8:25
First   1 2   Last