Find out where a store procedure is called

Joel Yourstone
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
  • Magnus Rahl
    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
  • valdis iljuconoks
    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
  • Quan Mai
    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
  • Joel Yourstone
    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
  • Quan Mai
    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
  • Erik Norberg
    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
  • Quan Mai
    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
  • Quan Mai
    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
  • Joel Yourstone
    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
  • Quan Mai
    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
  • Quan Mai
    Member since: 2011
     

    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

    #182698 Sep 28, 2017 8:27
  • Quan Mai
    Member since: 2011
     

    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.

    #182908 Sep 29, 2017 15:27