Как оптимизировано обновлять несколько строк в большой таблице?

Question

[Алексей Лебедев]

Есть таблица, в ней:

7 столбцов (6 числовых и 1 текстовый)

Первый (id) является столбцом идентификатором.



Мне приходится часто делать такие запросы:

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

Запрос занимает около 60мс. Есть ли возможность уменьшить еще время выполнения?


Также:

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

Возможно и этот запрос тоже можно как-то оптимизировать?


PS. В таблице несколько сотен тысяч записей. Я обновляю обычно 1-5 строк.


Вкратце:

Если игрок взаимодействует с другим игроком, то это взаимодействие добавляется в таблицу. Раз в несколько секунд выполняется запрос от каждого игрока, который получает все возможные взаимодействия на игрока. И отмечает 2, т.е. прочитанные. Прочитать еще раз игрок может в архиве. Поэтому удалять нет возможности.


Исходный код:

SQLname = "SELECT fid, type, val, message from sw.dbo.logs where status=1 and uid=@id;";
                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("]}");
                }

Comments

[Алексей Лебедев]

UPDATE sw.dbo.logs SET status = 2 WHERE id IN (11111,222,444);

А если так, то будет ли быстрее?

Answer 1

[z130]

Можно симулировать версионность, чтобы превратить update в последовательность insert и delete, ну и select тоже придется немножко модифицировать.
То есть либо вставляете в базу новую версию полной строки, либо выносите статусы в отдельную табличку.
Старые и новые версии строк отличаете друг от друга по timestamp.
Старые версии соответственно потом прибиваете либо храните в качестве истории изменений статуса.

Comments

[Алексей Лебедев]

DELETE и INSERT может быть быстрее, чем сейчас?

[z130]

Да.
Вы делаете insert, который отрабатывается очень быстро, и после него уже можно читать новые данные.
Старые данные можно удалить потом либо вообще не удалять.
На больших объемах прирост скорости на порядок.

А почему вы думаете ВКонтакте и Facebook не удаляют данные? ))))))

[z130]

Кстати, на id кластерный первичный ключ не пробовали вешать? Или на id + status? Может сильно помочь.

[Алексей Лебедев]

Кинул скрипт и описание, возможно не правильное проектирование.

[z130]

1) Индекс на таблице постройте для начала, возможно этого хватит — по скриншоту видно что сейчас индексов нет вообще.
2) Можно вынести работу со статусами в отдельную таблицу uid, status, тогда select будет что-то типа:
SELECT fid, type, val, message from sw.dbo.logs where status=1 and uid=id and uid not in (select uid from table2 where status = 2)
Тогда обновление статуса будет что-то типа:
insert into table 2 values (uid, 2)
Соответственно, под ваш жизненный цикл поправите работу с таблицами

[Алексей Лебедев]

Окей. Спасибо.

Answer 2

[egorinsk]

Прежде чем гадать, проанализировали бы выполнения запроса, и посмотрели, что именно так долго делается. Может, у вас тормозит не обновление, а поиск и просто индекса по полю uid не хватает?

Answer 3

[Tr1aL]

bulk insert…
bulk update…

Comments

[Алексей Лебедев]

Ради 5 строк это можно использовать?

[Tr1aL]

можно, почему нет?
хотя в вашем случае просто выполнение этих 5ти запросов в одной транзакции даст прирост.

[Алексей Лебедев]

Получаем 5 запросов, вместо 1:

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

Я думаю, это вряд ли сделает лучше.

[Tr1aL]

конструкция in (..) будет однозначно быстрее работать. 5 запросов в одной транзакции будут чуть медленнее, 5 отдельных транзакций (как сейчас вы и делаете) — самое медленное решение.

[Алексей Лебедев]

Понятно, буду еще думать над структурой. Скинул исходный код, может я неверно спроектировал.