My update takes so long to execute in C# but not in sql

Question

I have this procedure :

CREATE PROCEDURE UpdateVolunteers 
    @Vol_ID                   INT,
    @Vol_Name                 NVARCHAR(50),
    @Vol_Zone                 NVARCHAR(50),
    @vol_street               NVARCHAR(50),
    @Vol_Sex                  INT,
    @Vol_Date_of_Birth        DATE,
    @Vol_Home_Phone           INT,
    @Vol_Work_Phone           INT,
    @Vol_Mobile1              INT,
    @Vol_Mobile2              INT,
    @Vol_Email                NVARCHAR(50),
    @Vol_Job                  NVARCHAR(50),
    @Vol_Affiliation          NVARCHAR(50),
    @vol_Education            INT,
    @vol_Education_Place      NVARCHAR(50),
    @vol_Education_Department NVARCHAR(50),
    @vol_Interesting          INT,
    @Team_ID                  INT,
    @vol_Notes                NVARCHAR(50)
AS
    IF @team_id = -1
      BEGIN
          SET @team_id = NULL
      END

    UPDATE Personal_Info
    SET    Vol_Name = @Vol_Name,
           Vol_Zone = @Vol_Zone,
           vol_street = @vol_street,
           Vol_Sex = @Vol_Sex,
           Vol_Date_of_Birth = @Vol_Date_of_Birth,
           Vol_Home_Phone = @Vol_Home_Phone,
           Vol_Work_Phone = @Vol_Work_Phone,
           Vol_Mobile1 = @Vol_Mobile1,
           Vol_Mobile2 = @Vol_Mobile2,
           Vol_Email = @Vol_Email,
           Vol_Job = @Vol_Job,
           Vol_Affiliation = @Vol_Affiliation,
           vol_Education = @vol_Education,
           vol_Education_Place = @vol_Education_Place,
           vol_Education_Department = @vol_Education_Department,
           vol_Interesting = @vol_Interesting,
           Team_ID = @Team_ID,
           vol_Notes = @vol_Notes
    WHERE  Vol_ID = @Vol_ID 

and I called it to C# application by this code:

 private static void UpdateVolunteer(string volID, string volName, string volZone,
 string volStreet, int volSex, DateTime volBirthday, int volHomePhone, 
 int volWorkPhone, int volMobile1, int volMobile2, string volEmail, 
 string volJob, string volAffiliation, int volEducation, 
 string volEducationPlace, string volEducationDepartment, int volInteresting, 
 int Team_ID, string volNotes)
 {
    try
    {
       SqlCommand com = new SqlCommand("UpdateVolunteers", con);

       com.Parameters.Add("@Vol_ID", SqlDbType.Int).Value = volID;
       com.Parameters.Add("@Vol_Name", SqlDbType.NVarChar, 50).Value = volName;
       com.Parameters.Add("@Vol_Zone", SqlDbType.NVarChar, 50).Value = volZone;
       com.Parameters.Add("@vol_street", SqlDbType.NVarChar, 50).Value = volStreet;
       com.Parameters.Add("@Vol_Sex", SqlDbType.Int).Value = volSex;
       com.Parameters.Add("@Vol_Date_of_Birth", SqlDbType.DateTime).Value = volBirthday;
       com.Parameters.Add("@Vol_Home_Phone", SqlDbType.Int).Value = volHomePhone;
       com.Parameters.Add("@Vol_Work_Phone", SqlDbType.Int).Value = volWorkPhone;
       com.Parameters.Add("@Vol_Mobile1", SqlDbType.Int).Value = volMobile1;
       com.Parameters.Add("@Vol_Mobile2", SqlDbType.Int).Value = volMobile2;
       com.Parameters.Add("@Vol_Email", SqlDbType.NVarChar, 50).Value = volEmail;
       com.Parameters.Add("@Vol_Job", SqlDbType.NVarChar, 50).Value = volJob;
       com.Parameters.Add("@Vol_Affiliation", SqlDbType.NVarChar, 50).Value = volAffiliation;
       com.Parameters.Add("@vol_Education", SqlDbType.Int).Value = volEducation;
       com.Parameters.Add("@vol_Education_Place", SqlDbType.NVarChar, 50).Value = volEducationPlace;
       com.Parameters.Add("@vol_Education_Department", SqlDbType.NVarChar, 50).Value = volEducationDepartment;
       com.Parameters.Add("@vol_Interesting", SqlDbType.Int).Value = volInteresting;
       com.Parameters.Add("@vol_Notes", SqlDbType.NVarChar, 50).Value = volNotes;
       com.Parameters.Add("@Team_ID", SqlDbType.Int);
       com.Parameters["@Team_ID"].Value = Team_ID;

       com.CommandType = System.Data.CommandType.StoredProcedure;
       if (con.State != ConnectionState.Open)
          con.Open();
          com.ExecuteNonQuery();
          con.Close();
   }
   catch (SqlException sqlEx)
   {
      if (con.State == ConnectionState.Open)
         con.Close();
         throw sqlEx;
   }
}

And this Function in C#: Called in Button_Click event

public void showVolunteers(int x)
    {
        SqlCommand com = new SqlCommand("SELECT [Vol_ID]
  ,[Vol_Name]
  ,[Vol_Zone]
  ,[vol_street]
  ,[Vol_Sex]
  ,[vol_Age]
  ,[Vol_Date_of_Birth]
  ,[Vol_Home_Phone]
  ,[Vol_Work_Phone]
  ,[Vol_Mobile1]
  ,[Vol_Mobile2]
  ,[Vol_Email]
  ,[Vol_Job]
  ,[Vol_Affiliation]
  ,[vol_Education]
  ,[vol_Education_Place]
  ,[vol_Education_Department]
  ,[vol_Interesting]
  ,[vol_Hours]
  ,[vol_Notes]
  ,[Team_ID]
  FROM [VolunteersAffairs].[dbo].[Personal_Info]", con);
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet dats = new DataSet();
        da.SelectCommand = com;
        da.Fill(dats, "Personal_Info");
        dataGridViewX1.DataSource = dats.Tables["Personal_Info"];
        dataGridViewX1.Columns[2].DefaultCellStyle.NullValue = "Nothing";
        updateComboBox.SelectedIndex = updateComboBox.Items.Count - 1;
        if (x != -1)
        {
            dataGridViewX1.Rows[x].Selected = true;
            dataGridViewX1.CurrentCell = dataGridViewX1[0, x];
        }
        labelItem1.Text = "Number of Volunteers = " + dataGridViewX1.Rows.Count;
        int X = 0, Y = 0;
        foreach (DataGridViewRow n in dataGridViewX1.Rows)
        {
            if (n.Cells[5].Value.ToString() == "Male")
                X++;
            else
                Y++;
        }
        labelItem2.Text = "   |   Number of Males = " + X + " |Number of Female = " + Y;

        //When I put the following as a comment the update is so fast, else very slowly.
       //This operation takes the birthday of volunteers and calculate the age for every one. 
       //And calculate the age in runTime and the value of the column in database is null and always be.
        #region the problem has been detected here
        DateTime brithday;
        for (int i = 0; i < dataGridViewX1.Rows.Count; i++)
        {
            brithday = (DateTime)dataGridViewX1[6, i].Value;
            TimeSpan age = DateTime.Today - brithday;
            dataGridViewX1[7, i].Value = age.Days / 365;
        }
        #endregion

   }

The problem is that the code takes so long (about 1 min) to execute when I update the table via the form of C#, but it so fast when I update it via code in sql query. Is the problem in the code of C# or in the called procedure? Tell me what is the solution for this problem. Please!!

Answer 1

You have this in a try catch block. Try catch blocks are expensive, and you probably don't need one to perform an sql query.

try catch performance

Instead of a try block, use a using block, which will instruct the garbage collector not to dispose the connection or command and the connection will be closed at the end of the using block as well.

using (var connection = new SqlConnection(connectionString))
{
  using (var command = new SqlCommand("UpdateVolunteers", connection))
  {
    command.Parameters.AddWithValue("@Vol_ID", volID);
    // other paramters...

    connection.Open();
    command.ExecuteNonQuery();
  }
}