Hi All We have recently found that a combination of where clause parameters (which results in minimal filtering) on one of our queries is resulting in sql timeouts in our application.
We are happy to do dirty reads in this situation and so I initially just wrapped the query in a ReadUncommitted transaction...but the timeouts kept happening for this specific clause. Next thing I did was write a simple little console app to execute the same query that NHibernate was generating which confirmed that it wasn't some other issue within our application or the web server. What in did make the query work without timeout was explictly adding nolock hints to the query generated by NHibernate and pumping that through my console app. So I have a couple of queries really. First a more general query about sp_executesql (as I believed and currently still do believe that nolock hints and Read Uncommitted have the same effect), does this SP use the database default transaction isolation level and even though I have wrapped the query in a Read Uncommitted transaction it is falling back to Read Committed thus causing the timeout. The query NHibernate generates is via ICriteria, I can't seem to find a way to get nolock hints added to that query. Is there a way I can get a query which includes nolock with ICriteria (without writing an interceptor)? Is there a way I can get NHibernate to issue the query outside of sp_executesql (because I believe that is ignoring my transaction isolation level)? Any other suggestions are welcomed. Thanks Simon /* Email: [email protected] Twitter: @simonmfox Phone: +64 21 079 2900 */ -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
