Lesson Learned #138: Transferring data between two tables is taking too much resources

Today, I worked on a service request that our customer has an internal process that every night they transferred data from table (iot_table1) to another table (iot_table2) based on several filters. Despite that the number of rows of the source table is increasing every day, this process is taking all the transaction log resource due to amount of data that this process is transferring. 

In this type of situation, our best recomendation is to use Business Critical o Premium because the IO capacity is greater if you are using General Purpose or Standard. But, our customer, wants to find an alternative to stay in the Standard/General Purpose without moving to Premium/Business Critical in order to reduce the cost. 

Let's assume that our customer has two tables: IOT_Table1 (source) and IOT_Table2 (destination).

CREATE TABLE [dbo].[iot_table1](
	[id] [int] NOT NULL,
	[text] [nchar](10) NULL,
	[Date] [datetime] NULL,
 CONSTRAINT [PK_iot_table1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[iot_table2](
	[id] [int] NOT NULL,
	[text] [nchar](10) NULL,
 CONSTRAINT [PK_iot_table2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

We suggested different workarounds to prevent the execution of this process, for example, using an incremental process using Azure Data Factory or the following alternatives using SQL Server Engine:

• Alternative 1) Create a trigger that for every row that you are inserting in the table iot_table1 will be transferred to iot_table2. I’m sharing with you an example about it:

CREATE TRIGGER [dbo].[inserted]
   ON [dbo].[iot_table1]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO IOT_table2(ID,[TEXT]) SELECT ID,[TEXT] from inserted
END

• Alternative 2) Reduce the amount of numbers of rows to be transferred, for example, creating a table per day. 

CREATE TRIGGER [dbo].[inserted]
   ON [dbo].[iot_table1]
   AFTER INSERT
AS 
BEGIN
       DECLARE @DAY AS VARCHAR(2)
       DECLARE @MONTH AS VARCHAR(2)
       DECLARE @YEAR AS VARCHAR(4)
       SET NOCOUNT ON;
       SET @DAY = CONVERT(VARCHAR(2),DAY(GETDATE()))
       SET @MONTH = CONVERT(VARCHAR(2),MONTH(GETDATE()))
       SET @YEAR = CONVERT(VARCHAR(4),YEAR(GETDATE()))
       IF @MONTH=9 AND @DAY =25 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_25(ID,[TEXT]) SELECT ID,[TEXT] from inserted
    IF @MONTH=9 AND @DAY =26 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_26(ID,[TEXT]) SELECT ID,[TEXT] from inserted
    IF @MONTH=9 AND @DAY =27 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_27(ID,[TEXT]) SELECT ID,[TEXT] from inserted         
    IF @MONTH=9 AND @DAY =28 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_28(ID,[TEXT]) SELECT ID,[TEXT] from inserted         
    IF @MONTH=9 AND @DAY =29 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_29(ID,[TEXT]) SELECT ID,[TEXT] from inserted         
END

• Alternative 3) Using Indexed views but it is depending on the definition of this table, because we have some limitations, for example: - https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15

• You could create an indexed view that the definition, as example, could be:

CREATE OR ALTER VIEW Data_Per_Day_29_09_2020
WITH SCHEMABINDING
AS
SELECT ID, [TEXT] from dbo.iot_table1 where DAY([DATE])=29 and MONTH([DATE])=9 AND YEAR([DATE])=2020

CREATE UNIQUE CLUSTERED Index Data_Per_Day_29_09_2020_X1 ON Data_Per_Day_29_09_2020(ID)

• When you have an indexed view the data will be automatically saved as materialized data, so, in every row that you added in the table depending on the value of the field DATE you are going to have a materialized view with data. If you run the query using this view SELECT * FROM Data_Per_Day_29_09_2020 WITH (NOEXPAND) the data that you are going to have is the materialized data and will be not retrieved from the table. If you use SELECT * FROM Data_Per_Day_29_09_2020 the data will be retrieved from the table.

Enjoy!!