ORM vs parametrized queries if my only concern is speed of data access

Question

I work in an ASP.net/ SQL Server development environment. If my only concern is speed should I go with parametrized queries or should I use an ORM such as nHibernate or LINQ?

Answer 1

Most ORMs support parameterized queries, so no difference in that regard. I would implement using an ORM as much as possible, and only optimize by handcrafting SQL when you need to.

Understanding how the ORM works should help you build code that performs well. That way you can avoid obvious traps like calling a query repeatedly within a loop, e.g., when querying another table via foreign key.

Answer 2

The answer to this is really "it depends," and will require you to test it in your own environment to confirm, but in general I would say that using an ORM tool adds an additional layer of abstraction that will likely cost you some overhead. I suppose if you are really bad at writing SQL an ORM might be better, but in that case I'd just work on improving your SQL skills.

Edit: I'd just like to add that I wouldn't normally decide between ORM vs. no ORM based on performance. I'd make my selection based on other factors and optimize my design within that context.

Answer 3

All things being equal, ORMs add overhead, so they start at a disadvantage.

On the other hand, an ORM might generate more efficient SQL than you and many of them come with built-in caching features that you don't have to write yourself (and if they suit your purposes, the improvements you gain from that caching might be greater than what you lose to additional overhead).

Answer 4

If your only concern is speed of queries without cache usage, then you don't need ORM, because its main feature is to simply coding by removing the need to execute native queries. But sometimes you need to use natives queries with an ORM to get the best speed, and even still, the abstraction may take slightly longer than manually using a parametrized query.

However, if its possible that your queries will benefit from caching objects, an ORM may improve your speed.

Answer 5

The overhead comes from doing the mapping, tracking changes etc..., not the query itself. Mapping is very fast, but if you are working with 100,000 records in an operation, the straight sql will win. However, if you are dealing with 100,000 records in an operation, you shouldn't be using an ORM.