Search code examples
c#sql-serverperformancecpu-speed

Very Very Slow Query Update on Sql Server

Hi I wanted to ask for something about sql server, I'm getting a c # software that needs to update the data on a sql server with an update, Now my problem is that the Parameters to Update Recently (Codice Barcode) is present on a different file From the import, so I have to enter (Codice Barcode) with an update query, My problem is that I have to update more than 200'000 items. I wanted to know if there was a way to speed things up on a Sql Server 2014 now as it now takes a lot to import everything:

Upgrade Query:

SqlConnection conn = db.apriconnessione();
            String Query = "Update Articolo set CodiceBarcode='"+CodiceBarcode+"' where CodMarca='"+SiglaMarchio+"' and CodArt='"+CodiceArticolo+"' ";

            SqlCommand cmd = new SqlCommand(Query, conn);

            try
            {
                cmd.ExecuteNonQuery();
            }

            catch(Exception ex)
            {

            }

           conn.Close();

Design of table articolo

enter image description here

enter image description here

Solution

  • You probably need an index on the columns in your where clause. Else it will be doing a table scan on each of your inserts.

    Also you are doing things one at a time. Much quicker would be to bulk load the data and then so a single update.