SQL Server, Non-updating updates ...why avoid them clearly explained!

Hi Guys,

Today we will talk about Non-updating updates.

I recommend that you read because these are important aspects when it comes to performance.

I will not go into the details of what happens (maybe in the future) rather I have developed a very clear example.

What Non-updating updates are?

 

Well, Enjoy the reading!

 

 

Non-updating updates 

A non updating update is an update the not change any value. 

 

Suppose we perform an update on field A which contains the value 1 to put the value 1 ... this is it a non updating update.

UPDATE TABLE SET MYFIELD = 1 (when the value of the field myfield is already equal to 1) 

UPDATE TABLE SET MYFIELD = MYFIELD WHERE ...
       

 

But who is it that does such a thing?  ...unfortunately many!

Many always perform a single update on all fields in a table, even if these have not changed.

To make you understand the weight of this operation, I have developed a simple example that demonstrates the difference in performance.

 

The Example

 

For this example we will create two tables TEST_UPDATE and TEST_DETAIL_UPDATE

CREATE TABLE TEST_UPDATE (
ID INT IDENTITY(1,1) PRIMARY KEY,
IDRIG1 INT,
IDRIG2 INT)

CREATE TABLE TEST_DETAIL_UPDATE(
ID INT IDENTITY(1,1) PRIMARY KEY,
CODE VARCHAR(20))

ALTER TABLE TEST_UPDATE ADD FOREIGN KEY (IDRIG1) REFERENCES TEST_DETAIL_UPDATE(ID)
ALTER TABLE TEST_UPDATE ADD FOREIGN KEY (IDRIG2) REFERENCES TEST_DETAIL_UPDATE(ID)
       

Each table has a field ID primary key of type integer

The Test_Update table has two fields IDRIG1 and IDRIG2 with a foreign key where:

TEST_UPDATE.IDRIG1 -> TEST_DETAIL_UPDATE.ID

TEST_UPDATE.IDRIG2 -> TEST_DETAIL_UPDATE.ID 
       

 

After inserted some data into the tables...

INSERT INTO TEST_DETAIL_UPDATE(CODE) VALUES ('A'),('B')

INSERT INTO TEST_UPDATE(IDRIG1,IDRIG2) SELECT 1,1
       

...we are ready to test the performance of some updates.

Before each test we execute  the dbcc freeproccache.

 

For this first test (through a loop cycle) we execute 80 + 80 updates  

The value of the field IDRIG1 is changing constantly

DECLARE @COUNT INT;
SET @COUNT = 1;
WHILE @COUNT < 80
BEGIN
  UPDATE TEST_UPDATE SET IDRIG1  = 1 WHERE ID = 1;
  UPDATE TEST_UPDATE SET IDRIG1  = 2 WHERE ID = 1;
  SET @COUNT = @COUNT +1;
END;
       

The exection of is equal to 32bv seconds.

Now, let's see what happens if the value of field IDRIG1 never changes:

DECLARE @COUNT INT;
SET @COUNT = 1;
WHILE @COUNT < 80
BEGIN
  UPDATE TEST_UPDATE SET IDRIG1  = 1 WHERE ID = 1;
  UPDATE TEST_UPDATE SET IDRIG1  = 1 WHERE ID = 1;
  SET @COUNT = @COUNT +1;
END;
       

According to you ... how long is the execution time?

Again 31 seconds!

This shows that the update is still done even if the value I want to write is already the one wanted.
From the execution plan we also understand that regardless of the value we want to put, it is always verified that it satisfies the requirements imposed by the foreign key:

This means also that i need to go on the test_detail_update table.

 

Another test!

This time we will compare the update two fields versus the update of a single field.

Down here, we update a the same moment the fields IDRIG1 and IDRIG2. where the value of the field IDRIG2 never changes.

DECLARE @COUNT INT;
SET @COUNT = 1;
WHILE @COUNT < 80
BEGIN
  UPDATE TEST_UPDATE SET IDRIG1  = 1, IDRIG2 = 1 WHERE ID = 1;
  UPDATE TEST_UPDATE SET IDRIG1  = 2, IDRIG2 = 1 WHERE ID = 1;
  SET @COUNT = @COUNT +1;
END;
       

The execution time is 42 seconds.

Now we will drop the second field from the update and run the test again.

DECLARE @COUNT INT;
SET @COUNT = 1;
WHILE @COUNT < 80
BEGIN
  UPDATE TEST_UPDATE SET IDRIG1  = 1 WHERE ID = 1;
  UPDATE TEST_UPDATE SET IDRIG1  = 2 WHERE ID = 1;
  SET @COUNT = @COUNT +1;
END;
       

The esecution time is 32 seconds.

Wow! update at the same time a second field take 30% more time!

 

The 4 test cases: Case no.3 update two fields at the same moment

 

Conclusion

If you have a table with a field enforced with  referential integrity watch out!

• Even if the field if you want to write already contains the same value, the update takes place anyway
• Each additional field inside our update involves an increase in the execution time (even if I don't change any value)

That's why I don't like updates that update all fields in a table!

 

That's all for today mates!

Stay tuned for the next post and support my blog  

 

 

 

Previous post: ooops.. an April fool's