ORM performance cost

Question

Does anyone have any experience that indicates what kind of performance hit a developer could expect by choosing to use an ORM (in Django, RoR, SQLAlechemy, etc) over SQL and hand-designed databases? I imagine there are complicating issues, including whether specifying a database within the constraints of an ORM increases or decreases the chances of creating an efficient database structure (based on the developer's level of experience), and the question of how well the developer constructs either the SQL or ORM-based queries (again based on his/her experience). Any information regarding these or intrinsic performance issues would be really interesting to me.

Answer 1

Performance - its always pro and cons. If you deep deeper into ORM architecture (see my article: avoid ORM bad habits) then you will find intuitively the ways to make it faster. Here's my another article on how to make EF6x 5x faster (at least for read situations): EF6.x 5x faster

Anyhow for good performance, even with ORM you will need to create database views, indexes too as to check the queries that are generated and executed by ORM and fine tune them too. Eager loading is a must with ORM.

Answer 2

Performance has always been an after thought in most DAL Layer development / architecture. I think its about time we start questioning the performance of these ORM tools, for the so-called ease of development they promise:

The 2 biggest areas of performance issues in ORMs are:

1. Inability to write Optimum SQL. You have to use an Object Query Language which is interpreted into SQL by the framework. Mostly it is good SQL, but often enough it is not the most efficient SQL.

2. Reflection. Most ORM frameworks use Reflection to populate objects with Data from the database. Reflection operations are costly, and with increasing number of load and data, the performance degradation becomes obvious.

Other performance issues that arise are because of inefficient Database Design or Entity Model design due to the tight coupling of Entity objects to Tables.

Answer 3

It also depends on what you're using as an ORM. In my experience, Hibernate is a pig, in terms of speed, resource usage and startup time. LINQ to SQL, on the other hand, is an extremely light SQL wrapper, whose impact you'd likely barely (if at all) notice.

Answer 4

This will depend a lot on the what you compare it with. The the coder writing the hand code is a total hack it might be a boon rather than a hit. Clearly it can go the other way as well.

Answer 5

My advice is not to worry about this until you need to - don't optimise prematurely. An ORM can provide many benefits to development speed, code readability and can remove a lot of code repetition. I would recommend using one if it will make your application easier to develop.

As you progress through the development use benchmarks and profiling to determine the bottlenecks in the code and if needed you can bypass the ORM and use manual queries where they are required. Normally you will be able to improve the speed of the ORM using caching and database indexes (amongst other things) and then you can decide where manual queries are required. For the most part, the ORM performance will probably acceptable and the benefits of using it will far outweigh the performance cost.