SQL – Reduce Write I/O on Updates

In a nutshell – update only where needed. If you want more in-depth, proceed to read.

I recently had a DBA send me an email on how one of the stored procedures I maintain is bad; In my defense, I didn’t write it, I just maintain it. Another quick side note, I don’t find this to be a “omfg you serious?!” moment when DBAs explain things to me; I think it’s a great learning opportunity.

Anyway, the query was bad because it updated multiple fields when it really didn’t need to. This leads to an unnecessary increase of write I/O on the server. You may be thinking “well duh, it’s so obvious” while reading or after the examples, but I figured why not blog about it. Let’s get to the examples:

	CREATE TABLE [dbo].[tblCustomers] (
	[custId] INT NOT NULL PRIMARY KEY IDENTITY,
	[custFirstName] VARCHAR(50) NULL,
	[custLastName] VARCHAR(50) NULL,
	[custEmail] VARCHAR(100) NULL,
	[custAddress] VARCHAR(200) NULL,
	[custIsLoyal] BIT NULL,
	[custDateAdded] DATETIME NULL,
	[custDateUpdated] DATETIME NULL );
	CREATE TABLE [dbo].[tblStageCustomers] (
	[stgId] INT NOT NULL PRIMARY KEY IDENTITY,
	[stgFirstName] VARCHAR(50) NULL,
	[stgLastName] VARCHAR(50) NULL,
	[stgEmail] VARCHAR(100) NULL,
	[stgAddress] VARCHAR(200) NULL,
	[stgDateAdded] DATETIME NULL );
	------------------------------
	-- EXAMPLE 1 - Simple update
	------------------------------
	-- What not to do:
	UPDATE dbo.tblCustomers
	SET custIsLoyal = 1;
	-- What to do:
	UPDATE dbo.tblCustomers
	SET custIsLoyal = 1
	WHERE custIsLoyal IS NULL;
	------------------------------
	-- EXAMPLE 2 - Using Merge to insert or update records
	------------------------------
	MERGE dbo.tblCustomers AS SOURCE
	USING dbo.tblStageCustomers AS TARGET
	ON ( TARGET.stgEmail = SOURCE.custEmail )
	WHEN NOT MATCHED BY TARGET
	THEN INSERT (custFirstName, custLastName, custEmail, custAddress, custDateAdded, custDateUpdated)
	VALUES (stgFirstName, stgLastName, stgEmail, stgAddress, stgDateAdded, GETDATE())
	------- INCORRECT WAY TO UPDATE SINCE THIS ALWAYS UPDATES EVERYTHING
	WHEN MATCHED
	THEN UPDATE
	SET custFirstName = stgFirstName,
	custLastName = stgLastName,
	custEmail = stgEmail,
	custAddress = stgAddress,
	custDateAdded = stgDateAdded,
	custDateUpdated = GETDATE()
	------- CORRECT WAY TO UPDATE SINCE ATLEAST ONE HAS TO BE DIFFERENT
	WHEN MATCHED AND NOT (custFirstName = stgFirstName AND
	custLastName = stgLastName AND
	custEmail = stgEmail AND
	custAddress = stgAddress AND
	custDateAdded = stgDateAdded)
	THEN UPDATE
	SET custFirstName = stgFirstName,
	custLastName = stgLastName,
	custEmail = stgEmail,
	custAddress = stgAddress,
	custDateAdded = stgDateAdded,
	custDateUpdated = GETDATE();

view raw updateIO.sql hosted with by GitHub

So we have two tables; tblCustomers that belong to the system and tblStageCustomers to hold customers coming into the system. The first example is just showing a simple way to help decrease the write I/O. The second example shows how we want to ensure we must have some change prior to updating. Hopefully it’s simple enough to understand.