.NET Zone is brought to you in partnership with:

I am developer and technology maniac who is working on Microsoft and PHP technologies. I have ASP.NET MVP title and I hold MCAD, MCSD and MCTS certificates. When I have free time I usually play with new technologies, hack something, read books, participate in communities and speak in events. I am also active blogger and my ASP.NET blog is the place you can find some interesting reading about my discoveries and personal thoughts. Gunnar is a DZone MVB and is not an employee of DZone and has posted 136 posts at DZone. You can read more from them at their website. View Full User Profile

How to Avoid Throttling SQL Azure Database with NHibernate

10.17.2013
| 2162 views |
  • submit to reddit

Lately I had one weird experience on with running NHibernate on Windows Azure and I think it’s worth to share with you. NHibernate is matured industry-level ORM that is proven itself over years. Although it is not so easy to use as Entity Framework it is still my favorite due to flexibility and wide databases support it provides. In this posting I will show you one important trick with enum mappings. When ignored you may put huge unnecessary load to your database.

We have one system launched to public where NHibernate is used as ORM. The system runs on Windows Azure (web roles and one worker role). One nice day out worker role that runs bunch of services ran into troubles with database. From logs we found out that throttling protection was activated on SQL Azure.

It’s possible to switch to some bigger database package on SQL Azure but as we don’t have this much data yet and the site is not heavy load I got very interested in what is going on. Why is our service shooting something expensive to database?

Problem with enum mapping

When analyzing the problem I found out the problem. Take a look at the following NHibernate mapping:

<property name="AlertType" column="alert_type" type="int" />

The problem is in this mapping – it defines enumerator as integer. As enums and ints can be casted from one to another, NHibernate doesn’t complain about this mapping but internally it doesn’t like it. This mapping screws something up in change tracking and NHibernate thinks that entity is changed. Yes, it loads entity and finds then out it is changed. This means that when session is flushed a load of update commands are executed on database and as the result SQL Azure throttling protection may activate.

Mapping enums correctly

When mapping enums we have to specify also their type. This mapping here is correct:

<property name="AlertType" column="alert_type">

  <type name="MyProject.MyEnum, MyProject"></type>

</property>

After fixing all enum mappings to use correct enum types we calmed down NHibernate and this far we have had no problems with SQL Azure throttling anymore.

Related Posts

The post How to avoid throttling SQL Azure database with NHibernate appeared first on Gunnar Peipman - Programming Blog.

Published at DZone with permission of Gunnar Peipman, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)