Databases
StackOverflow2013
Postsdbo
POTracing ORM performance
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTracing ORM performance
    primarykey
    data
    text
    <p>This isn't a question of "which is the fastest ORM", nor is it a question on "how to write good code with ORMs". This is the other side: the code's been written, it's gone live, several thousand users are hitting the application, but there's a perceived overall performance problem. A SQL Profiler trace can only be ran for a short amount of time: 5 mins gives several hundred thousand results.</p> <p>The question is simply this: having used SQL Profiler to narrow down a number of slow queries (duration greater than a given amount of time), what techniques and solutions exist for tracing these SQL queries back into the problematic component? A releated question is that if a specific area is slow, how can we identify the SQL that this area is executing so it can be suitably filtered in SQL Profiler? </p> <p>The background to this is that we have a rather large application with a fairly complex table structure, and is currently based around data-access via stored procedures. If a SQL performance problem arises, it's usually case of pulling out SQL profiler, find out if there's anything slow (filter by duration) or if a the area being complained about is slow (filter by stored procedure), and tune the stored procedures (or the schema - through indexing). </p> <p>Now there's a push to move our code over from a mostly-sproc solution to a mostly-ORM solution, however the big push against the move is how performance problems, if they arise, can be traced back to problematic code. I've read around and it seems that more often than not, it may involve third-party tools (ORM tracing utilities like NHProf or .NET tracing utils like dottrace) that we'd need to install on the server. Now whether additional tools can be installed on a live environment is another question, so if things like this can be performed without additional tools, then that may be a bonus. </p> <p>I'm mostly interested in solutions with SQL Server 2008, but it's probably generic enough for any RDBMS. As far as the ORM tech, on this I have no specific focus as nothing's currently in use, so be interested to hear how techniques differ (or are common) twixt nHibernate, fluent-nhibernate and Entity Framework. Other ORMs are welcome though if they offer something else :-)</p> <p>I've read through <a href="http://weblogs.asp.net/fbouma/archive/2011/01/08/how-to-find-and-fix-performance-problems-in-orm-powered-applications.aspx" rel="nofollow noreferrer">How to find and fix performance problems (...)</a>, and I think the issue is simply the section on there that says "isolate". A problem that is easily reproducible <strong><em>only</em></strong> on a live system is going to be difficult to isolate. The figures I quoted in para 2 are figures the types of volumes that we can get from a profile as well...</p> <p>If you have real-world experience of ORM tracing on live, so much the better :-) </p> <p><strong>Update, 2016-10-21</strong>: Just for completeness, we eventually solved this for NHibernate by writing code, and overriding NHibernate methods. Full details in this other SO question I asked: <a href="https://stackoverflow.com/a/22323025/130352">NHibernate and Interceptors - measuring SQL round trip times</a>. I expect this will be a similar approach for many different ORMs.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USChris J
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTracing ORM performance
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTracing ORM performance
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POTracing ORM performance
      1. USHLGEM
      1. VTFavorite
    1. COAre you familiar already with the various DMVs and reports that show top query by duration, CPU, IO etc. based on execution plan stats rather than profiling? Also what version of SQL Server? If 2008 there is the management data warehouse that may help (though I guess with non parameterised queries issued by an ORM you may end up with lots of similar queries and plans making aggregating the results difficult)
      singulars
      1. POTracing ORM performance
      1. USMartin Smith
    2. COInteresting question, I look forward to learning something about this subject since I don't have an answer for you.
      singulars
      1. POTracing ORM performance
      1. USHLGEM
    3. CO@Martin -- sorry, 2008 (question edited). Now you mention the DMVs, that does ring bells, will investigate. The app at the moment is based on SQL 2000, but the next version's being elevated to 2008. Not *too* bothered about 2000 solutions. Though once we have the query, how do we trace that backwards to the module that's causing the problem via the ORM layer?
      singulars
      1. POTracing ORM performance
      1. USChris J
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload