Удаление большого числа строк из таблицы Transact-SQL

#sql_server #sql

В общем нужно удалить из таблицы Nodes порядка 110000 записей, только их id указывается
не явно а берётся из другой таблицы.

DELETE FROM Nodes
WHERE  (id NOT IN
           (SELECT DISTINCT node_id
                   FROM Relations))


В SELECT DISTINCT id FROM @gh гдето около 20000 записей и в результате удаления не
совершаются изза истекшего таймаута. 

Update:
Если удалять по пакетам и убрать DISTINCT то возникает другая ошибка связанная с
нехваткой памяти и завершением процесса SQL Server

WHILE EXISTS(SELECT * FROM Nodes WHERE id NOT IN (SELECT node_id FROM Relations))
    DELETE TOP (100) Nodes WHERE id NOT IN (SELECT node_id FROM Relations)



    

Ответы

Ответ 1

Если node_id - уникальный, создайте ключ на id.
Уберите DISTINCT.
Попробуйте использовать внешнее соединение вместо NOT IN.
Разбивать на порции по диапазонам id.


Ответ 2

DELETE FROM Nodes
WHERE NOT EXISTS (SELECT 1 FROM Relations r WHERE r.node_id = id)

И создать индекс на Relations.node_id.


Ответ 3

Есть запросы, которые будут выполняться долго несмотря на любые ухищрения. Насколько
я понимаю, это свойство соединения со стороны клиента, а не со стороны сервера. В MS
Managament Studio это устанваливается в Сервис->Параметры->Выполнение запроса-> Вермя
ожидания запроса.


Попробуйте такой синтакис
DELETE FROM 
    Nodes 
FROM    Nodes N 
    JOIN Relations R ON R.node_id=N.id


Ответ 4

На самом деле проблема решена сторонним способом через ADO.NET. Сначала загрузил
в список всех id из таблицы Nodes. Затем в другой список загрузил node_id из таблицы
Relations. И наконец циклом пробежался по первому списку выполняя запрос на удаление
тех строк id которых не содержится во втором списке. Это полностью решает проблему
с накоплением памяти.
    using (var connection = new SqlConnection(MainWindow._connectionString))
    {
        connection.Open();
        var readerRelations =
            new SqlCommand("SELECT DISTINCT node_id FROM Relations", connection).ExecuteReader();
        var DistinctRelations = new List();
        while (readerRelations.Read())
        {
            DistinctRelations.Add((int)readerRelations.GetValue(0));
        }
        readerRelations.Close();

        var readerNodes = new SqlCommand("SELECT id FROM Nodes", connection).ExecuteReader();
        var AllNodes = new List();
        while (readerNodes.Read())
        {
            AllNodes.Add((int)readerNodes.GetValue(0));
        }
        readerNodes.Close();
        int iterator = 0;
        int NodesCount = AllNodes.Count;
        int lastpercent = 0;
        foreach (var node in AllNodes)
        {
            int percent = (int)(iterator * 1.0 / NodesCount * 100);
            if (percent == lastpercent + 1)
            {
                lastpercent = percent;
                DeleteUnnecesseryNodesChanged(this,
                                                new MyEventArgs("Удаление ненужных
точек", (int)percent));
            }
            if (!DistinctRelations.Contains(node))
            {
                try
                {
                    new SqlCommand("DELETE FROM Nodes WHERE id = " + node, connection).ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    var t = e.ToString();
                    throw new Exception(t);
                }
            }
            iterator++;
        }

        connection.Close();
    }