4

Recently, our company is focusing on performance of the application we are developing for long time. One thing we noticed during performance test, certain methods are making so many database calls (over 500 queries)

Then this brings a question like, which methods are doing so many calls and how should be prioritize which method to refactor first. When we initially try to refactor some of those methods, we observed that it is requiring a lot of effort to reduce the number of round-trips. The reason is our data access layer is pretty much depending on NHibernate ORM framework and we figured out that we have totally misused Lazyloading configuration from the beginning of the development. That is why number of round-trips are huge and impacting the performance a lot. And just correcting Lazy Loading configuration creates a lot of regression.

Thus, we somehow have to figure out a way to collect number of database call per http request. I have seen some tools like Application Insight or AppDynamics provides overall result for all Dependent calls. But I am just wondering is there a way to collect these traces differently than using those frameworks ?

For instance, every time http request is made, can we have attribute in the controller that whenever ExecuteQuery() or SqlDataAdapter.Fill method is called within the call stack of the method, can it increase the counter. I am looking for a solution something like this.

Any help is greatly appreciated.

Thank you in advance for all suggestions.

telli
  • 363
  • 3
  • 14
  • http://stackoverflow.com/questions/7667092/nhibernate-database-call-count – Paul Abbott May 04 '17 at 00:24
  • Besides what @PaulAbbott linked to, you can also use tools such as [SQL Profiler](https://msdn.microsoft.com/en-us/library/ff650699.aspx) or SSMS – Mad Myche May 04 '17 at 01:02
  • Hi @PaulAbbott, I checked the link. NHibernate logging is providing an statistics not per http request. It gives me overall information across many methods. And also I am not looking something specific to NHibernate. Although our data access layer mainly uses the NHibernate for data manipulation, we sometimes used Ado.Net library directly to communicate with database. Hence, I am looking for more low level solution which can collect stats from SqlCommand, SqlConnection and SqlDataAdapter level – telli May 04 '17 at 01:19
  • @MadMyche, we are aware of SQL profiler and extended events. Each developer can use when he/she develops a new method or refactor an existing method and check the number of query executed in that specific method. But I am more looking for something that we can set it up for testing environment and run all methods and breakdown the number of calls per method to be able to prioritize refactoring strategy. – telli May 04 '17 at 01:19
  • If you aren't using the `Application Name` property of the SqlConnection object, you can leverage that with method names and track that on the DB end. – Mad Myche May 04 '17 at 03:24
  • are this calls Async? can we rely on the fact that they are being executed on the same thread? – Ori Refael May 04 '17 at 08:46
  • @OriRefael, NHibernate does not support async currently. telli, you may be interested about how reducing lazy-loading calls with NHibernate, since this is fairly easy and safe to achieve if batching of lazy-loads is not enabled: just enable it. I give some details in my answer to another question [here](/a/36070727/1178314) – Frédéric May 04 '17 at 14:10
  • @Frédéric, The problem we are facing is not about using LazyLoading. As a design mistake, in the beginning of the project almost all of the references in each entity have been configured as Not.LazyLoad(). So we are observing Select N+1 problem a lot. We are aware that we should have used LazyLoad which is default behavior. – telli May 05 '17 at 01:18

1 Answers1

2

Use Stackify Prefix for this kind of things:

https://stackify.com/prefix/

View SQL queries: Including SQL parameters, affected records and how long it took to download the result set.

Orlando Helmer
  • 403
  • 1
  • 4
  • 14