Menu
I got a problem with NHibernate in C#.
When it wants to execute a query the application face with a ADO timeout error, but when I use SQL Profiler to capture the query, and then I run it in new query of SQL Server, it happens to take just 2 seconds
Any ideas??
978
If you encounter a connection-timeout error, follow the steps: Increase the connection-timeout parameter. If you use an application to connect to SQL Server, increase the relevant connection-timeout parameter values and check whether the connection eventually succeeds.
SQL Server will typically show you connection timeouts or operation (query) timeouts. These values are set by the client connecting to the SQL Server. An operation timeout occurs when a command takes too long to complete, and the client raises an error.
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.
Try using the WAITFOR command in T-SQL. That'll pause the execution of the query and you should see a timeout. Try using the WAITFOR command in T-SQL. That'll pause the execution of the query and you should see a timeout.
When you capture the query from SQL Profiler and run it in SSMS, are you running it as an sp_executesql query? I ran into a similar problem using NHibernate 2.1GA and this answer applies to that version, I haven't converted to NH3 yet. NH Profiler is a great tool but it helpfully extracts the SQL into a formatted query that doesn't represent the actual query sent to the server.
The problem is the way NHibernate supplies string parameters to sp_executesql. String parameters are typed as nvarchar with a length equal to the value's length. For example, this query restricts two columns that are varchar(4) and varchar(20) respectively:
exec sp_executesql N'SELECT this_.Column0, this_.Column1 FROM MySchema.MyTable this_ WHERE this_.Column0 = @p0 and this_.Column1 = @p1',N'@p0 nvarchar(4),@p1 nvarchar(7)',@p0='Val0',@p1='Value01'
The query plan for this used an index scan and took 17 sec. Changing the nvarchar to varchar generated a plan that used an index seek and executed in < 2 sec. This was reproducible in SSMS.
The root cause was the NHibnerate uses DbType.String instead of DbType.AnsiString for varchar columns by default. The solution for me was to add a Fluent NHibernate convention to change all string mappings to AnsiString which caused NHibernate to create queries that supplied parameters as varchar.
63
well i hv seen nhibernate timeouts occurring when you are dealing with a transaction that is not yet committed to the database and using a different transaction that operates on the same object.. so i would suggest look out for multiple sessions poen within your app and make sure that is not the case and use only 1..
and also using nhibernate profiles is something that i would suggest too.. http://nhprof.com/ Its a cool tool to have.. it actually shows the query fired to the db and the rows retrieved and is very easy to use too..All you need to do is set the connection string to the dB that u r running the query against and voila u can see all ur queries and u can say good bye to SQL profiler.
Hope that helps.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
