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 Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

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-2025 All rights reserved

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