Hi I'm pretty sure the setting doesn't change based on your driver you use we had to explicitly turn it on in our NHibernate config.
Naz On Nov 1, 1:02 pm, zvolkov <[email protected]> wrote: > Naz, I thought prepare_sql is set to true by default in NH 2.1 (when > using SqlClientDriver)... > > On Oct 31, 3:36 pm, Naz <[email protected]> wrote: > > > Thanks Carsten Hess for explaining how execution plans are re-used in > > prepared statements. > > > However we still believe the default behaviour of NHibernate is that > > all queries should be parameterized with the correct parameter lengths > > ideally based on the table mapping and not dynamically as this causes > > huge performance problems in executions plans not bieng re-used. What > > surprised us is that is the default behaviour of the NHibernate's MS > > SQL Driver which if we did not look into would have caused us major > > problems had we gone live with NHibernate with the default setting. > > > So unless you know to set prepare_sql your application is not re-using > > execution plans and even when you know to set it the queries are sent > > using a not very well documented MS SQL procedure. > > > I still belive the fix is valid and we will continue to use our own > > driver until NHibernate sorts out it's MS SQL driver. > > > Naz > > > On Oct 30, 11:43 pm, Fabio Maulo <[email protected]> wrote: > > > > at the end somebody with a way to confirm or deny an issue. > > > > @Carsten Thanks. > > > > @Andrei now you can add an episode to your story. Thanks > > > > Btw the point, IMO, is the same... I never saw the issue for MsSQL team, > > > only some external workaround. > > > > 2009/10/30 Carsten Hess <[email protected]> > > > > > Being a part of this discussion in the beginning (and being quoted in > > > > some of the referenced material) I'll give my latest 10 pence to the > > > > discussion. I'm not working on a project with NHibernate right now, > > > > thats why I've been silent. > > > > > 1) prepare_sql: I wrote that setting prepare_sql was causing the > > > > underlying ADO.NET layer to generate "prepared" statements instead of > > > > "parameterized" statements. Prepared statements are not wery well > > > > documented by MS, but gives back a handle to the application layer > > > > which can be used to execute the same statement again without > > > > resending the statement sql text (only actual parametervalues). What I > > > > also wrote was that these prepared statements are only connection > > > > local. That it still true when it comes to the handle (representing > > > > the statement), but I later found out that the executionplan that was > > > > generated at the same time is GLOBAL - as we want it to be... So > > > > setting "prepare_sql = true", sqlprofiler shows us a lot of sp_prepare > > > > statements which gives back new handles for the same statement for new > > > > connections - but the underlying executionplan IS reused (as long as > > > > the size of the parameterdefinitions are unchanged). > > > > > Just before version 2.1, Fabio implemented changes so the > > > > typedefinition for strings (which caused the biggest problems) were > > > > sent using the standard size of 4000. This is a OK solution for the > > > > problem sketched above, which requires the typedefinition to be > > > > constant in order to use an existing executionplan. In ver. 2.1 some > > > > bugs concerning size of binaryblobs and decimals not being set > > > > correctly were also fixed. > > > > > All in all my conclusion is that setting prepare_sql = true for > > > > NHibernate later than ver. 2.1 IS a viable solution in order to reuse > > > > executionplans (and avoid sending sql text more than once per > > > > connection). You dont need to override the database driver code. > > > > > Kind regards > > > > Carsten Hess > > > > > You can use this query to check whether your executionplans are reused > > > > (usecounts column): > > > > > select top 100 st.text, cp.cacheobjtype, cp.objtype, cp.refcounts, > > > > cp.usecounts, cp.size_in_bytes, cp.bucketid, cp.plan_handle > > > > from sys.dm_exec_cached_plans cp > > > > cross apply sys.dm_exec_sql_text(cp.plan_handle) st > > > > where cp.cacheobjtype = 'Compiled Plan' > > > > and cp.objtype = 'Prepared' > > > > > On 30 Okt., 18:49, Naz <[email protected]> wrote: > > > > > I know ;) > > > > > > "The ideal situation is alongside the fix you set parameter sizes in > > > > > your mapping that match your table..." > > > > > > Naz > > > > > > On Oct 30, 12:58 pm, zvolkov <[email protected]> wrote: > > > > > > > Naz, once the fix is in place, you can do it in the mappings by > > > > > > adding > > > > > > type="AnsiString(LENGTH)" to each string property. > > > > > > > On Oct 30, 5:08 am, Naz <[email protected]> wrote: > > > > > > > > Hi > > > > > > > Although passing large default parameter sizes is still not a good > > > > > > > idea with MS SQL since the database potentially might start > > > > > > > holding > > > > > > > large nvarchar(4000) parameters in memory. SQL Server is fairly > > > > > > > smart > > > > > > > and will just make sure it has the memory available to hold it but > > > > > > > only use the memory required and not allocate the full amount. > > > > > > > > This is still a lot better situation than not re-using execution > > > > plans > > > > > > > and/or filling up your server full of them to the point it starts > > > > > > > dropping plans so although the fix isn't perfect it's so much > > > > > > > better > > > > > > > than the current behaviour. > > > > > > > > The ideal situation is alongside the fix you set parameter sizes > > > > > > > in > > > > > > > your mapping that match your table or if your to lazy to do that > > > > > > > then > > > > > > > at least if you know most of your nvarchar fields are say 255 > > > > > > > length > > > > > > > make that a default convention and only specify larger if > > > > > > > required. > > > > > > > > Regards, > > > > > > > > Naz > > > > > > > > On Oct 29, 1:55 pm, zvolkov <[email protected]> wrote: > > > > > > > > > Ok, implemented these changes in the next version of my app, > > > > > > > > goes > > > > to > > > > > > > > PROD around New Year. > > > > > > > > > On Oct 29, 12:09 am, Fabio Maulo <[email protected]> wrote: > > > > > > > > > > 2009/10/29 zvolkov <[email protected]> > > > > > > > > > > > Fabio, my point is: if SQLServer is changed to always call > > > > > > > > > > SetParameterSizes and SetParameter does not specify the > > > > > > > > > > size, > > > > NH will > > > > > > > > > > simply default to nvarchar(4000), so what's the problem? > > > > > > > > > > Try it with a app in prod. never set parameter size in your > > > > queries and > > > > > > > > > pray. > > > > > > > > > If all is working as you expect and your DBA is happy let me > > > > > > > > > know > > > > (opening a > > > > > > > > > JIRA with needed info). > > > > > > > > > > -- > > > > > > > > > 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 -~----------~----~----~----~------~----~------~--~---
