Sql – ORM and database indexes

hibernatenhibernateormsql serversql-server-2005

What approach do you have towards creating and maintaining database indexes when using ORM such as NHibernate/Hibernate.

Since the ORM is generating the queries, are there any tools you could recommend that could analyze query plans of those and suggest the kind of indexes that should be created?

My current approach is … wait until something works slow and then find the slow query and optimize it … but this is sort of lame isn't it? My goal is not to end up with tens or hundreds of indexes of which nobody knows which are actually being used by the system and which aren't. So again index maintenance.

My environment is NHibernate + SQL Server 2005.

Best Answer

I find that the columns that need indexing are typically "obvious". By that I mean if you create queries like "select p from Person p where p.surname = :surname" then whatever column surname refers to needs an index.

Likewise every foreign key should be indexed.

And no I don't wait until performance is actually a problem. Indexes are just something I do right from the start.

Oh the other thing I wanted to add was that most (if not all) ORMs have the ability to turn on statement logging. These often aren't particularly readable (single line, table names of t0, t1, t2, etc) but this could tell you what queries were run and how often.

Related Solutions

The “N+1 selects problem” in ORM (Object-Relational Mapping)

Let's say you have a collection of Car objects (database rows), and each Car has a collection of Wheel objects (also rows). In other words, Car → Wheel is a 1-to-many relationship.

Now, let's say you need to iterate through all the cars, and for each one, print out a list of the wheels. The naive O/R implementation would do the following:

SELECT * FROM Cars;

And then for each Car:

SELECT * FROM Wheel WHERE CarId = ?

In other words, you have one select for the Cars, and then N additional selects, where N is the total number of cars.

Alternatively, one could get all wheels and perform the lookups in memory:

SELECT * FROM Wheel

This reduces the number of round-trips to the database from N+1 to 2. Most ORM tools give you several ways to prevent N+1 selects.

Reference: Java Persistence with Hibernate, chapter 13.

Sql-server – the difference between varchar and nvarchar

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benefits of full Unicode storage.

Best Answer

Related Solutions

The “N+1 selects problem” in ORM (Object-Relational Mapping)

Sql-server – the difference between varchar and nvarchar

Related Topic