Hello! My SQL Server 2005 plan cache was filling up and I stumbled across this post. I have some opinions on the matter:
First of all - there is a big difference between a parameterized statement and a prepared statement (even though they may look almost the same). The former is what ADO.NET does automatically for most queries - that is substituting variables with typed placeholders and executing it with a "exec sp_executesql ..". These types of statements requires the whole query to be send down every time, but if the type definition (including size of varchars) of the query is exactly the same (apart from actual parametervalues) the execution plan in SQL server can and will be reused. Sql server hashes the parameterized statement body and uses that value to look for an existing execution plan. The latter is a "secret" feature (sp_preparesql is undocumented) where it is possible to prepare a statement (much like for a parameterized) in the database - get a handle back for it - and call the query only by using this reference and the actual parameters. So far so good. But this can ONLY be reused in the same connection!! If you are execting the same statement over and over again on the same connection it may pay off - but it does not give you anything in a scenario where you have short transactions on random connections from the connectionpool - on the contrary!!! So: 1) I think it is correct to let "prepare_sql" be false as default. Setting it to true may solve the problem with the excessive amount of execution plans being generated - but it slows your application down in another way... 2) Instead I think NHibernate should be fixed (SqlClientDriver.cs: GenerateCommand should always call SetParameterSizes), this will make the parameterized statement have type definitions corresponding to mapping settings instead of the size of the actual parameter. But... there is an inconsistency and undocumented feature of NHibernate mapping here: If you have a property mapping of a string: <property name="Name" length="100">, hibernate will correctly discover it as a string and use the length field if you generate your schema from it (SchemaExport.Create). So it will generate a varchar(100) column. It WILL NOT though use the length field when setting the size of the type in the above scenarios!! If you change the mapping to this: <property name="Name" type="String(100)"> NHibernate will use the length for both the schema preparation and the size for parameterized or prepared statements! It was only looking through code I found out this possibility - haven't found it documented anywhere. That must be bug / leftover ? Kind regards Carsten Hess On Mar 17, 1:45 am, Dario Quintana <[email protected]> wrote: > BTW, thanks to Claudio Maccari we have write it > downhttp://nhforge.org/wikis/howtonh/tuning-queries-with-ms-sqlserver.aspx > > On Mon, Mar 16, 2009 at 12:52 AM, Fabio Maulo <[email protected]> wrote: > > 2009/3/15 Dario Quintana <[email protected]> > > >> Here it says, that there is no reason to call it in the client application > >> where using sql2000 or 2005 > > >>http://msdn.microsoft.com/en-us/library/system.data.idbcommand.prepar... > > > without that parameter the drive (in nh) don't set parameter size. > > -- > > Fabio Maulo > > -- > Dario Quintanahttp://darioquintana.com.ar --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
