-1

I need to update a lot of rows in a database.

Currently I'm using the following code:

string cmd = ""; 
cmd = "UPDATE [" + symb + "] SET V1='" + v1.ToString() + "',V2 ='" + v2.ToString() + "',V3='" + v3.ToString() + "' Where V4='" + v4.ToString("yyyyMMdd HH:mm:ss.mmm") + "' AND V5='" + v5.ToString() + "'";

l_Cmd = new SqlCommand(cmd, access_connection);
l_Cmd.ExecuteNonQuery();

Since I have to update 500/700k rows, it takes a lot of time.

Have you got any advice?

Sorry for previous post, but all values are different:

for (int a = 0; a < tot_rows; a++)
{
    string cmd = "";
    cmd = "UPDATE [" + symb + "] SET V1='" + v1[a].ToString() + "',V2 ='" + v2[a].ToString() + "',V3='" + v3[a].ToString() + "' Where V4='" + v4[a].ToString("yyyyMMdd HH:mm:ss.mmm") + "' AND V5='" + v5[a].ToString() + "'";

    l_Cmd = new SqlCommand(cmd, access_connection);
    l_Cmd.ExecuteNonQuery();
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Antonio
  • 19
  • 1
  • 9
  • 8
    Use Parameterized Queries please. – jAC Jun 07 '17 at 16:24
  • 4
    http://bobby-tables.com/ – Sean Lange Jun 07 '17 at 16:25
  • If there are so many identical row values, why aren't you building a second table for that value, reference it via foreign key and update this one value in the new table? – jAC Jun 07 '17 at 16:27
  • 4
    [Table Valued Parameters](https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx) – Ivan Stoev Jun 07 '17 at 16:27
  • Janes, in following posts, I will use parametrized queries. About possible duplicate, I haven' t found a satisfactory answer. – Antonio Jun 07 '17 at 16:27
  • Janes, what function do I have to use about update through a new table? Ty – Antonio Jun 07 '17 at 16:29
  • 4
    It's not a function you need but a better database structure. – jAC Jun 07 '17 at 16:31
  • Maybe I wrote bad in the post, but all values are different. – Antonio Jun 07 '17 at 16:34
  • Do you have different tables with the same structure in your database? Seems to me like a bad design. Anyway, a table valued parameter should enable you to speed up your update significantly. – Zohar Peled Jun 07 '17 at 16:39
  • Your update does not have a WHERE to point out which one is to update. As a result, you will be updating everything with single value. Now, with it wrapped in forloop. you will be doing the same thing over and over. – jmag Jun 07 '17 at 16:41
  • I have two WHERE clauses – Antonio Jun 07 '17 at 16:42
  • 1
    It would be better if you could share your database structure and the use case in more detail. SQL is not really meant to be interacted with using loops like this, especially with the number of rows you're talking about (you say 700k, which I take to be 700000, which is far too many to be updating one at a time). There's likely an easier/better way of doing this. – Heretic Monkey Jun 07 '17 at 16:49

3 Answers3

0

You can try to implement a second (inner) loop that "collects" e. g. 100 UPDATE strings and concatenates them by ";" and then execute the 100 UPDATEs within one SqlCommand. As far as I remember that gave us a remarkable boost in a recent project.

IngoB
  • 2,552
  • 1
  • 20
  • 35
0

Add your array V1 datas in a tempary table on SQL Server, then you will can do an update between your 'symb' table and your temprary table.

Esperento57
  • 16,521
  • 3
  • 39
  • 45
0

Thank you, I solved using JOIN command with a temp table. Bye.

Antonio
  • 19
  • 1
  • 9