Extending the default Command Timeout in Nhibernate

There was a Nhibernate query that is taking more than 30 sec , 34 seconds to be exact. The query involves a view that joins so many views and tables. It started taking more than 30 seconds last week. Reviewing the execution plan and throwing indexes here and there didn't help. The next immediate solution was to extend the Nhibernate default timeout time (i.e.30 seconds).

I believe extending the timeout should be a last resort approach , should be used as immediate fix until you come up with a long term solution - like redesigning your data architecture , optimizing your queries and others.

Nhibernate allows you set the command timeout for query. This timeout is the length of time the query will be allowed to execute and finish. If  the query takes more than this time,the query will be aborted and SQL timeout exception will be thrown.

Currently you can set the timeout on query if you are using ICriteria. The Nhibernate team is working to make this method (setting the timeout) available on  QueryOver  on the next release (3.3.x). 

I was using Nhibernate QueryOver for my query, thus I had to rewrite it using ICriteria , I know it is a bummer :(  . Assuming you are using a unit of work pattern  the code will be along the line of the following 

var query = _unitOfWork.currentSession.CreateCriteria<MyView>().          setTimeout(TIMEOUT_IN_MILLISECONDS);

I will leave it like that until we completely remove that view and replace it with a roll up table in our next release.