SQL Server Performance – Death by Entity Framework / nHibernate

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

It is very popular to use Entity Framework or nHibernate in devlopment Projects because the developers want to save time, they doesn’t want to write SQL code or even Think about it. The managers doesn’t hire any  database designers or database developers because every developer “knows how to create and use databases”  But this is where the problem starts! As i use to say, it’s “when you don’t think, thats when the shit hits the fan”.

Why does the frameworks kill the Performance? Well there are some key issues:

  • They make it too easy to create a datamodel that isn’t normalized
  • The make it too easy to use wrong datatypes (too big or too detailed)
  • The T-SQL code generated from the framework is very inefficient
  • The T-SQL code generated is unreadable because of aliases and nested subqueries in many levels

But the system performed well in development and test! Yes, but have you used enough testdata? Well, I didn’t Think so. I have seen many applications with Performance issues due to “frameworks” and they performed well in test when there was a fraction of records in the tables.