wagnerblog.com

Klaus Aschenbrenner is quantifying the speed that some operations gain by being moved into the db. I love ORM’s like LLBLGen but just as Klaus clearly saw, you can’t simply throw any old ORM at an app. LLBLGen happens to be able to deal with this. But sometimes it may take judicious tuning and purposeful movement of operations into the db (as shown with the use oif NHibernate). As an architect I have to decide if the manageability of a domain object model is worth some loss of speed. If so, how much. And of course as long as we are married to 1 db without any chance of changing it, it becomes less of an issue to encapsulate business logic in procs. As all things in life, these are constant tradeoffs.

Here are some of Klaus’ results:

Testing agains 10,000 rows with an update statement. The results of this performance tests are as follows:

NHibernate: ~10 seconds with ~30k T-SQL Statements
DataSet: ~8 seconds with ~20k T-SQL Statements
Managed Stored Procedure: ~8 seconds with 1 T-SQL Statement (it’s called inside SQL-Server)
pure T-SQL Solution #1: 2.5 sec. with 1 T-SQL Statement
pure T-SQL Solution #2: 0.8 sec. with 3 T-SQL Statements

I’m 10 times faster in T-SQL as an O/R mapper or a DataSet solution - part #1
The results of this performance tests are as follows:
NHibernate: ~10 seconds with ~30k T-SQL Statements
DataSet: ~8 seconds with ~20k T-SQL Statements
Managed Stored Procedure: ~8 seconds with 1 T-SQL Statement (it’s called inside SQL-Server)
pure T-SQL Solution #1: 2.5 sec. with 1 T-SQL Statement
pure T-SQL Solution #2: 0.8 sec. with 3 T-SQL Statements