On Aug 20, 3:59 am, GlennDoten <gdo...@gmail.c omwrote:
But, um, yes, single-statement sprocs are indeed faster than their
inline SQL counterparts. Every time. Enkidu, there's no skill involved
there. If you time running, say, a single SELECT run via inline SQL and
the same SELECT run via a sproc then sproc will always be faster. A lot
faster? Not really. But faster nonetheless.
Glenn, that's a sweeping statement that's no longer always true. SQL
Server 2005 does a much better job reusing execution plans for
parameterized statements, even when the parameter values change
between queries. Here's MSDN, under the execute_sql entry:
<MSDN>
In earlier versions of SQL Server, the only way to be able to reuse
execution plans is to define the Transact-SQL statements as a stored
procedure and have the application execute the stored procedure. This
generates additional administrative overhead for the applications.
Using sp_executesql can help reduce this overhead while still letting
SQL Server reuse execution plans. sp_executesql can be used instead of
stored procedures when executing a Transact-SQL statement several
times, when the only variation is in the parameter values supplied to
the Transact-SQL statement. Because the Transact-SQL statements
themselves remain constant and only the parameter values change, the
SQL Server query optimizer is likely to reuse the execution plan it
generates for the first execution.
</MSDN>
I put together a little benchmark to compare a simple query with its
equivalent stored procedure. I ran both 10,000 times. Here's what I
saw:
Stored procedure 10000 iterations. Elapsed time: 00:00:09.984311 1
Statement 10000 iterations. Elapsed time: 00:00:09.953061 3
(to be fair, sometimes the stored procedure would come out ahead.
they were always within half a percent of each other)
Here's my source:
using System;
using System.Data.Sql Client;
using System.Data;
namespace StatementVersus SP
{
class Program
{
static string server = XXXX
static string database = XXXX;
static string connString = "Data Source={0};Init ial
Catalog={1};Int egrated Security=True";
static void Main(string[] args)
{
Compare(10000);
Console.ReadLin e();
}
static private void Compare(int iterations)
{
// preload
for (int i = 0; i < 10; i++)
{
using (SqlConnection conn = new
SqlConnection(S tring.Format(co nnString, server, database)))
{
QueryWithStored Procedure(conn, i);
}
}
for (int i = 0; i < 10; i++)
{
using (SqlConnection conn = new
SqlConnection(S tring.Format(co nnString, server, database)))
{
QueryWithStatem ent(conn, i);
}
}
//
DateTime start;
DateTime end;
start = DateTime.Now;
for (int i = 0; i < iterations; i++)
{
using (SqlConnection conn = new
SqlConnection(S tring.Format(co nnString, server, database)))
{
QueryWithStored Procedure(conn, i);
}
}
end = DateTime.Now;
Console.WriteLi ne(String.Forma t("Stored procedure {0} iterations.
Elapsed time: {1}", iterations, end - start));
start = DateTime.Now;
for (int i = 0; i < iterations; i++)
{
using (SqlConnection conn = new
SqlConnection(S tring.Format(co nnString, server, database)))
{
QueryWithStatem ent(conn, i);
}
}
end = DateTime.Now;
Console.WriteLi ne(String.Forma t("Statement {0} iterations. Elapsed
time: {1}", iterations, end - start));
}
private static void QueryWithStored Procedure(SqlCo nnection conn, int
i)
{
// defined as:
//create proc test_sp
//@id int
//as
//SELECT * from TEST WHERE ID = @id
SqlCommand command = new SqlCommand("tes t_sp", conn);
command.Command Type = CommandType.Sto redProcedure;
command.Paramet ers.Add(new SqlParameter("@ id", i));
conn.Open();
using (IDataReader dr = command.Execute Reader())
{
dr.Read();
}
}
private static void QueryWithStatem ent(SqlConnecti on conn, int i)
{
SqlCommand command = new SqlCommand("SEL ECT * FROM Test WHERE ID =
@id", conn);
command.Command Type = CommandType.Tex t;
command.Paramet ers.Add(new SqlParameter("@ id", i));
conn.Open();
using (IDataReader dr = command.Execute Reader())
{
dr.Read();
}
}
}
}
Michael
