My pleasure. 2009/10/28 Naz <[email protected]>
> > 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 > > > > -- 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 -~----------~----~----~----~------~----~------~--~---
