Large volume database updates with an ORM

Question

I like ORM tools, but I have often thought that for large updates (thousands of rows), it seems inefficient to load, update and save when something like

UPDATE [table] set [column] = [value] WHERE [predicate]

would give much better performance.

However, assuming one wanted to go down this route for performance reasons, how would you then make sure that any objects cached in memory were updated correctly.

Say you're using LINQ to SQL, and you've been working on a DataContext, how do you make sure that your high-performance UPDATE is reflected in the DataContext's object graph?

This might be a "you don't" or "use triggers on the DB to call .NET code that drops the cache" etc etc, but I'm interested to hear common solutions to this sort of problem.

Answer 1

ORMs are great for rapid development, but you're right -- they're not efficient. They're great in that you don't need to think about the underlying mechanisms which convert your objects in memory to rows in tables and back again. However, many times the ORM doesn't pick the most efficient process to do that. If you really care about the performance of your app, it's best to work with a DBA to help you design the database and tune your queries appropriately. (or at least understand the basic concepts of SQL yourself)

Answer 2

You're right, in this instance using an ORM to load, change and then persist records is not efficient. My process goes something like this

1) Early implementation use ORM, in my case NHibernate, exclusively

2) As development matures identify performance issues, which will include large updates

3) Refactor those out to sql or SP approach

4) Use Refresh(object) command to update cached objects,

My big problem has been informing other clients that the update has occured. In most instances we have accepted that some clients will be stale, which is the case with standard ORM usage anyway, and then check a timestamp on update/insert.

Answer 3

ORMs just won't be as efficient as hand-crafted SQL. Period. Just like hand-crafted assembler will be faster than C#. Whether or not that performance difference matters depends on lots of things. In some cases the higher level of abstraction ORMs give you might be worth more than potentailly higher performance, in other cases not.

Traversing relationships with object code can be quite nice but as you rightly point out there are potential problems.

That being said, I personally view ORms to be largely a false economy. I won't repeat myself here but just point to Using an ORM or plain SQL?

Answer 4

Most ORMs also have facilities for performing large or "bulk" updates efficiently. The Stateless Session is one such mechanism available in Hibernate for Java which apparently will be available in NHibernate 2.x:

http://ayende.com/Blog/archive/2007/11/13/What-is-going-on-with-NHibernate-2.0.aspx

Answer 5

Bulk updates are a questionable design. Sometimes they seems necessary; in many cases, however, a better application design can remove the need for bulk updates.

Often, some other part of the application already touched each object one at a time; the "bulk" update should have been done in the other part of the application.

In other cases, the update is a prelude to processing elsewhere. In this case, the update should be part of the later processing.

My general design strategy is to refactor applications to eliminate bulk updates.