Sql Server Execution Plans / Parameter Size

[Daniel Auger]

I came across this today:
http://scarydba.wordpress.com/2008/04/29/nhibernate-recompiles-and-execution-plans/

I'm wondering how people are dealing with this, or if it is seen as a
non issue.

[Gustavo Ringel]

It is using nvarchar as a primary key, i hope that was what scared you.

Gustavo.

[Daniel Auger]

I hadn't even considered the scariness of that :).

Are you implying this behavior only happens with primary keys? I was
under the impression that any of the varchar parameters could
potentially cause another execution plan to be created.

On Mar 10, 12:35 pm, Gustavo Ringel <gustavo.rin...@gmail.com> wrote:
> It is using nvarchar as a primary key, i hope that was what scared you.
>
> Gustavo.
>

> On Tue, Mar 10, 2009 at 5:59 PM, Daniel Auger <daniel.au...@gmail.com>wrote:
>
>
>
> > I came across this today:
>

> >http://scarydba.wordpress.com/2008/04/29/nhibernate-recompiles-and-ex...

[Gustavo Ringel]

No, i'm telling that if i try a tool using it in a way it is not supposed to be used, then i won't get any value of my test.

As i understood in a brief chat with Fabio setting prepare_sql to true is enough to solve this problem...it should have been de default value, but for some reason it is not.

Gustavo.

[Fabio Maulo]

try to set prepare_sql to true and, btw, it is a specific "problem" of a specific RDBMS.

I know that many people think that NH can solve anything but we can't...

If an RDBMS has a strange way to paginate result, we can do something but we can't change the RDBMS.

If WCF has a bug for distributed transaction we can apply, may be, a workaroud but the bug is in WCF.

If a RDBMS change its behavior when you use "OR" clause instead "IN" and the "OR" has better performance then "IN" the real problem is not in NH but in that RDBMS.

We are few, poor and ugly and commercial companies (as Microsoft or ORACLE) has more power to fix their issues.

2009/3/10 Daniel Auger <daniel...@gmail.com>

--
Fabio Maulo

[Daniel Auger]

Excellent information. Thanks!

On Mar 10, 2:07 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> try to set prepare_sql to true and, btw, it is a specific "problem" of a
> specific RDBMS.
> I know that many people think that NH can solve anything but we can't...
> If an RDBMS has a strange way to paginate result, we can do something but we
> can't change the RDBMS.
> If WCF has a bug for distributed transaction we can apply, may be, a
> workaroud but the bug is in WCF.
> If a RDBMS change its behavior when you use "OR" clause instead "IN" and the
> "OR" has better performance then "IN" the real problem is not in NH but in
> that RDBMS.
>
> We are few, poor and ugly and commercial companies (as Microsoft or ORACLE)
> has more power to fix their issues.
>

> 2009/3/10 Daniel Auger <daniel.au...@gmail.com>

[Fabio Maulo]

2009/3/10 Daniel Auger <daniel...@gmail.com>

Excellent information. Thanks!

BTW you know... everything is possible in IT... with the correct motivation.

--
Fabio Maulo

[Fabio Maulo]

Daniel, if you have time please send the same request to the team are developing your RDBMS and ask they why the system is using a different query plan only because a parameter has a different length... at the end the query-plan should be based on tables and fields involved in the query and not on a parameter value especially when the parameter is not involved in the SELECT clause.

2009/3/10 Daniel Auger <daniel...@gmail.com>

--
Fabio Maulo

[Daniel Auger]

So, it looks like the author of the blog post was putting the blame in
the wrong spot. You are right, the NH team can't fix Sql Server for
MS.

Fabio, that request could make for an entertaining conversation. ;)

On Mar 10, 3:38 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> Daniel, if you have time please send the same request to the team are
> developing your RDBMS and ask they why the system is using a different query
> plan only because a parameter has a different length... at the end the
> query-plan should be based on tables and fields involved in the query and
> not on a parameter value especially when the parameter is not involved in
> the SELECT clause.
>

> 2009/3/10 Daniel Auger <daniel.au...@gmail.com>

[Daniel Auger]

I'd like to add a little bit of followup on this issue. The original
blog author posted a new entry the day after I started this thread.
The author indicates that prepare_sql does alleviate the issue.
http://scarydba.wordpress.com/2009/03/11/a-fix-for-nhibernates-parameter-problem/

In that entry the author points to the following:
http://testdrivendevelopment.wordpress.com/2009/03/10/nhibernate-queries-sql-server-execution-plans/

Also related to this topic: The issue came up right out of the gate on
the Herding Code episode 38 podcast that was published on March 13th.
http://herdingcode.com/?p=171

[Fabio Maulo]

Now I understood who is the author.

2009/3/15 Daniel Auger <daniel...@gmail.com>

--
Fabio Maulo

[Daniel Auger]

I apologize that I wasn't clearer in my original post. When I said
that I came across the issue, I meant that I stumbled upon the
original blog post. I am not the author of the blog post, but merely a
C# developer trying to get DBA buy in ;).

On Mar 15, 4:55 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> Now I understood who is the author.
>

> 2009/3/15 Daniel Auger <daniel.au...@gmail.com>

>
>
>
>
>
> > I'd like to add a little bit of followup on this issue. The original
> > blog author posted a new entry the day after I started this thread.
> > The author indicates that prepare_sql does alleviate the issue.
>

> >http://scarydba.wordpress.com/2009/03/11/a-fix-for-nhibernates-parame...

>
> > In that entry the author points to the following:
>

> >http://testdrivendevelopment.wordpress.com/2009/03/10/nhibernate-quer...

[Fabio Maulo]

Ok... Note that the author had a chat with me about this matter.

BTW another time.... ok that you can do it in NH but the real issue is:

why Firebird can choose the right plan without an explicit parameter length in the query ?

why MsSQL need so complicated clause to simply do the same others RDBMS are doing without any sql clause addition ?

I think that somebody should write to MsSQL team.

P.S. I don't remember why the "prepare_sql" property is set to "false" in MsSQL dialect.

2009/3/15 Daniel Auger <daniel...@gmail.com>

--
Fabio Maulo

[Dario Quintana]

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.prepare.aspx

On Sun, Mar 15, 2009 at 7:14 PM, Fabio Maulo <fabio...@gmail.com> wrote:

P.S. I don't remember why the "prepare_sql" property is set to "false" in MsSQL dialect.

--
Dario Quintana
http://darioquintana.com.ar

[Fabio Maulo]

2009/3/15 Dario Quintana <cont...@darioquintana.com.ar>

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.prepare.aspx

without that parameter the drive (in nh) don't set parameter size.

--
Fabio Maulo

[Dario Quintana]

BTW, thanks to Claudio Maccari we have write it down
http://nhforge.org/wikis/howtonh/tuning-queries-with-ms-sqlserver.aspx

[Carsten Hess]

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 <conta...@darioquintana.com.ar>
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 <fabioma...@gmail.com> wrote:
> > 2009/3/15 Dario Quintana <conta...@darioquintana.com.ar>

>
> >> 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...

[Fabio Maulo]

2009/4/1 Carsten Hess <carsten.hess.dk@gmail.com>

That must be bug / leftover ?

We have a specific test about this matter.

--
Fabio Maulo

[Carsten Hess]

Hello Fabio,

Not exactly sure what you mean by that... ?

But wouldn't you say that

<property name="Name" type="String(100)">

<property name="Name" type="String" length="100">
<property name="Name" length="100"> // Type is automatically
deducted as string

Should be read as exactly the same by the mapping parser?

(And one could argue that there is no need for the syntax "String
(100)" when we have the length field).

Regards
Carsten

On Apr 1, 4:06 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> 2009/4/1 Carsten Hess <carsten.hess...@gmail.com>

[Fabio Maulo]

I maen that it is a know "problem".

Btw to "optimize" MsSQL plan-choosing you should set the IType of each query parameter (really a PITA).

Don't forget to send a mail to MsSQL team.

--
Fabio Maulo

[Carsten Hess]

Hello Fabio - in order to optimize SQL servers usage of executionplans
its actually enough to set the size of the SqlParameter to a fixed
(max) length which is exactly what SetParameterSizes in
SqlClientDriver.cs does.

So you just need to change

if (IsPrepareSqlEnabled)
{
SetParameterSizes(command.Parameters, parameterTypes);
}

to
SetParameterSizes(command.Parameters, parameterTypes);

The only thing is that it does not respect the mapping-configuration
of max sizes for strings unless (you use type="String(100)" for
instance) - but thats a different matter)

Due to this fact, the varchar types of the parameterized queries will
typically be of size 4000 - but that does not harm the executionplans,
so its not a showstopper.

Regards
Carsten Hess


On Apr 2, 3:54 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> I maen that it is a know "problem".Btw to "optimize" MsSQL plan-choosing you

> should set the IType of each query parameter (really a PITA).
>
> Don't forget to send a mail to MsSQL team.
>

> 2009/4/2 Carsten Hess <carsten.hess...@gmail.com>