A DBA’s Guide to ORMs Webcast

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.

• Using NHibernate with Stored Procedures
• Entity Framework – How to Define a Model with a Stored Procedure

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.

• Solving the Select N+1 Problem
• What is the N+1 Selects Problem?

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

• How to Fail at ORM
• Ted Neward’s essay The Vietnam of Computer Science
• Jeff Atwood’s follow up to Neward’s essay: Object-Relation Mapping is the Vietnam of Computer Science
• The Active Record Design Pattern – This pattern is used in most ORM frameworks

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.

Jeremiah Peschka

Jeremiah Peschka has worked as a database and emerging technology expert at Quest Software where he researched new trends and technologies in the world of data storage. Over the course of his career he’s worked with companies across many industries as a system administrator, developer, and DBA. He’s been involved with all aspects of application development and deployment. He likes cheesecake, coffee, and ice cream.

More Posts - Website

Follow Me: