Slow to Execute Stored Procedure

  • Thread starter Thread starter Ed_P.
  • Start date Start date
E

Ed_P.

Hello,

I've recently started playing around with SQL 2000 and Stored procedures
and calling these stored procedures with a C# program. I have the
following setup: 1 Windows 2003 server running SQL 2000 (SP3). I have
created a small C# program to run a stored procedure called
"usp_CreateNewAddressType". It has two parameters, both of type
varchar: @AddressName varchar(30), @AddressDescription varchar(50).

When I run the code posted below on the server itself it takes about 3
seconds to run. Is this what I am too expect as far as running this
code? Or am I missing something that may make it run faster (note: the
slowness only happens when I run the application for the first time,
after that it inserts the two items to the db much faster).

Here is the code I am using:

//Constructor
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();

string storedProcedureName = "usp_CreateNewAddressType";
string connectionString =
"server=win2003srv;database=mytestdb;uid=sa;pwd=123";
this.mySQLConnection = new SqlConnection(this.connectionString);
this.command = new SqlCommand(storedProcedureName, mySQLConnection);

//Sets the command type to be of stored procedure type
this.command.CommandType = CommandType.StoredProcedure;
}

//Button Event that saves to DB
private void btnSaveToDB_Click(object sender, System.EventArgs e)
{
//This fills in the data that has been typed in to the text boxes
//this.param = this.command.Parameters.Add("@AddressName",
SqlDbType.VarChar, 30);
//this.param.Value = this.txtAddressTypeName.Text;

//this.param = this.command.Parameters.Add("@AddressDescription",
SqlDbType.VarChar, 50);
//this.param.Value = this.txtAddressTypeDescription.Text;

this.command.Parameters.Add("@AddressName", SqlDbType.VarChar,
30).Value = this.txtAddressTypeName.Text;
this.command.Parameters.Add("@AddressDescription", SqlDbType.VarChar,
50).Value = this.txtAddressTypeDescription.Text;

//Will execute the stored procedure
this.mySQLConnection.Open();
this.rows = this.command.ExecuteNonQuery();
this.mySQLConnection.Close();

//Shows' user that it worked
MessageBox.Show("Number of Rows affected: " + rows.ToString());
}

//End of Code

Can anyone point me to any reason as to why this is happening? Maybe I
am just expecting this to run faster, or this may just be normal. I
just want to make sure I am not missing anything that can make this code
run fast the first time you run the program. For example, should I put
some of the code in the load event of the windows form? Any advice
provided will be greatly appreciated!
 
It isn't the stored procedure causing the lag; it's the initial connection,
which I've found to be common with .Net. If you had a large program with a
lot of activity, you would notice that the first stored procedure called
would take a seemingly inordinate amount of time, while the rest would run
much quicker (regardless of which one you called first).
 
Back
Top