DB: bulk delete table

Progetti di esempio Foundation
, ,
1

InDeF 21.5, C# .NET 4.x, MS SQL Server.

Una delle nostre elaborazioni si basa su una decina di tabelle DB di appoggio, il cui contenuto viene mantenuto fintantoché non viene lanciata una nuova elaborazione. Al lancio della nuova elaborazione, tutti i record di appoggio vengono eliminati per poi procedere all’inserimento dei nuovi. La stessa elaborazione può essere lanciata da più utenti con parametri diversi, per cui le tabelle contengono i dati di appoggio di diverse sessioni di elaborazione.
Alcune di queste tabelle contengono oltre un milione di record, e la singola elaborazione è formata da oltre cento mila record.

Randomicamente, la cancellazione dei dati di appoggio di una singola elaborazione va in timeout.
Nello specifico, una semplice
DELETE FROM [tabella] WHERE IdElaborazione=[valore_id]
sfora i 30 secondi standard e SQL Server genera l’errore di timeout (IdElaborazione è indicizzato).

Cercando tra le varie soluzioni, abbiamo scelto l’idea di frammentare la cancellazione in bocconi più piccoli, evitando di strozzare SQL Server, lasciandogli nel contempo la gestione dell’intero processo di eliminazione.
In pratica, generiamo al volo uno script, per poi eseguirlo tramite SQLExecute:

BEGIN TRANSACTION;

WHILE EXISTS (SELECT TOP 1 * FROM |1 |2) 
BEGIN 
WITH LT AS 
( 
SELECT TOP 60000 * 
FROM |1 |2 
) 
DELETE FROM LT 
END;

COMMIT;

Dove |1 è il nome della tabella e |2 l’eventuale clausola where.

Nei test effettuati, dove prima una singola delete durava più di 30 secondi, ora l’eliminazione dei record di 10 tabelle (215mila record in totale su oltre 2milioni) impiega poco più di 60 secondi, senza dare timeout.

Avremmo potuto eseguire direttamente da InDe diverse delete, ma avremmo in pratica allungato il timeout complessivo per l’intera tabella e sollecitato il driver rischiando di ingolfarlo. Con la soluzione proposta invece il numero di istruzioni (e quindi timeout) eseguite da SQL Sever non è cambiato.

Le parti dolenti sono due:

  1. Se dovessimo cambiare motore DB?.
  2. Il nome della tabella è dato da una costante, totalmente slegato dallo schema db all’interno del progetto.

Voi che soluzioni avete adottato?

1 Mi Piace
2

Ciao Riccardo,
ci sono diverse problematiche da considerare in queste situazioni e le principali sono legate al sistema transazionale del DBMS.
Quando cancelli per una chiave esterna come nel tuo caso (id elab) andrai a mettere “in modifica” un numero elevato di righe sparse.
La prima cosa da valutare è il tipo di lock che effettua il DBMS: row, page, table.
A me capitavano dei deadlock difficili da analizzare perchè ero convinto stesse lockando a livello di riga invece faceva escalation (in particolari condizioni può accadere, SQLServer) a livello di pagina.
La soluzione adottata era un processo, lungo, di cancellazione in piccole delete batch con la loro transazione delle singole righe che avevano quella fk.
In una situazione dove hai più libertà di azione sul db una soluzione potrebbe essere quella di creare un sistema per cui ogni elaborazione ha delle tabelle temporanee dedicate e quando devi svuotare esegui una trunk o una drop. Questo approccio non l’ho mai sperimentato sul campo per cui non so se porta ad altri effetti collaterali, sicuramente devi crearti delle tabelle dove tenere le metainfo di queste tabelle temporanee, almeno sapere che per un certo id elab le tabelle di appoggio si chiamano in un certo modo.
Questo approccio può essere però interessante da valutare perchè su tabelle con molte insert/delete si crea frammentazione sia dello spazio fisico che degli indici con conseguente spreco di spazio e degrado degli accessi alla tabella stessa per cui devi prevedere delle manutenzioni programmate sulla tabella (tipo shrinking e reindex). Sarebbe meglio poi dedicare un tablespace ad hoc per queste tabelle per faciltare le operazioni di manutenzione e per non incidere sul tablespace delle tabelle core.

2 Mi Piace
3

Ciao a tutti, riprendo l’argomento.

Tramite SQL Server Profiler abbiamo analizzato:

  • tempi di esecuzione
  • esecuzione di scan
  • lock timeout
  • lock escalation

Abbiamo trovato quanto segue:

  • Una delle tabelle coinvolte nella cancellazione viene usata in foreign key da un’altra tabella, molto popolata. La foreign key non era indicizzata scatenando, per ogni record eliminato, una scan piuttosto lenta. Aggiungendo l’indice siamo passati da 400ms di esecuzione per record a 20ms.
  • Abbiamo eliminato, in InDe, i vincoli di propagazione su cancellazione, che facevano scattare per ogni record eliminato un trigger di eliminazione dei record figli. Al suo posto abbiamo cancellato in modo massivo anche i record figli. Siamo passati da 5 secondi di esecuzione a 2, per la prima tabella, e da 21 secondi a 13 per la seconda.
  • Abbiamo cambiato il numero di record da eliminare in blocco da 60.000 a 10.000. In questo caso i lock escalation non sono scomparsi. Siamo quindi passati a blocchi da 1.000, ottenendo la scomparsa dei lock escalation ma avendo un aumento del tempo di esecuzione di 18 e 67 secondi ciascuna (non accettabile).
  • Abbiamo riscritto lo script di eliminazione eliminando le due select:
DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(|3) FROM |1 |2;
    IF @@rowcount < |3 SET @done = 1;
END;

In quest’ultimo caso non ho notato miglioramenti, ma credo sia cmq scritto meglio così.

2 Mi Piace