go backsqlschool blogs list

What is the best method to delete huge number of rows?

by Antonios Chatzipavlis

Όλοι σχεδόν έχουμε έρθει αντιμέτωποι με το πως θα σβήσουμε ένα μεγάλο αριθμό εγγραφών από ένα πίνακα σε μια βάση χωρίς αυτό να επιβαρύνει το σύστημα τόσο σε πόρους όσο και σε 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.

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

graph 1

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


Ημερομηνία: 22 August 2017 22:23
Αξιολόγηση: ( 1 )
Κατηγορίες:
Tags:
Share it:

Σχετικά Blog Post

Σχόλια - Comments

user-gravatar

Στις 04 Sep 2017 @ 6:02 PM o/η nonlinearly έγραψε:

Επίσης... μπορούμε να δούμε το ίδιο πρόβλημα από άλλη όψη. Θα μπορούσαμε να κάνουμε ένα αντίγραφο του πίνακα για να κρατήσουμε τις εγγραφές που θέλουμε αν είναι πολύ λιγότερες από αυτές που θέλουμε να διαγράψουμε και να κάνουμε DROP τον αρχικό πίνακα!!!

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS