-2

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

  • Why don't use a SQL query to throw this update? – McNets Aug 11 '17 at 10:12
  • What do you mean? @McNets –  Aug 11 '17 at 10:15
  • 1
    Execute this query from SSMS instead of use C# code. – McNets Aug 11 '17 at 10:17
  • You could start by improving your code's security and using parameters instead of allowing SQL injections – Camilo Terevinto Aug 11 '17 at 10:17
  • i guess he means that he didn't want to iterate all lines from his file and call 200k update queries. He just wants to call one query that updates all records. Please clearify your question. – Rabban Aug 11 '17 at 10:35
  • So really you have data in a file that you want to apply to data in a table. If you want to use C# to do this, investigate _table valued parameters_. But there are better ways to do this like importing the file into a staging table and running one single update to apply the data, which will be 100 times faster – Nick.Mc Aug 11 '17 at 10:40

1 Answers1

0

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.

James Casey
  • 2,447
  • 1
  • 11
  • 19