How to update multiple rows in a large table in an optimized way?

Question

SQL

There is a table, in it:
7 columns (6 numeric and 1 text)
The first (id) is an identifier column.

I often have to make requests like this:

UPDATE sw.dbo.logs SET status = 2 WHERE uid = @id and status=1;

The request takes about 60ms. Is it possible to reduce the execution time even more?
Also:

INSERT INTO sw.dbo.logs VALUES (@id, @id, 40, 2, '', 1);

Perhaps this query can also be optimized somehow?
PS. The table has several hundred thousand records. I usually update 1-5 rows.
In short:
If a player interacts with another player, then that interaction is added to the table. Once every few seconds, a request is made from each player, which receives all possible interactions on the player. And marks 2, i.e. read. The player can read it again in the archive. So there is no way to delete it.
Source:

SQLname = "SELECT fid, type, val, message from sw.dbo.logs where status=1 and [email protected];";
                using (var comm = new SqlCommand(SQLname, conn, null))
                {
                    comm.Parameters.AddWithValue("@id", id);
                    using (var reader = comm.ExecuteReader())
                    {
                        Response.Write(",\"messages\": [");
                        while (reader.Read())
                        {
                            if (i > 0)
                            {
                                Response.Write(",");
                            }
                            Response.Write("{\"fid\":" + reader["fid"] + ",");
                            Response.Write("\"message\":\"" + reader["message"] + "\",");
                            Response.Write("\"type\":" + reader["type"] + ",");
                            Response.Write("\"val\":" + reader["val"] + "}");
                            uids.Add(reader["fid"].ToString());
                            i++;
                        }
                    }
                }
                if (i > 0)
                {
                    string tmp = "";
                    while (i > 0)
                    {
                        tmp += uids[i - 1] + " or vkid=";
                        i--;
                    }
                    Response.Write("],");
                    SQLname = "SELECT name, sex, vkid, photo from sw.dbo.users where vkid=" + tmp + "-1;";
                    using (var comm = new SqlCommand(SQLname, conn, null))
                    {
                        comm.Parameters.AddWithValue("@id", id);
                        using (var reader = comm.ExecuteReader())
                        {
                            Response.Write("\"uids\": [");
                            while (reader.Read())
                            {
                                if (i > 0)
                                {
                                    Response.Write(",");
                                }
                                Response.Write("{\"sex\":" + reader["sex"] + ",");
                                Response.Write("\"vkid\":" + reader["vkid"] + ",");
                                Response.Write("\"photo\":" + reader["photo"] + ",");
                                Response.Write("\"name\":\"" + reader["name"] + "\"}");
                                i++;
                            }
                            Response.Write("]}");
                        }
                    }
                    SQLname = "UPDATE sw.dbo.logs SET status = 2 WHERE uid = @id and status=1;";
                    using (var comm = new SqlCommand(SQLname, conn, null))
                    {
                        comm.Parameters.AddWithValue("@id", id);
                        comm.ExecuteNonQuery();
                    }
                    int time, unique;
                    string sig, xml, suc;
                    time = UnixTime();
                    unique = rnd.Next(50000);
                    sig = MD5("api_id=" + apiid + "counter=0method=secure.setCounterrandom=" + unique + "timestamp=" + time + "uid=" + id + "v=2.0" + paykey);
                    xml = @"http://api.vk.com.ru/api.php?api_id=" + apiid + "&method=secure.setCounter&random=" + unique + "&timestamp=" + time + "&uid=" + id + "&v=2.0&counter=0&sig=" + sig;
                    suc = XmlGet(xml, "response");
                }
                else
                {
                    Response.Write("]}");
                }

Answer 1

You can simulate versioning to turn update into an insert and delete sequence, and select also needs to be slightly modified.
That is, either insert a new version of the full line into the database, or take out the statuses in a separate plate.
Old and new versions of strings are distinguished from each other by timestamp.
Old versions, respectively, then nail or store as a history of status changes.

Answer 2

Before guessing, we would analyze the execution of the request, and see what exactly is being done for so long. Maybe it’s not the update that slows you down, but the search and just the index on the uid field is not enough?

Answer 3

bulk insert…
bulk update…