In general, it is necessary to remove about 10,000 records from the Nodes table, only their id is not indicated explicitly, but taken from another table.

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

In SELECT DISTINCT id FROM @gh , there are about 20,000 records and as a result of the deletion, the expired timeout is not performed.

Update : If you delete by package and remove DISTINCT, then another error occurs due to the lack of memory and the completion of the SQL Server process

 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)

alt text

    4 answers 4

    1. If node_id is unique, create a key on id.
    2. Remove DISTINCT.
    3. Try using an external connection instead of NOT IN.
    4. Break into portions by id range.
    • poorly understood: node_id is not unique because DISTINCT. But about the last 2 points you can read more? - LackOfKnowledge
    • Distinct is still not needed. It causes extra sorting. 3. DELETE FROM Node from nodes left join Relations on id = node_id WHERE id is null 4. Delete in a loop, breaking id into ranges. - msi
    • Please see I updated the header. - LackOfKnowledge
    • Pack in node_id. - msi
     DELETE FROM Nodes WHERE NOT EXISTS (SELECT 1 FROM Relations r WHERE r.node_id = id)

    And create an index on Relations.node_id.

    • node_id has an index. Please see I updated the header. - LackOfKnowledge
    • You can also try TOP 1 in the subquery to add. - Ilya Pirogov

    1. There are requests that will be executed for a long time despite any tricks. As far as I understand, this is a connection property from the client, not from the server. In MS Managament Studio, this is set in Tools-> Options-> Request Execution-> Request Waiting Verb.

    2. Try this syntakis

      DELETE FROM Nodes FROM Nodes N JOIN Relations R ON R.node_id=N.id

      Actually the problem is solved in a third-party way through ADO.NET. At first I loaded into the list all id's from the Nodes table. Then I loaded node_id from the Relations table to another list. Finally, I ran through the first list in a loop, performing a request to delete those lines whose id is not contained in the second list. This completely solves the problem of memory accumulation.

        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<int>(); 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<int>(); 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(); }