Όλοι σχεδόν έχουμε έρθει αντιμέτωποι με το πως θα σβήσουμε ένα μεγάλο αριθμό εγγραφών από ένα πίνακα σε μια βάση χωρίς αυτό να επιβαρύνει το σύστημα τόσο σε πόρους όσο και σε locks.

Υπάρχουν πολλοί λόγοι που μπορούν να αναφερθούν γιατί χρειάζεται να πραγματοποιηθεί κάτι τέτοιο με συνηθέστερους:

• Την διαγραφή ιστορικών δεδομένων που έχουν γίνει archiving.
• Την διαγραφή εγγραφών από staging tables

Σε αυτές τις περιπτώσεις θα πρέπει να εξετασθούν και να ληφθούν σοβαρά υπόψη παράμετροι που απορρέουν από την εκτέλεση της διαδικασίας όπως:

• Ο αριθμός των εγγραφών που πρόκειται να διαγραφούν
• Η σειρά με την οποία θα διαγραφούν
• Οι πόροι που θα χρειαστούν (CPU, Memory)
• Η επιβάρυνση που θα υπάρξει στο transaction log.
• Το locking που θα πρέπει να χρησιμοποιηθεί αλλά και χρόνος που αυτό θα είναι ενεργό.

Εξαρχής θα πρέπει να επισημανθεί ότι δεν υπάρχει η τέλεια μέθοδος.

Όλες οι παρακάτω αναφερόμενες μέθοδοι έχουν τα πλεονεκτήματα και μειονεκτήματα τους και θα πρέπει ανάλογα με τις ανάγκες να επιλεχθεί η περισσότερο κατάλληλη.

Για τον έλεγχο των περιπτώσεων ας πάρουμε μια database που έχει ένα table στο οποίο υπάρχουν 1Μ rows. Για την απλότητα του σεναρίου επιλέγεται η διαγράφη όλων των εγγραφών.

TRUNCATE TABLE

Ο ιδανικότερος τρόπος είναι το TRUNCATE TABLE. Γρήγορος, κάνει minimal logging και locking όμως:

• Μπορεί να δουλέψει εφόσον δεν είναι related με άλλο table και πρέπει πρώτα βγάλω όλα τα referential integrity constraints από αυτόν εφόσον θέλω να κάνω κάτι τέτοιο.
• Σβήνει όλα τα δεδομένα από το πίνακα. Δεν έχω την δυνατότητα να σβήσω ένα συγκεκριμένο τμήμα πχ από ημερομηνία και πίσω εκτός αν είμαι σε SQL Server 2016 και έχω κάνει partitioning στον συγκεκριμένο table καθώς υποστηρίζεται σε αυτή την έκδοση το TRUNCATE WITH PARTITION.

SIMPLE DELETE

Ο "χειρότερος" σε σχέση με τους άλλους τρόπος είναι το DELETE που διαγράφει όλες τις εγγραφές. Αν οι εγγραφές είναι λίγες (<1000) θα πάνε όλα καλά με μικρό έως σχεδόν καθόλου locking impact και μικρότερο επίσης transaction log usage. Αν όμως είναι περισσότερες τότε:

• Μεγαλώνει ο χρόνος εκτέλεσης και φυσικά ο χρόνος του locking με αποτέλεσμα να έχω blocking sessions.
• Όσο μεγαλύτερος είναι ο αριθμός των εγγραφών που πρέπει να διαγραφούν τόσο μεγαλύτερο χώρο χρειάζομαι στο transaction log.

DELETE WITH TOP

Ο τρόπος που φαίνεται αλλά και είναι αρκετά λογικός είναι το DELETE WITH TOP.

Το μυστικό σε αυτό το τρόπο είναι βρεθεί η τιμή που πρέπει να έχει το TOP ώστε να μην δημιουργεί locking και αυτό θα πρέπει να το βρει κανείς με πειράματα.

Επίσης ένα ακόμα στοιχείο που θα πρέπει να προσεχθεί ιδιαίτερα είναι να γίνονται οι διαγραφές ταξινομημένες με βάση τον clustered index.

Με αυτό το τρόπο επιτυγχάνεται το αποτέλεσμα με σημαντικά πλεονεκτήματα.

• Την μικρότερη χρήση του transaction log καθώς διαγράφονται λίγες εγγραφές κάθε φορά και σε συνδυασμό με transaction log backup δεν θα έχω φαινόμενα όπως την διόγκωση του transaction log.
• Ελαχιστοποίηση του locking impact καθώς δίνεται "ανάσα" στα άλλα ταυτόχρονα sessions να εκτελεστούν.
• Μικρότερο χρόνο στο ROLLBACK για την περίπτωση που χρειαστεί να διακοπεί η εργασία της διαγραφής.
• Ο χρόνος συνήθως είναι εφάμιλλος ή και καλύτερος από το απλό DELETE αλλά αυτό εξαρτάται από τον αριθμό των concurrent sessions που υπάρχουν. Στις παρακάτω μετρήσεις δεν υπήρχε concurrency καθώς επιλέχθηκε να γίνει άμεση αντιπαραβολή και με το TRUNCATE TRABLE.

Τα αποτελέσματα των σεναρίων φαίνονται στα παρακάτω γραφήματα.

Delete with Top - code snippet

Υπάρχουν αρκετοί τρόποι για την υλοποίηση του σεναρίου DELETE WITH TOP προσωπικά χρησιμοποιώ τον παρακάτω για δύο λόγους:

• Εξασφαλίζω ότι θα γίνουν οι διαγραφές με βάση το πεδίο που είναι clustered index έχω μαζί και το TOP αλλά και τα τυχόν φίλτρα που θέλω να έχω μέσα σε ένα απλά view
• Το επιπλέον διάβασμα στο πίνακα ώστε να σταματήσει το loop το κάνω γιατί όπως έχουν πει εδώ και αρκετές εκδόσεις η @@ROWCOUNT δεν θα επιστρέφει δεδομένα σε επόμενες εκδόσεις του SQL Server στις διαδικασίες INSERT/UPDATE/DELETE.

create view todelete     
as    
select top(<N>) * from <T>
where <field> <operator> <value>    
order by <clustered key column>;    
    
    
while 1=1    
begin    
    begin tran
    delete from todelete;
    commit
    if ( select count(*) from T ) = 0 break
end    

//antonch