by thorhalvor

ORM & Linq – Know your queries and indexes

In many projects I have been working on we don’t have database administrators (DBAs). At least not the kind of DBAs that optimize the queries/views etc. It’s mostly up to us developers. Nowadays we abstract database using O/R Mappers like nHibernate and Entity Framework. Doing this we don’t have to think about if we should use an INNER or LEFT OUTER JOIN but we can just use the Query-objects given by the framework.

This blogpost’s main focus is not database optimization specifically, but more the importance of thinking and doing something about it even when using ORMs.

When creating Views in the database or writing SELECTs in general most developers remember to add INDEXES on the WHERE clauses, but when going through a O/R Mapper we sometimes forget. We have abstracted all of the database and our mindset is not “there”.

Some examples:

var customer = _session.Query<Customer>()
                .Where(x => x.Email == “myemail@tempuri.org”);

 

or a more advanced one:

var linq = (from product in session.Query<Product>()
join category in session.Query<Category>()
on product.Category.Id equals category.Id
where category.CategoryName == "Condiments"
            && !product.Discontinued
select product).ToList();

The first example above is very simple and normally would take milliseconds to run. But what if we had millions of records in the database, then the query might take seconds. Particularly if we don’t have the Email column indexed in the database. In the second example maybe you don’t have a clue what kind of SELECT that was actually executed and then its hard to know what to optimize in the SQL Server…

Last example:

var blogpost = _session.Query<BlogPost>()
                .Where(x => x.Content.Contains("nHibernate"));

 

What is so special about this example? I guess you have used the Contains method hundred of times when working with Strings. But in this case it is on the ORM and the SELECT that is executed is most likely a ‘LIKE ‘”%nHibernate%’” who might trigger full table scan. (Depends on the scenarios, but a custom Full-Text Search might be vice here..)

To find out the SELECTs really executed on the SQL server you might have used the SQL Service Profiler and Database Engine Tuning Advisor. But most times we don’t. If you are using NHibernate they log all the queries on the DEBUG level using log4net.  But another perfect tool for this profiling is Ayendes NHProfiler/EFProfiler ++. The tools won’t just display the SELECTs but also help you doing them better.

As he writes on Efprof.com

“Entity Framework Profiler is a real-time visual debugger allowing a development team to gain valuable insight and perspective into their usage of Entity Framework. The product is architected with input coming from many top industry leaders within the OR/M community. Alerts are presented in a concise code-review manner indicating patterns of misuse by your application. To streamline your efforts to correct the misuse, we provide links to the problematic code section that triggered the alert.”

So, when you are abstracting the database using various frameworks you should always know and care about your queries. If you don’t, you probably get issues in the production environments.