SQL Server, why does the DELETE statement tend to be slow?

Hi Guys,

Today i will post only a small and very informat and short post.

Just a question to make a little reflection:  

Why DELETE statement tend to be slow?

 

Enjoy the reading!

 

 

Why DELETE statement tend to be slow?

Surely you know that data is stored in databases within a series of tables. 

When a database is in the 3rd normal form, tables are linked to each other through referential integrity.

It is precisely referential integrity that guarantees data integrity.

 

Suppose we have to manage a list of person.

 

Our table tblperson will have a primary key (ID) and a number of fields such as name, email and gender.

 

The information relating to sex will be contained in another table (tblGender). it will also have a primary key ID field

Beetween the field GenderID of the table tblPerson and the field ID of the table tblGender we define a referential integrity. 

In T-SQL language we can do this by adding a foreign key to the TblPerson:

       
ALTER TABLE tblPerson ADD FOREIGN KEY (GenderID)
REFERENCES TblGender(ID); 

What does this mean?

It means that the value contained within the TblPerson.GenderId field can only be one of the values ​​contained in the TblGender.ID column.

As the only exception you can put the value NULL if the column GenderID is nullable.

The NULL value means that there is still no value in the column.

So, can you put the value 4 in the GenderID field? NO!

 

After this brief and really informal explanation let's see with the help of an example what SQL Server does with integrity in the case of UPDATE and DELETE  statements

Let's create the two tables for the example:

       
CREATE TABLE TblGender (
ID INT IDENTITY(1,1),
Gender VARCHAR(20),
PRIMARY KEY(ID)
); 

CREATE TABLE tblPerson (
ID INT IDENTITY(1,1),
Name VARCHAR(20),
Email VARCHAR(20),
GenderID int NULL
PRIMARY KEY(ID)
FOREIGN KEY (GenderID) REFERENCES TblGender(ID)
); 

Let's insert some data:

       
INSERT INTO TblGender(Gender) VALUES ('Male'),('Female'),('Unknown')

INSERT INTO TblPerson(Name,Email,GenderID) 
  VALUES ('Jade','J@j.com',2),('Mary','m@m.com',3),('Martin','ma@ma.com',1)

Now perform an UPDATE 

"Miss. Mary changes the sex value from unknown to female"

This is done through this update:

       
UPDATE TblPerson SET GenderID = 2 WHERE ID = 2

What happens?

Looking at the execution plan we can see that SQL Server uses referential integrity.

This is because it has to understand if the value we want to put inside the column respects the referential integrity or not.

Let us therefore remember that when we make an UPDATE, for each field that we want to update that points to another table (foreign key) its referential integrity will be checked.

From a performance point of view, the more fields that point to another table we update the more time our update takes.

Delete

 

Let's now try what happens with a DELETE statement by entering this command statement

       
DELETE FROM TblGender WHERE ID = 2

if you run it, the message below will come out

       
Msg 547, Level 16, State 0, Line 32
The DELETE statement conflicted with the REFERENCE constraint "FK__tblPerson__Gende__5EBF139D". The conflict occurred in database "Tests", table "dbo.tblPerson", column 'GenderID'.
The statement has been terminated.

The referential integrity will protect our data: You can't delete the row of the table TmlGender with ID equal to 2 because there is at least a row in the table TblPerson where IdGender is equal to 2.

You can see this clearly from the execution plan:

SQL Server will scan the entire Tblperson table searching for a row with GenderID = 2.

From this we understand that to avoid scanning the whole table it is always wise to add a non-clustered index on each column with foreign key.

 

Finally we turn to the main question: why DELETE statement tend to be slow?

In a relational database, tables are linked together by many relationships..

Just think, for example, of a common table present in many ERPs such as that of VAT.

 

How many tables have a field pointing to this table?
Counterparties, customers, suppliers, products, price lists, documents of any kind? 

Really many!

So to delete a row of this table it will be necessary to check all the tables in which fields point "at" it.

And if there are many fields to check, the update takes time.
Indeed, most of the time it takes is this Verification.

That's all for today!

 

 

 

A big thanks! 

 

Today I wanted to say a big thank you!

Thanks for all the compliments and also for the constructive criticisms.

In the last two months alone, the blog has been clicked by almost 15,000 people!

For me is really amazing!
 

You may have noticed that the advertisement has appeared. 

Very honestly I had the opportunity to add it and I did it also by virtue of the time dedicated to the blog.
However, I will try to favor announcements dedicated to this sector or to offers.

I myself see that sometimes there are also some really interesting offers!

 

 

 

Stay tuned mates!

Luca

 

 

 

 

Help me to share knowledge on my blog  

Next post:

Previous post: Cursors and a trick before Christmas