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();
        }
    }
}

What is the use case here? User somehow edit all 10k rows, and you update those data to your database? Or you update all 10k rows even when user only update 1 row in grid? IMO with that large number of row, it should be an INSERT instead of UPDATE, and should be an one_time_insert instead of 10k UPDATE — Pham X. Bach, Mar 24 '17 at 07:36
in the first I select tow values ("glid" and "date") and take the values from my datagrid view if exist I make update if no I make insert all values I take from my datagrid view — Ahmad Hassan, Mar 24 '17 at 07:39
Tag your DBMS. Check it supports collection parameters, see http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure for example. — Serg, Mar 24 '17 at 08:01

Abdullah Dibas · Accepted Answer · 2017-03-24T08:52:42.510

2

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
     }
   }
 }
}

edited Mar 24 '17 at 08:52

answered Mar 24 '17 at 07:36

Abdullah Dibas

1,499
1
9
13

On each row you are executing a select command to check if that row's values are already exist in your database. So why don't you select all your table data before starting the for loop and cache it inside dataTable object or something and check on this object instead. – Abdullah Dibas Mar 24 '17 at 07:46
I you mean that I enter select command in for loop I do it now but still very slow It make update for 500 row in one minute – Ahmad Hassan Mar 24 '17 at 07:52
but all the data in SQL table I need I want just update table this table regarding clients balances i take it from excel and put in datagridview some of value i this table changed and some not so i need to check every row in data gridview if this row exis just make update for balance and if not make insert to ("glid","Name","Date","Balance") – Ahmad Hassan Mar 24 '17 at 07:55
No I've meant to make your select command OUT of your for loop. – Abdullah Dibas Mar 24 '17 at 07:58
You may have to change your select stored procedure to retrieve all your data => I mean it should not include a where statement – Abdullah Dibas Mar 24 '17 at 08:22
every loop i select about this datagridview from SQL table if this row exist i have to make update just balance if this row not exist i have to insert it in SQL table – Ahmad Hassan Mar 24 '17 at 08:25
i used your code but the same slow 22 row per second – Ahmad Hassan Mar 26 '17 at 21:57
I don't know if this will make any difference but you don't actually need to initialize and dispose the SqlCommand using "USING" statement for the insert or the update commands each iteration in the FOR loop, you just need to initialize it once before the loop and once after the loop, and for sure you still need to update the parameters each iteration. – Abdullah Dibas Mar 27 '17 at 08:42

score 2 · Answer 2 · answered Mar 24 '17 at 08:12

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

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

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;
    }

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!

thank you but my issue regarding to loop every row alone in datagridview i select every row and check this value in sql table if this row exist i have to make update in the columns balance and if not i have to make insert to this row in SQL table note : i take the data that exist in datagridview from excel — Ahmad Hassan, Mar 24 '17 at 08:29
you can loop every row and add it to a list, then pass all to sql for check exist, update or insert. — TriV, Mar 24 '17 at 08:36

Update SQL table very slow

2 Answers2