ryan.offord (12/8/2011)

---

One of the main problems I see with nHibernate is the way it approaches parameters for IN statements. I fully admit it's most likely our developers way of using it, but essentially when you pass in a list of ID's they come through in the underlying query as 'SELECT blah from myTable WHERE Id in (@p0, @p1, @p2.....)'. This ends up with a different hash of the execution plan for each variation of the same query. If I pass in 100 parameters I get one variation. If I pass in 101 I get another identical plan, albeit with a different hash so technically it's a different plan. As such we spend most of our time compiling execution plans that we only use once. Imagine the above scenario whereby we get up to 2000 params and you can see the problem (2000 potential different variations on a query). Yes, we've actually maxed out on params passed to a statement. I reckon we use 80% of the plans once before they are aged out. I've been told that the more recent versions have addressed this but I haven't seen it yet.

ryan.offord (12/8/2011)

---

The width of an nVarChar seems to cause it confusion. If I have a param with 7 characters I get one variation with the query using a param of nVarChar(7). If I change the value being passed in to 8 characters it changes the param in the query to nVarChar(8). Combine the two of these comments above and it gets silly. I haven't yet found anyone to agree to setting this to (and here is my crazy idea) the width of the column.