CODEWITHSUNDEEP
sql-servernhibernatetimeout
Eoin Campbell
Eoin Campbell

Reputation: 44268

NHibernate.Exceptions.GenericADOException Timeout from code but not from DB

I have an NHibernate Query (which is populating an EXTJS grid)

It's firing 2 queries off to the database, one to get the record count (for paging purposes) and the other to get the top N rows to fill the grid with.

From code, I'm consistently getting an exception on the Select count(*) statement.

NHibernate.Exceptions.GenericADOException: 
Failed to execute multi criteria[SQL: 
 SELECT count(*) as y0_ FROM RecordView this_ inner join ProcessesView 
 process1_ on this_.ProcessId=process1_.Id inner join BusinessModelsView 
 businessmo3_ on process1_.BusinessModelId=businessmo3_.Id inner join BatchesView
 batch2_ on this_.BatchId=batch2_.Id WHERE this_.ProcessId = ?;
] ---> System.Data.SqlClient.SqlException: Timeout expired.  
The timeout period elapsed prior to completion of the operation or the server 
is not responding.

However if I take that exact query and drop it into an SSMS window, and run it, it executes in a <1 second.

Is NHibernate doing anything "funny" under the hood here. Are there execution plan/cache issues. I'm at a complete loss as to why this is occurring.

Upvotes: 2

Views: 6492

Answers (2)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

Whenever I encountered this error, the reason was locking (never performance). There was two sessions opened (accidently). Both started transaction and one of them locked the table.

The problem could be some not disposed session, or "unintended" singleton... holding opened session.

This answer is not as straigth forward as I wish, but I am sure about the direction. Because I experienced the same (and was guilty)

BTW: as Oskar Berggren found out from you, 30 secods timeout would be related to the <property name="command_timeout">30</property>. I am sure, if you will provide 60, 120 ... it will be not enough because of lock

Upvotes: 2

jbl
jbl

Reputation: 15413

Your two queries are not handled in the same way by SQL SERVER

  • your NH query has been compiled on its first execution, based on table statistics and on the first value of the parameter. The generated query plan will then be used for all subsequent calls, witout considering the parameter value

  • your SQL query (where, I guess, you replace the ? with an actual value) gets a different compilation for each value, based on statistics, and on the value.

Your first NH compilation might have produced a query plan, effective for the first value, but not in the general case.

First, I would suggest that :

  • you count on a projection (say on the main table id), as it is slightly more effective than count(*), allowing the DB to work only on indexes when possible
  • you check that you don't miss any index necessary to your query
  • you check that all your table statistics are up to date

If this does not improve execution time, this post offers some options (recompile might be the good one) : Query executed from Nhibernate is slow, but from ADO.NET is fast

Upvotes: 1

Related Questions