Blog

Developers often use tools, like Entity Framework or NHibernate, to make it easier to work with the database. These tools sometimes cause problems for developers and DBAs alike. In this talk, we’ll talk about the terminology and techniques used with an ORM. We’ll also uncover ways to help DBAs work with developers, detect problematic queries, and improve performance in both the database and the application.

This 30 minute session is for DBAs who are unfamiliar with ORMs and who aren’t sure where to start.

Using ORMs with Stored Procedures

Most ORMs can use stored procedures instead of writing their own SQL. This is important when data is more complex than a single object to table mapping.

The n+1 Selects problem

The n+1 selects problem frequently occurs when displaying a list of items to a user. This can happen through a combination of looping in application code and lazy loading (only loading data when it’s explicitly needed). The ORM will generate multiple calls to the database, one for each object that’s used. Solving this problem depends on the particulars of the ORM that you’re using.

Query Cache Pollution

ORMs can cause the same problems that ad hoc SQL can cause – many plans will be generated and consume SQL Server’s memory. Grant Fritchey (blog | twitter) documented how this problem appears in NHibernate and how to detect it in NHibernate Recompiles and Execution Plans. Solutions abound and there’s an excellent write up of the history of this problem in NHibernate Parameter Sizes Controversy.

General ORM Links

Links to Common ORM Tools

  • NHibernate – a commonly used .NET ORM that is based on Hibernate
  • Hibernate – the grandaddy of Java ORMs and the inspiration for many others.
  • LLBLGen Pro – this is the Cadillac of ORMs. If there’s something you wished an ORM could do, odds are LLBLGen Pro can do it. It even provides tools to generate code for other ORMs.
  • NHibernate Profiler – it’s a profiler that developers can locally to grab only their own queries to the database.
  • Ruby on Rails – Ruby on Rails uses an ORM named ActiveRecord to do the heavy lifting.
↑ Back to top
  1. Hi Jeremiah. I enjoyed your appearance on RunAs (http://www.runasradio.com/default.aspx?showNum=247).

    You mentioned that Jamie Thomson had written an e-book on Datadude, but I’ve been unable to find this, do you have a link to this that you could get the RunAs guys to post on the episode show notes? (or here?).

    Many Thanks.

  2. This is a great post, and hopefully it will help both devs and DBAs in the ORM battles that often occur.
    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php