Hi I don't now how the other databases work but MS SQL generates different execution plans for each query individual query including the parameter sizes so the Execution Plans can be different for a query with a parameter of a different size it is a feature/behaviour of SQL Server. Because of this known behaviour it has been documented by nhforge and ayende who claim a so called fix for the issue.
http://ayende.com/Blog/archive/2009/05/02/nhibernate-the-database-query-cache-and-parameter-sizes.aspx http://nhforge.org/wikis/howtonh/tuning-queries-with-ms-sqlserver.aspx http://testdrivendevelopment.wordpress.com/2009/03/10/nhibernate-queries-sql-server-execution-plans/ The issue is this fix doesn't fix the problem rather it just changes behaviour in which NHibernate connects to the database which requires it to call SetParameterSizes() which at first glance seem to fix the problem but actually only doing it for the current connection. In my opinion the default behaviour of the NHibernate SQLServer driver should be to SetParameterSizes() on every query it passes to the Database. Thanks Fabio for showing us how to implement our own driver. Regards, Naz On Oct 28, 3:54 am, Fabio Maulo <[email protected]> wrote: > Have you a link to pros and con of set all parameters size always for any > DbType ? > I don't remind why we have that "if" there... probably was for the bug I > have entirely analysed and fixed (about the management of > size/precision,scale) but perhaps is because somebody have analysed some > other situation. > > If you can provide enough info., to justify such change, you know which is > the procedure (JIRA with explication, test and links). > > I'm not worried by NH adoption... I'm 42 and you can imagine how much > technology I saw born and die. NH has its lifecycle as any other tech. > > 2009/10/27 Daniel Auger <[email protected]> > > > > > > > Fabio, > > > I totally understand your frustration with this quirk of SQL Server. > > However I tend to agree with Rob. If NHibernate can work around it > > without compromising the framework (i.e. the workaround is isolated to > > the driver), the workaround should probably be implemented if only to > > help adoption. This issue is a glaring problem that a developer or DBA > > can point to as a reason to avoid NH. Even though it's easy to inject > > a custom driver, it's a hard sell to potential adopters because I > > think most people are going to feel queasy about doing so. People just > > want things to "work". If you go look around on some of the SQL > > forums / blogs, this is an issue used as anti-NH ammo. What's more > > practical: implementing the work around, or telling everyone using SQL > > Server who cares about performance to create a custom driver? Where > > the blame lies is not important to 99% NH users. Also I'd bet money MS > > is going to make sure EF won't have this issue. > > > With all my respect, > > > - Dan > > > On Oct 27, 3:22 pm, Fabio Maulo <[email protected]> wrote: > > > sorry for the relief... sorry > > > > 2009/10/27 Fabio Maulo <[email protected]> > > > > > and Rob... > > > > my goal is do the right thing in the right place. > > > > We all should learn how send issues to MsSQL team and not only looking > > for > > > > a workaround in NHibernate. > > > > Do you have a link to the issue regarding the query-execution plan in > > MsSQL > > > > ? > > > > > do you know why MsSQL need the parameter-size to choose the > > execution-plan > > > > ? > > > > why the others RDBMS does not need it ? > > > > > why I can write something so simple as > > > > in FireBird: SELECT FIRST x [SKIP y] rest-of-sql-statement > > > > in Postgre : SELECT rest-of-sql-statement LIMIT x [OFFSET y] > > > > in MySQL : SELECT rest-of-sql-statement LIMIT x [, y] > > > > and in MsSQL I must make me crazy for a simple pagination in the 21th > > > > century ? > > > > > why I can't write something like this ? > > > > SELECT rest-of-sql-statement WHERE CONTAINS(…, …) = :pShouldContain > > > > Which is the type of return-value of the two functions CONTAINS and > > > > FREETEXT ? > > > > > Perhaps you can tolerate all these stuff the MsSQL-team are giving us > > but > > > > I'm starting to be a little bit not tolerant. > > > > > 2009/10/27 Rob <[email protected]> > > > > >> Although I agree with your sentiment from an architectural perspective > > > >> (i.e., "ORM is to fill the gap between O and RDMS and not to fix RDBMS > > > >> issues"), > > > >> as a practical matter (if NH adoption is your goal), providing the > > > >> user with a(n easy) way to overcome "issues" is often required by the > > > >> thing filling the gap. May not be what you'd like to do, but it's > > > >> often necessary. > > > > >> Rob > > > > >> On Oct 27, 10:55 am, Fabio Maulo <[email protected]> wrote: > > > >> > To be more clearhttp://twitter.com/fabiomaulo/status/5186045199 > > > > >> > 2009/10/27 Fabio Maulo <[email protected]> > > > > >> > > if so... remember that the Driver, in NHibernate, is an injectable > > > >> > > component. > > > >> > > You can copy&paste the default impl. and then change it to do what > > you > > > >> > > want; after that you only need to use the NH's configuration to > > inject > > > >> your > > > >> > > drive. > > > > >> > > 2009/10/27 Daniel Auger <[email protected]> > > > > >> > >> The blog author posted a comment on the blog indicating that the > > team > > > >> > >> was using NHibernate 2.1.0 with MSSQL Server 2005. I will try to > > test > > > >> > >> 2.1.1 when time allows. > > > > >> > >> Fabio, do the fixes you made negate the need for setting > > prepare_sql > > > >> = > > > >> > >> true? The reason why I ask is that if I'm understanding the blog > > > >> > >> correctly, using prepare_sql is the wrong way to solve the > > problem as > > > >> > >> SQL server is going to cause the execution plan per connection > > > >> > >> behavior. > > > > >> > >> On Oct 26, 8:29 pm, Daniel Auger <[email protected]> > > wrote: > > > >> > >> > I've sent messages to both the blog poster and the initial > > reporter > > > >> to > > > >> > >> > validate the fix and report back here. > > > > >> > >> > On Oct 26, 6:13 pm, Fabio Maulo <[email protected]> wrote: > > > > >> > >> > > Use 2.1.0 and then let me know... > > > >> > >> > > I don't remember exactly where I have fixed all > > parameters-size > > > >> stuff > > > >> > >> but > > > >> > >> > > should be 2.1.0 or in the actual branch (2.1.1 that will be > > > >> released > > > >> > >> > > on Sundays). > > > > >> > >> > > 2009/10/26 Craig van Nieuwkerk <[email protected]> > > > > >> > >> > > > 2.0 it says in the article. Could be 2.01, it is not > > specific. > > > > >> > >> > > > On Tue, Oct 27, 2009 at 9:39 AM, Fabio Maulo < > > > >> [email protected]> > > > >> > >> wrote: > > > >> > >> > > > > NH version? > > > > >> > >> > > > > 2009/10/26 Daniel Auger <[email protected]> > > > > >> > >> > > > >> This came across my twitter stream today: > > >http://www.objectreference.net/post/NHibernate-and-Execution-Plans.aspx > > > > >> > >> > > > >> The blog entry claims that prepare_sql = true really > > only > > > >> solves > > > >> > >> the > > > >> > >> > > > >> problem on a per connetion basis. > > > > >> > >> > > -- > > > >> > >> > > Fabio Maulo > > > > >> > > -- > > > >> > > Fabio Maulo > > > > >> > -- > > > >> > Fabio Maulo- Hide quoted text - > > > > >> > - Show quoted text - > > > > > -- > > > > Fabio Maulo > > > > -- > > > Fabio Maulo > > -- > Fabio Maulo --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
