Update SQL table very slow

Question

I have problem when I try to update SQL table with I have datagridview and I need to update SQL table and take the value form my datagridview . my datagridview have more than 10000 rows I take time more than 1:30 hour very slow

datagridview name "dgv_balance"

Here is the code:

using (SqlConnection cn = new SqlConnection())
{
    cn.ConnectionString = "My Connection"
    cn.Open();
    using (SqlCommand cmd_select = new SqlCommand())
    {
        for (int i = 0; i < dgv_balance.RowCount; i++)
        {
            cmd_select.Connection = cn;
            cmd_select.CommandType = CommandType.StoredProcedure;
            cmd_select.CommandText = "clients_balances_select_glid_date";
            cmd_select.Parameters.AddWithValue("@glid", Convert.ToString(dgv_balance.Rows[i].Cells[0].Value));
            cmd_select.Parameters.AddWithValue("@date", Convert.ToDateTime(dgv_balance.Rows[i].Cells[2].Value));
            if (cmd_select.ExecuteScalar().ToString()=="")
            {
                using (SqlCommand cmd_insert = new SqlCommand())
                {
                    cmd_insert.Connection = cn;
                    cmd_insert.CommandType = CommandType.StoredProcedure;
                    cmd_insert.CommandText = "clients_balances_insert_data";
                    cmd_insert.Parameters.AddWithValue("@glid", Convert.ToString(dgv_balance.Rows[i].Cells[0].Value));
                    cmd_insert.Parameters.AddWithValue("@name", Convert.ToString(dgv_balance.Rows[i].Cells[1].Value));
                    cmd_insert.Parameters.AddWithValue("@date", Convert.ToString(dgv_balance.Rows[i].Cells[2].Value));
                    cmd_insert.Parameters.AddWithValue("@balance", Convert.ToString(dgv_balance.Rows[i].Cells[3].Value));
                    cmd_insert.ExecuteNonQuery();
                    cmd_insert.Parameters.Clear();
                }
            }
            else
            {
                using (SqlCommand cmd_update= new SqlCommand())
                {
                    cmd_update.Connection = cn;
                    cmd_update.CommandType = CommandType.StoredProcedure;
                    cmd_update.CommandText = "clients_balances_update_balance";
                    cmd_update.Parameters.AddWithValue("@glid", Convert.ToString(dgv_balance.Rows[i].Cells[0].Value));
                    cmd_update.Parameters.AddWithValue("@date", Convert.ToString(dgv_balance.Rows[i].Cells[2].Value));
                    cmd_update.Parameters.AddWithValue("@balance", Convert.ToString(dgv_balance.Rows[i].Cells[3].Value));
                    cmd_update.ExecuteNonQuery();
                    cmd_update.Parameters.Clear();
                }
            }
            cmd_select.Parameters.Clear();
        }
    }
}

Answer 1

You may have to call SELECT command for one time only before you loop through your datagridview rows and cache the result data and check on the result while iterating your datagridview instead of calling it on each row. This way you will reduce your commands by 10000. It also better if you could show us your procedures' queries. Or if your datagridview is the ONLY source for your data then you can delete all your previous data in your database and make one insert call for all of your datagridview data.

Try this:

using (SqlConnection cn = new SqlConnection())
{
 cn.ConnectionString = "MyConnection" ;
 cn.Open();
 SqlDataAdapter da = new SqlDataAdapter(); 
 DataTable dt = new DataTable();
 using (SqlCommand cmd_select = new SqlCommand()) 
 {
   cmd_select.Connection = cn;      cmd_select.CommandType =   CommandType.StoredProcedure;    cmd_select.CommandText =   "clients_balances_select_glid_date";
  da.SelectCommand = cmd_select; 
  da.Fill(dt);
  for (int i = 0; i < dgv_balance.RowCount; i++) 
  { 
    if(/* check here if dt contains this    row*/)
    {
        // Insert
    }
    else 
    {
        // Update
     }
   }
 }
}

Answer 2

I think you should insert or update all data one time.

1. Create index for glId column. If glId is primary key, it's indexed.

2. Assumes that List ClientBalance is list items you need update or insert.

   public class ClientBalance
   {
      GlId int {get;set;}
      ClientName string {get;set;}
      Balance decimal {get;set;}
      DateInput DateTime {get;set;}
   }
   

You could serialize list Item to xml string and pass it to store procedure

   public  string Serialize<T>(T value) where T : new()
    {
        var serializeXml = string.Empty;            
        if (value != null)
        {
            try
            {
                var xmlserializer = new XmlSerializer(typeof(T));
                var stringWriter = new StringWriter();
                var writer = XmlWriter.Create(stringWriter);
                xmlserializer.Serialize(writer, value);
                serializeXml = stringWriter.ToString();

                writer.Close();
            }
            catch (Exception ex)
            {                   
                return string.Empty;
            }
        }
        return serializeXml;
    }

3. Create a new store procedure for insert or update item like that:

       CREATE PROCEDURE [dbo].[clients_balances_insert_or_update]
        (
           @xmlObject nvarchar(max)
        )
        AS
        BEGIN
             -- TABLE INCLUDE DATE FROM XML
           DECLARE @tblBalances AS TABLE
           (
              GlId int,
              DateInput datetime,
              ClientName nvarchar(50),
              Balance decimal(18,2)
           )
           DECLARE @idoc int -- xml id
   
            -- PARSE XML TO OBJECT
           EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlObject 
           INSERT INTO @tblBalances
                           (
                               GlId, DateInput, ClientName, Balance
                           )
                           SELECT s.GlId, s.DateInput, s.ClientName, s.Balance
                           FROM    OPENXML (@idoc, '/ArrayOfClientBalance/ClientBalance', 8) WITH (
                                       GlId            int 'GlId',                     
                                       DateInput   datetime 'DateInput',
                                       ClientName NVARCHAR(50) 'ClientName',
                                       Balance DECIMAL(18,2) 'Balance'
                                   ) s
           EXEC sp_xml_removedocument @idoc
   
           -- USE MERGE FOR INSERT OR UPDATE DATE
           -- Use transaction 
           BEGIN TRAN InsertOrUpdate
           BEGIN TRY
               MERGE Target AS T
               USING @tblBalances AS S
               ON (T.GlId = S.GlId) 
               WHEN NOT MATCHED BY TARGET 
                  THEN INSERT( GlId, DateInput, ClientName, Balance) VALUES( GlId, DateInput, ClientName, Balance)
               WHEN MATCHED 
                  THEN UPDATE SET DateInput = S.DateInput, Balance = s.Balance
   
               COMMIT TRAN InsertOrUpdate;
           END TRY
           BEGIN CATCH
               ROLLBACK TRAN InsertOrUpdate;
               THROW;
           END CATCH
        END  
   

Hope this helpfully!