By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Explicit Parameters for Stored Procedures

P: n/a
I created a test to check the execution time difference between executing a
SQL Server stored procedured using explicit parameters versus not. In one
case I created new SqlParameters in the code, and added the parameters to
the SqlParametersCollection of the SqlCommand object. In the second, I just
made it all into long execution string. I found that both executed with
the same speed. I would claim that the second (shorter) method is easier to
maintain AND quicker to write. Why wouldn't we always use the second
method?

If you're interested, I've included the code same below that illustrates my
test. Thanks in advance.

Mark

******

SqlConnection sqlConn = new SqlConnection(strConn);
SqlCommand sqlComm = new SqlCommand("p_test", sqlConn);

// Mark the Command as a SPROC
sqlComm.CommandType = CommandType.StoredProcedure;

sqlComm.Parameters.Add(new SqlParameter("@param1", SqlDbType.VarChar, 30));
sqlComm.Parameters.Add(new SqlParameter("@param2", SqlDbType.VarChar, 50));
sqlComm.Parameters.Add(new SqlParameter("@param3", SqlDbType.VarChar, 30));
sqlComm.Parameters.Add(new SqlParameter("@param4", SqlDbType.VarChar, 10));
sqlComm.Parameters.Add(new SqlParameter("@param5", SqlDbType.VarChar, 255));
sqlComm.Parameters.Add(new SqlParameter("@param6", SqlDbType.VarChar, 255));
sqlComm.Parameters.Add(new SqlParameter("@param7", SqlDbType.VarChar, 50));

//Test fields
sqlComm.Parameters.Add(new SqlParameter("@param8", SqlDbType.Char, 1));
sqlComm.Parameters.Add(new SqlParameter("@param9", SqlDbType.DateTime));

sqlComm.Parameters["@param1"].Value = "whatever";
sqlComm.Parameters["@param2"].Value = "whatever";
sqlComm.Parameters["@param3"].Value = "whatever";
sqlComm.Parameters["@param4"].Value = "whatever";
sqlComm.Parameters["@param5"].Value = "whatever";
sqlComm.Parameters["@param6"].Value = "whatever";
sqlComm.Parameters["@param7"].Value = "whatever";

//Test params
sqlComm.Parameters["@param8"].Value = "Y";

for (Int32 i = 0; i < 10; i++)
{
//Execute the command
sqlComm.Parameters["@param9"].Value = DateTime.Now;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}

// ***** NOW TEST THE SECOND (QUICKER) METHOD *****

sqlComm.CommandType = CommandType.Text;
String strExecute = "exec p_test 'whatever', 'whatever', 'whatever',
'whatever', 'whatever', 'whatever', 'whatever','N'";

for (Int32 j = 0; j < 10; j++)
{
//Execute the command
sqlComm.CommandText = strExecute + ", '" + DateTime.Now.ToString() + "'";
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}
Nov 15 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Mark" <mf****@idonotlikespam.cce.umn.edu> wrote in message
news:Oh**************@TK2MSFTNGP09.phx.gbl...
I created a test to check the execution time difference between executing a SQL Server stored procedured using explicit parameters versus not. In one
case I created new SqlParameters in the code, and added the parameters to
the SqlParametersCollection of the SqlCommand object. In the second, I just made it all into long execution string. I found that both executed with
the same speed. I would claim that the second (shorter) method is easier to maintain AND quicker to write.
Why wouldn't we always use the second
method?


But you are in charge of converting all of the parameters to strings, which
can be a source of errors. And (turn on the profiler) you will be using SQL
Batches instead of RPC's to invoke the stored procedure. You probably won't
notice the difference running just one at a time, but the RPC mechanism is
more efficient on the network.
Moreover, the string method is not really any easier or shorter. Look at
the Data Access Application block for how to call a stored procedure with
bound parameters in a single, compact line of code.

David
Nov 15 '05 #2

P: n/a
Depending on how you are implementing the second, you may not be taking
advantage of cached execution plans. moreoever, you can't really conclude
from one test that they run the same speed and even if they do, that
everything always will. In addition, just let someone pass in the name
O'Reilly or anything with an apostrophe just once. The time it takes you to
acknwoledge the error then find it and fix it will be more time than you'll
ever save by using abbreviated syntax without params. I've heard sooo many
people claim that they'd never forget to check for an apostrophe, and
invariably they do. Then there's the issue of passing really long strings
which invariably occur over the network.

IMHO, the little bit of syntax saved is scarcely worth all of the headaches
that come with it.

HTH,

Bill

"Mark" <mf****@idonotlikespam.cce.umn.edu> wrote in message
news:Oh**************@TK2MSFTNGP09.phx.gbl...
I created a test to check the execution time difference between executing a SQL Server stored procedured using explicit parameters versus not. In one
case I created new SqlParameters in the code, and added the parameters to
the SqlParametersCollection of the SqlCommand object. In the second, I just made it all into long execution string. I found that both executed with
the same speed. I would claim that the second (shorter) method is easier to maintain AND quicker to write. Why wouldn't we always use the second
method?

If you're interested, I've included the code same below that illustrates my test. Thanks in advance.

Mark

******

SqlConnection sqlConn = new SqlConnection(strConn);
SqlCommand sqlComm = new SqlCommand("p_test", sqlConn);

// Mark the Command as a SPROC
sqlComm.CommandType = CommandType.StoredProcedure;

sqlComm.Parameters.Add(new SqlParameter("@param1", SqlDbType.VarChar, 30)); sqlComm.Parameters.Add(new SqlParameter("@param2", SqlDbType.VarChar, 50)); sqlComm.Parameters.Add(new SqlParameter("@param3", SqlDbType.VarChar, 30)); sqlComm.Parameters.Add(new SqlParameter("@param4", SqlDbType.VarChar, 10)); sqlComm.Parameters.Add(new SqlParameter("@param5", SqlDbType.VarChar, 255)); sqlComm.Parameters.Add(new SqlParameter("@param6", SqlDbType.VarChar, 255)); sqlComm.Parameters.Add(new SqlParameter("@param7", SqlDbType.VarChar, 50));
//Test fields
sqlComm.Parameters.Add(new SqlParameter("@param8", SqlDbType.Char, 1));
sqlComm.Parameters.Add(new SqlParameter("@param9", SqlDbType.DateTime));

sqlComm.Parameters["@param1"].Value = "whatever";
sqlComm.Parameters["@param2"].Value = "whatever";
sqlComm.Parameters["@param3"].Value = "whatever";
sqlComm.Parameters["@param4"].Value = "whatever";
sqlComm.Parameters["@param5"].Value = "whatever";
sqlComm.Parameters["@param6"].Value = "whatever";
sqlComm.Parameters["@param7"].Value = "whatever";

//Test params
sqlComm.Parameters["@param8"].Value = "Y";

for (Int32 i = 0; i < 10; i++)
{
//Execute the command
sqlComm.Parameters["@param9"].Value = DateTime.Now;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}

// ***** NOW TEST THE SECOND (QUICKER) METHOD *****

sqlComm.CommandType = CommandType.Text;
String strExecute = "exec p_test 'whatever', 'whatever', 'whatever',
'whatever', 'whatever', 'whatever', 'whatever','N'";

for (Int32 j = 0; j < 10; j++)
{
//Execute the command
sqlComm.CommandText = strExecute + ", '" + DateTime.Now.ToString() + "'";
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}

Nov 15 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.