Finally figured out the trigger here! We have a custom IUserType that maps single-character fields to Enum values. That custom type declares that it maps to a StringFixedLength with a Length of 1. However, the NHibernate SqlClientDriver.InitializeParameter calls SqlClientDriver.SetDefaultParameterSize which ends up setting the size to 4000. Now, if I try a raw ADO.NET query and set the Size property of the parameter to 4000, the query times out. However, if I set the Size to 1 like it's supposed to be, it executes almost instantly.
I created a NHibernate issue for this: https://nhibernate.jira.com/browse/NH-3745 -Lauri On Wednesday, October 8, 2014 4:14:11 PM UTC+3, Lauri Kotilainen wrote: > > Hi, > > I encountered a really weird situation today. I've got a NHLinq query that > does an OrderBy, then adds a bunch of Fetches and finally ToLists the whole > thing. The query returns about 700 root entities from a total of about 2000 > rows. It's a fairly heavy query, and on my development machine, it takes > between 1 and 3 seconds to finish hydrating the entities. > > The weird part starts, unfortunately, with a production database. The DB > is running on Azure SQL Databases. And on the production db, the query > takes anywhere between 15 and 20 seconds to execute. However, if I export > the production database to a copy and run the query against the copy, we're > back to between 1 and 3 seconds. > > OK, I think to myself, maybe the import/export does something to rearrange > the data. Except... according to NHProf, the query itself takes under 200ms > to execute, and the rest of the time is spent in hydration. I've confirmed > this with SQL Server Management Studio -- the SQL query itself completes in > under a second. > > And here's where it gets *really* weird: if I remove the OrderBy clause > from the NHLinq query, we're back to 1-3 second execution times. > > I guess my question is basically: what can the OrderBy call possibly do > that would make hydration an order of magnitude slower in some cases? > > -Lauri > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
