is there a performance benefit when using Prepare() on command thatuses plain sql?

[Lorenz Kahl]

Hi,

I was just reading the book "ADO.NET in a Nutshell".
In the chapter on the Command-Object (p. 36) it reads:

"A parameterized command won't improve performance as compared to the
original dynamic SQL statement. Unlike a stored procedure, a
parameterized query isn't stored in the database and isn't precompiled.
The difference is simply one of syntax."

OK. Sounds plausible to me. But i recalled reading something different
to that on MSDN. The article "Best Practices for Using ADO.NET"
(http://msdn.microsoft.com/library/en-us/dnadonet/html/adonetbest.asp?frame=true#adonetbest_topic4)
includes the following:

"The Command.Prepare method can improve the performance of parameterized
commands that are repeated at your data source. Prepare instructs the
data source to optimize the specified command for multiple calls. To use
Prepare effectively, you need to thoroughly understand how your data
source responds to the Prepare call. For some data sources such as SQL
Server 2000, commands are implicitly optimized and a call to Prepare is
unnecessary. For other data sources such as SQL Server 7.0, Prepare can
be effective."


So, who is right? Or am I just getting something wrong and mixed up?
Is the effect of the Prepare()-call specific to the data source?
Does it only work with SPs or also with plain SQL-Text?


Any help will be greatly appreciated.
Thanks in advance,
Lorenz

 

[Patrice]

It just depends the underlying DB. As said in the text it's likely to
improve SQL Server 7, but won't improve SQL Server 2000.
It stores temporarily the statement under a compiled form server side (this
is done automatically for SQL Server 2000).

It's probaly almost useless for plain text (IMO the server won't see you are
executing the same statement with different parameters as they are part of
the statement ie. the server sill see this as anoither statement each time).
SP are already compiled.

Of course you'll see a difference when you execute the same statement
multiple times. Should be quite easy to do your own small bench.

Patrice