sqlschool.gr logo

articles

Articles of SQLschool.gr Team

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

Antonios Chatzipavlis
Tuesday 22 August 2017

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


Comments

04 Sep 2017 @ 6:02 PM

user-gravatar

nonlinearly

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

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.

Episode

Transparent Data Encryption (TDE)

image

More Episodes...

Tip

Refresh Intellisence in SSMS

Για να κάνουμε refresh το intellisence μέσα στο SSMS αρκεί να πατήσουμε Ctrl+Shift+R

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn


sqlschool.gr © 2010-2023 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.