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:
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
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
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)
Enjoy!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.