Simple deletion works very long (about 40 seconds from one table, in which about 1.5 million records), you need to do it faster (you need to delete all user data). Base MSSQL.

Closed due to the fact that the essence of the question is incomprehensible by the participants Vladimir Martianov , aleksandr barakin , Pavel Parshin , user194374, Grundy 13 Feb '16 at 21:24 .

Try to write more detailed questions. To get an answer, explain what exactly you see the problem, how to reproduce it, what you want to get as a result, etc. Give an example that clearly demonstrates the problem. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • 2
    And how many records are deleted. Are there any foreign keys that reference the table with the records to be deleted. How many indexes are created on this table. - Mike
  • one
    Yes, in more detail: would all the data be deleted from the table altogether , or is part of the data deleted, and some remain? How are rows selected for deletion (by key or other fields), are there indices? - i-one
  • One index is specified, in two fields. - AndrijM
  • 2
    @AndrijM And how long does the select count (1) work from the table where user_id = XX? The index will of course slow down the delete operation itself, and inserts into the table at the same time, but it will allow to find those records that need to be deleted much faster. If select count () is too slow, try creating an index and see what happens - Mike
  • one
    In my opinion, if deleting is a frequent operation, or relatively infrequent, but still time-critical, then the index should be added. "If 20k?" I would add. Just do not ask "and if 100k?". :) Yes, there may be a certain threshold, after which the query processor may decide that the table scan will go faster than the index seek. This threshold will depend on the data and reliability of the statistics. - i-one

1 answer 1

If you want to remove ALL entries from a table, use TRUNCATE, after deleting foreign keys that reference the table. This is the fastest way. He, in particular, ignores triggers.

If just a large number, then you need to remove packs. I optimized job, which deleted obsolete data, about ~ 100 million. lines per week. run on the weekend. Experienced to come to the conclusion that the fastest line is removed in batches of 100-500 pieces.

We create a temporary table with IDs of records that we will delete. We write there 100-500 IDs. and call:

DELETE T2 FROM #Table T1 INNER LOOP JOIN Table T2 ON T1.ID = T2.ID

Here you need LOOP. Although the server is likely to guess without hints. Instead of the ID of the Table, there can be any unique key. If the key is clustered, then it is advisable to insert the IDs of the row bundles that are written in the Table "next" to the #Table table.

If the whole package could not be deleted, we call for deleting one record at a time and log the results (well, I did that :)) and then we analyze it in the morning.

UPD : about the quick search for deleted records. It is necessary to create an index on UserId and it will look better or not.

UPD : a little bit about why it is packs.

Deletion on one record is N transactions. It is executed rather slowly ... That was the job before I started optimizing it. Trying to delete ~ 1 million lines with a single DML statement is also bad. Because The transaction itself becomes very large and we risk getting out of memory. Yes, and it takes a long time.

As a result, setting the pack size, I selected the optimal number of simultaneously deleted records for each table. The boss was pleased (c) :)

  • I agree. Thank you, I will also introduce myself! Previously, I used the entire table drop and created a new one. :) - Vladimir Ch
  • @VladimirCh, is this about TRUNCATE? You 're welcome :) - pegoopik
  • Thanks, I will try - AndrijM
  • 2
    There, only the nuance is such that truncate will not work if there are foreign keys that reference the table. - i-one
  • @ i-one, so it is logical. Drop and re-create the table foreign key also hurt. In response, added, thanks. ps: how do you make a monofire font in the comments? - pegoopik