February 1, 2023 at 11:49 am
Hi there
I have a script which processes and aggregates data.
Now this is procesing data from a source table called #LoadReadingDetails_SSC
Currently I am using a Loop to process each batch of records by filtering on the columns [SerialNumber] and [ChannelID]
On running the script its taking 38 seconds to processs 320,000 records
Now i understand that using a loop based approach is bad and Set based approach should be used especially for performance reasons.
But the problem here is that I am taking the data and then peforming a number of steps on each set of data including a merge operation, 2 NTILE statements and so I am struggling to turn this into a set based approach
Im providing the DDL for this ...apologies its quite long ...but the main driver to each loop is the following :
INSERT INTO [#ChannelReading] ([ReadingDateTime], [RawReading], [SIReading])
SELECT
[Timestamp],
IsNull([Raw],0),
[Si]
FROM
#LoadReadingDetails_SSC
WHERE
SerialNumber = @SerialNumber
AND
ChannelID = @ChannelID
So say if I have 1000 records for SerialNumber - 894339, channel ID = 11 and 1000 records for SerialNumber - 894339, channel ID = 14, i have to batch process each of those groups individually.
Is it possible to process this using a set based approach? I have turned to using Cursors but this does not improve performance
USE [Logger_Groups_CI]
GO
/****** Object: Table [#LoadReadingDetails_SSC] Script Date: 01/02/2023 11:10:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************************************************************************************************************************************/
/*CREATION AND POPULATION OF TABLE*/
/*******************************************************************************************************************************************************************************************/
DROP TABLE IF EXISTS [#LoadReadingDownloadDeviceDetails]
DROP TABLE IF EXISTS [#LoadReadingDetails_SSC]
DROP TABLE IF EXISTS [#ChannelReading]
DROP TABLE IF EXISTS [#ChannelReadingDelta]
DROP TABLE IF EXISTS [#ChannelReading_NTILE_Results]
DROP TABLE IF EXISTS [#ChannelReading_NTILE_Remainder]
DROP TABLE IF EXISTS [#LoadReadingDownloadDeviceDetails] -- 1.07 (07/10/2022)
CREATE TABLE [#LoadReadingDetails_SSC](
[SerialNumber] [nvarchar](60) NULL,
[ChannelID] [int] NULL,
[Si] [decimal](10, 2) NULL,
[Raw] [decimal](10, 2) NULL,
[Conversion] [int] NULL,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.18 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T10:04:07.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.14 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T10:06:07.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.18 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T10:08:07.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.14 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T10:10:07.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.07 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T10:12:07.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.22 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T13:04:43.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.35 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T13:06:43.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.49 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T13:08:43.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.44 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-13T13:10:43.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 11, CAST(24.95 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-06-14T06:44:01.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.03 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:20:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(53.97 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:22:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.01 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:24:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.06 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:26:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.02 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:28:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.04 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:30:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.11 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:32:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.12 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:34:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.12 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:36:57.000' AS DateTime))
GO
INSERT [#LoadReadingDetails_SSC] ([SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (N'894339', 14, CAST(54.10 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-04-22T14:38:57.000' AS DateTime))
GO
CREATE TABLE [#LoadReadingDownloadDeviceDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ReadingDownloadID] [uniqueidentifier] NULL,
[DeviceID] [uniqueidentifier] NULL,
[DeviceChannelID] [uniqueidentifier] NULL,
[SerialNumber] [nvarchar](60) NULL,
[ChannelID] [int] NULL,
[ReadingDownloadTable] [nvarchar](200) NULL,
[MainLevelDataTable] [nvarchar](200) NULL,
[MainLevelDataTableRecordCountsTable] [nvarchar](200) NULL,
[ReadingValueHashSource] [nvarchar](500) NULL,
[ReadingValueHashTarget] [nvarchar](500) NULL,
[NewDownloadTable] [nvarchar](5) NULL,
[SignatureDifferent] [nvarchar](1) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [#LoadReadingDownloadDeviceDetails] ON
GO
INSERT [#LoadReadingDownloadDeviceDetails] ([ID], [ReadingDownloadID], [DeviceID], [DeviceChannelID], [SerialNumber], [ChannelID], [ReadingDownloadTable], [MainLevelDataTable], [MainLevelDataTableRecordCountsTable], [ReadingValueHashSource], [ReadingValueHashTarget], [NewDownloadTable], [SignatureDifferent]) VALUES (1, NULL, N'637a9a2b-9534-4b6a-b049-d50d188d1626', N'2d5ee5fa-b4af-4fd7-a2ee-cdc48ae61d53', N'894339', 11, N'ChannelReading_894339_11_2D5EE5FA-B4AF-4FD7-A2EE-CDC48AE61D53', N'ChannelReading_894339_11_2D5EE5FA-B4AF-4FD7-A2EE-CDC48AE61D53_MainLevelData', N'ChannelReading_894339_11_2D5EE5FA-B4AF-4FD7-A2EE-CDC48AE61D53_MainLevelData_TableRecordCounts', N'jr61f8n7n0CaUZeQfUvaFA==', NULL, N'false', N'Y')
GO
INSERT [#LoadReadingDownloadDeviceDetails] ([ID], [ReadingDownloadID], [DeviceID], [DeviceChannelID], [SerialNumber], [ChannelID], [ReadingDownloadTable], [MainLevelDataTable], [MainLevelDataTableRecordCountsTable], [ReadingValueHashSource], [ReadingValueHashTarget], [NewDownloadTable], [SignatureDifferent]) VALUES (2, NULL, N'637a9a2b-9534-4b6a-b049-d50d188d1626', N'b086efa9-8c71-4bb4-bb22-c03b089c6297', N'894339', 12, N'ChannelReading_894339_12_B086EFA9-8C71-4BB4-BB22-C03B089C6297', N'ChannelReading_894339_12_B086EFA9-8C71-4BB4-BB22-C03B089C6297_MainLevelData', N'ChannelReading_894339_12_B086EFA9-8C71-4BB4-BB22-C03B089C6297_MainLevelData_TableRecordCounts', N'jr61f8n7n0CaUZeQfUvaFA==', NULL, N'false', N'Y')
GO
INSERT [#LoadReadingDownloadDeviceDetails] ([ID], [ReadingDownloadID], [DeviceID], [DeviceChannelID], [SerialNumber], [ChannelID], [ReadingDownloadTable], [MainLevelDataTable], [MainLevelDataTableRecordCountsTable], [ReadingValueHashSource], [ReadingValueHashTarget], [NewDownloadTable], [SignatureDifferent]) VALUES (3, NULL, N'637a9a2b-9534-4b6a-b049-d50d188d1626', N'd9d39800-009c-433a-99dd-3ef0e7e83ed9', N'894339', 13, N'ChannelReading_894339_13_D9D39800-009C-433A-99DD-3EF0E7E83ED9', N'ChannelReading_894339_13_D9D39800-009C-433A-99DD-3EF0E7E83ED9_MainLevelData', N'ChannelReading_894339_13_D9D39800-009C-433A-99DD-3EF0E7E83ED9_MainLevelData_TableRecordCounts', N'jr61f8n7n0CaUZeQfUvaFA==', NULL, N'false', N'Y')
GO
INSERT [#LoadReadingDownloadDeviceDetails] ([ID], [ReadingDownloadID], [DeviceID], [DeviceChannelID], [SerialNumber], [ChannelID], [ReadingDownloadTable], [MainLevelDataTable], [MainLevelDataTableRecordCountsTable], [ReadingValueHashSource], [ReadingValueHashTarget], [NewDownloadTable], [SignatureDifferent]) VALUES (4, NULL, N'637a9a2b-9534-4b6a-b049-d50d188d1626', N'504dad55-afca-4138-b052-de3d2d8ce791', N'894339', 14, N'ChannelReading_894339_14_504DAD55-AFCA-4138-B052-DE3D2D8CE791', N'ChannelReading_894339_14_504DAD55-AFCA-4138-B052-DE3D2D8CE791_MainLevelData', N'ChannelReading_894339_14_504DAD55-AFCA-4138-B052-DE3D2D8CE791_MainLevelData_TableRecordCounts', N'jr61f8n7n0CaUZeQfUvaFA==', NULL, N'false', N'Y')
GO
INSERT [#LoadReadingDownloadDeviceDetails] ([ID], [ReadingDownloadID], [DeviceID], [DeviceChannelID], [SerialNumber], [ChannelID], [ReadingDownloadTable], [MainLevelDataTable], [MainLevelDataTableRecordCountsTable], [ReadingValueHashSource], [ReadingValueHashTarget], [NewDownloadTable], [SignatureDifferent]) VALUES (5, NULL, N'637a9a2b-9534-4b6a-b049-d50d188d1626', N'ece04ca1-5365-4d02-b114-09234afc6c4e', N'894339', 16, N'ChannelReading_894339_16_ECE04CA1-5365-4D02-B114-09234AFC6C4E', N'ChannelReading_894339_16_ECE04CA1-5365-4D02-B114-09234AFC6C4E_MainLevelData', N'ChannelReading_894339_16_ECE04CA1-5365-4D02-B114-09234AFC6C4E_MainLevelData_TableRecordCounts', N'jr61f8n7n0CaUZeQfUvaFA==', NULL, N'false', N'Y')
GO
INSERT [#LoadReadingDownloadDeviceDetails] ([ID], [ReadingDownloadID], [DeviceID], [DeviceChannelID], [SerialNumber], [ChannelID], [ReadingDownloadTable], [MainLevelDataTable], [MainLevelDataTableRecordCountsTable], [ReadingValueHashSource], [ReadingValueHashTarget], [NewDownloadTable], [SignatureDifferent]) VALUES (6, NULL, N'637a9a2b-9534-4b6a-b049-d50d188d1626', N'32fd27b0-b0ee-4702-bb72-771586781bc4', N'894339', 17, N'ChannelReading_894339_17_32FD27B0-B0EE-4702-BB72-771586781BC4', N'ChannelReading_894339_17_32FD27B0-B0EE-4702-BB72-771586781BC4_MainLevelData', N'ChannelReading_894339_17_32FD27B0-B0EE-4702-BB72-771586781BC4_MainLevelData_TableRecordCounts', N'jr61f8n7n0CaUZeQfUvaFA==', NULL, N'false', N'Y')
GO
SET IDENTITY_INSERT [#LoadReadingDownloadDeviceDetails] OFF
GO
/**********************************************************************************************************************************************************************************************/
/*CREATION AND POPULATION OF TABLE - END*/
/*******************************************************************************************************************************************************************************************/
-- 1.07 (07/10/2022)
DECLARE
@ErrorLine INT,
@ErrorState INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorProcedure NVARCHAR(200),
@RC INT,
@ReturnCodeAction NVARCHAR(20),
@InputMessage NVARCHAR(4000),
@TableName NVARCHAR(100),
@SQL NVARCHAR(4000),
@ArchiveInputFileName NVARCHAR(1000),
@ImportDelimiter VARCHAR(10),
@FileNameTimestamp NVARCHAR(100),
@sqlTable NVARCHAR(170),
@LatestReadingDate DATETIME2,
@RowsAffected INT = 0,
@TableCreatedNewFlag INT,
@TableNameInputMask NVARCHAR(100),
@RecordCount INT,
@LastReadingTime DATETIME,
@Procname NVARCHAR(200),
@CreatedByUser UNIQUEIDENTIFIER,
@msg NVARCHAR(3000),
@Arguments NVARCHAR(MAX),
@ChannelReadingDownload UNIQUEIDENTIFIER,
@FromDate DATETIME2,
@ToDate DATETIME2,
@MeasuringPointEntryID UNIQUEIDENTIFIER,
@CreationDate DATETIME,
@DeviceChannelID UNIQUEIDENTIFIER = NULL,
@DeviceID UNIQUEIDENTIFIER = NULL, -- 1.08 (17/10/2022)
@ReadingDownloadID UNIQUEIDENTIFIER = NULL,
@Num_Current INT, -- 1.01 (08/03/2022)
@Num_Added INT, -- 1.01 (08/03/2022)
@Num_Total INT
DECLARE @ErrorMessage_OUT NVARCHAR(4000)
-- spSetChartAggregatedData Variables
DECLARE @StartTime DATETIME2
DECLARE @CurrentEventTime DATETIME2
DECLARE @TimeInterval INT
DECLARE
@NumRecords INT ,
@NumInBatch DECIMAL(15, 6) = 512.00000,
@NumGroups INT = 1,
@count INT = 0,
@A INT = 0,
@B INT = 0,
@NumberOfGroups INT,
@GroupLevel INT,
@NTILENumberToProcess INT,
@SingleRecordsToAggregate INT
DECLARE @TimeDifferenceLevel INT
DECLARE @RecordsInEachGroup INT
DECLARE @MaxID INT
DECLARE @VvcError VARCHAR(150)
DECLARE @VinCounter INT
DECLARE @VinMaxCounter INT
DECLARE @VinCounter2 INT
DECLARE @VinMaxCounter2 INT
DECLARE @MainLevelDataTableName NVARCHAR(200)
DECLARE @TableRecordCountsTableName NVARCHAR(200)
DECLARE @ChannelReadingTableName NVARCHAR(200)
DECLARE
@DownloadTableName NVARCHAR(100),
@PixelCount INT,
@RecordCountTotal INT,
@dynamicSQL NVARCHAR(MAX),
@BinSize INT,
@TimeElapsedSecs DECIMAL(15, 6),
@RecordsToProcess INT,
@NTileValue INT,
@SerialNumber NVARCHAR(60), -- 1.07 (07/10/2022)
@ChannelID INT, -- 1.07 (07/10/2022)
-- @ChannelReadingsTable ChannelReadingType, -- 1.07 (07/10/2022)
@SignatureDifferent NVARCHAR(1), -- 1.08 (17/10/2022)
@NewDownloadTable NVARCHAR(5), -- 1.08 (17/10/2022)
@DataHashDtoSource NVARCHAR(500),
@RunIDUNIQUEIDENTIFIER
--1.12 (05/12/2022)
DECLARE
@TINT,
@lINT = 1,
@NTileINT = 0,
@sINT = 0, -- Single Records to Process
@nINT = 0, -- Count of incoming records (from JSON)
@rINT = 0,
@RecordstoExtractINT = 0,
@g INT = 0
DECLARE@IndexNameNVARCHAR(50) -- 1.13 (15/12/2022)
SET DATEFORMAT YMD;
/***********************
* Initialise Variables *
***********************/
SET @ErrorLine= 0
SET @ErrorState= 1
SET @ErrorNumber= 50000
SET @ErrorMessage= N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '
SET @ErrorSeverity= 16
SET @ErrorProcedure= ISNULL(OBJECT_NAME(@@PROCID), '-')
SET @ProcName= @ErrorProcedure
SET @RC= 0
SET @TableCreatedNewFlag= 0
SET @CreatedByUser= [dbo].[fnReturnUserID](SYSTEM_USER)
SET @CreationDate= GETUTCDATE()
SET @Num_Current= 0
SET @ReadingDownloadID = NEWID()
-- Create Temp tables
CREATE TABLE [#ChannelReading]
(
[ReadingDateTime] [datetime2](7) NULL,
[RawReading] [int] NULL,
[SIReading] [real] NULL
)
CREATE CLUSTERED INDEX [ClusteredIndex-20230131-224051] ON [#ChannelReading] ([ReadingDateTime])
-- Create Temp tables
CREATE TABLE [#ChannelReadingDelta]
(
[ReadingDateTime] [datetime2](7) NULL,
[RawReading] [int] NULL,
[SIReading] [real] NULL
)
CREATE TABLE [#ChannelReading_NTILE_Results]
(
[MinValue] [float] NULL,
[MaxValue] [float] NULL,
[AvgValue] [float] NULL,
[DateTime] [datetime2](7) NULL,
[Level] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [#ChannelReading_NTILE_Remainder]
(
[MinValue] [float] NULL,
[MaxValue] [float] NULL,
[AvgValue] [float] NULL,
[DateTime] [datetime2](7) NULL,
[Group] [bigint] NULL
) ON [PRIMARY]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- BEGIN TRY
DECLARE Test CURSOR LOCAL FAST_FORWARD FOR
SELECT
SerialNumber, DeviceID, ChannelID,ReadingDownloadID,SignatureDifferent,NewDownloadTable,
DeviceChannelID,QUOTENAME(ReadingDownloadTable,'[]'),QUOTENAME(ReadingDownloadTable, '[]'),
MainLevelDataTable,MainLevelDataTableRecordCountsTable,ReadingValueHashSource
FROM #LoadReadingDownloadDeviceDetails
ORDER BY ChannelID
OPEN Test
FETCH NEXT FROM Test INTO @SerialNumber,
@DeviceID,
@ChannelID,
@ReadingDownloadID,
@SignatureDifferent,
@NewDownloadTable,
@DeviceChannelID,
@Tablename,
@ChannelReadingTableName,
@MainLevelDataTableName,
@TableRecordCountsTableName,
@DataHashDtoSource
WHILE @@FETCH_STATUS = 0
BEGIN
-- Create Base, Main Level Data and Table Record Counts tables of they dont exist
IF (@SignatureDifferent = 'Y')
BEGIN
INSERT INTO [#ChannelReading] ([ReadingDateTime], [RawReading], [SIReading])
SELECT
[Timestamp],
IsNull([Raw],0),
[Si]
FROM
#LoadReadingDetails_SSC
WHERE
SerialNumber = @SerialNumber
AND
ChannelID = @ChannelID
SELECT @n = COUNT(1) FROM [#ChannelReading]
-- Extract FromDate from the first date in readings table variablr--------------------------------------------------------
SELECT @FromDate = MIN(ReadingDateTime) FROM #ChannelReading
-- Extract ToDate from the last date in readings tablevariable ------------------------------------------------------------------
SELECT @ToDate = MAX(ReadingDateTime) FROM #ChannelReading
-- CHECK TO SEE IF THIS PHYSICAL DOWNLOAD TABLE EXISTS. IF IT DOES THEN LOCATE TABLE, IF NOT THEN CREATE TABLE
IF OBJECT_ID(@ChannelReadingTableName) IS NULL
BEGIN
-- table is new then no records in @Num_Current
SET @Num_Current = 0
SET @sqlTable = 'CREATE TABLE ' + @ChannelReadingTableName +
'([ReadingDateTime] DateTime2, [SIReading] real, [RawReading] int)'
EXEC sp_executesql @sqlTable
SET @TableCreatedNewFlag = 1
END
-- Extract current number of readings in table
SET @sql = 'SELECT @Num_Current = count(1) FROM ' + @ChannelReadingTableName
EXEC sp_executesql @sql, N'@Num_Current int out', @Num_Current OUT
-- Extract Last reading time from reading table using dynamic SQL
SET @sql = 'SELECT @LastReadingTime = MAX(ReadingDateTime) FROM ' + @ChannelReadingTableName
EXEC sp_executesql @sql, N'@LastReadingTime DateTime out', @LastReadingTime out
-- check count in base tablle
-- SET @timeStarted = CAST(SYSDATETIME() AS NVARCHAR(200))
-- Merge channelReadings table variable contents with the channelReadings table
SET @sql = 'MERGE ' + @ChannelReadingTableName + ' T '+ CHAR(13) + CHAR(10) +
' USING [#ChannelReading] S on T.ReadingDateTime = S.ReadingDateTime ' + CHAR(13) + CHAR(10) +
-- ' IsNull(ROUND(S.SIReading, 10),0)= IsNull(ROUND(T.SIReading, 10),0) ' + CHAR(13) + CHAR(10) +
' WHEN NOT MATCHED BY TARGET '+ CHAR(13) + CHAR(10) +
' THEN '+ CHAR(13) + CHAR(10) +
' INSERT (ReadingDateTime,SIReading, RawReading)'+ CHAR(13) + CHAR(10) +--1.10 (09/11/22)
' VALUES (S.ReadingDateTime,S.SIReading, S.RawReading) '+ CHAR(13) + CHAR(10) +--1.10 (09/11/22)
' ' + CHAR(13) +
' OUTPUT ' + CHAR(13) +
' Inserted.ReadingDateTime, ' + CHAR(13) +
' Inserted.SIReading, ' + CHAR(13) +
' Inserted.[RawReading] ' + CHAR(13) +
' ' + CHAR(13) +
' INTO #ChannelReadingDelta ([ReadingDateTime], [SIReading], [RawReading]); '
EXEC sp_executesql @sql WITH RECOMPILE
select @n = count(1) from #ChannelReadingDelta
SET @RowsAffected = @@ROWCOUNT
SET @Num_Added = @RowsAffected --1.09 (04/11/2022)
TRUNCATE TABLE [#ChannelReading]
IF @Num_Current <= 0
BEGIN
SET @Num_Current = 1
END
-- Extract Total Record of all time from the base table (@T)
SET @sql = 'SELECT @NumRecords = count(1) FROM ' + @ChannelReadingTableName
EXEC sp_executesql @sql, N'@NumRecords int out', @NumRecords out
WHILE 1 = 1 -- PROCESSING RECORDS LOOP - Start
BEGIN
SET @B = case when @Count = 0 then @NumRecords else Ceiling(@NumRecords / (@count)) end
--SET @timeStarted = CAST(SYSDATETIME() AS NVARCHAR(200))
--Check to see if Level exists in TableRecordcounts; If so then extract single Records to aggregate
SET @sql = 'if exists (SELECT * FROM ' + Quotename(@TableRecordCountsTableName,'[]') + ' WHERE [Level] = ' + Cast(@l as nvarchar(20)) + ' ) ' + CHAR(13) + CHAR(10) +
' begin ' + CHAR(13) + CHAR(10) +
' Select @s = [SingleRecordsToAggregate] from ' + Quotename(@TableRecordCountsTableName,'[]') + ' WHERE [Level] = ' + Cast(@l as nvarchar(20))+ CHAR(13) + CHAR(10) +
' end ' + CHAR(13) + CHAR(10) +
' else' + CHAR(13) + CHAR(10) +
' begin ' + CHAR(13) + CHAR(10) +
' Insert into ' + Quotename(@TableRecordCountsTableName,'[]')+ CHAR(13) + CHAR(10) +
' ([Level], [SingleRecordsToAggregate]) ' + CHAR(13) + CHAR(10) +
' Values ' + CHAR(13) + CHAR(10) +
' ( ' + Cast(@l as nvarchar(20)) + ',0)' + CHAR(13) + CHAR(10) +
' end '
--print @sql
EXEC sp_executesql @sql, N'@s int out', @s OUT
-- check that @s > 0
If (@s > 0 )
BEGIN
-- SET @timeStarted = CAST(SYSDATETIME() AS NVARCHAR(200))
-- NOW DELETE THE LAST AGGREGATED RECORD IN EACH TABLE
SET @SQL =
' WITH MyCTE AS ('+ CHAR(10) + CHAR(13) +
'SELECT TOP (1) * '+ CHAR(10) + CHAR(13) +
'FROM [dbo].' + Quotename(@MainLevelDataTableName, '[]')+ CHAR(10) + CHAR(13) +
'WHERE [lEVEL] = ' + CAST(@l AS nvarchar(4))+ CHAR(10) + CHAR(13) +
'ORDER BY [DateTime] DESC'+ CHAR(10) + CHAR(13) +
')'
+ CHAR(10) + char(13) +
'delete FROM MyCTE'
--print @sql
EXEC sp_executesql @sql
END
SET @t = @s + @n
SET @s = @t % POWER (2,@l)-- s = t % 2^l
SET @r = @t - @s
SET @NTile = @r / (POWER (2,@l))
-- SET @timeStarted = CAST(SYSDATETIME() AS NVARCHAR(200))
SET @SQL =
' Update ' + Quotename(@TableRecordCountsTableName, '[]')+ CHAR(13) + CHAR(10) +
' Set [SingleRecordsToAggregate] '+ ' = ' + Cast(@s as nvarchar(20))+ CHAR(13) + CHAR(10) +
' where [Level] = ' + Cast(@l as nvarchar(20))+ CHAR(13) + CHAR(10)
--print @SQL
EXEC sp_executesql @sql
/*******************************************************************************************************************************************************/
/*Calculate Table Record Counts - END*/
/********************************************************************************************************************************************************/
/********************************************************************************************************************************************************/
/*Aggregation - START*/
/*******************************************************************************************************************************************************/
-- (1)Order Total Records of All Time from Base Table (T) by latest
--Select @r + @s from the latest
-- Print '*****************************PROCESS MAIN RECORDS***************************************************************************************'
SET @RecordstoExtract = @r + @s
SET @SQL = 'INSERT INTO #ChannelReading([ReadingDateTime],[SIReading],[RawReading])' + CHAR(13) +
'SELECT TOP ' + CAST(@RecordstoExtract AS nvarchar(20))+ CHAR(13) +
'ReadingDateTime, SIReading,[RawReading] '+ CHAR(13) +
'FROM ' + @ChannelReadingTableName+ CHAR(13) +
'ORDER BY ReadingDateTime DESC'+ CHAR(13)
-- print @sql
EXEC sp_executesql @sql
-- (2) Group using NTile N from the start of selection up to -s
If @Ntile > 0
BEGIN
SET @SQL = ';with cte_ntile2 as ' + CHAR(13) +
'('+ CHAR(13) +
'SELECT ReadingDateTime, SIreading, rawreading '+ CHAR(13) +
'FROM ( '+ CHAR(13) +
'SELECT ReadingDateTime, SIreading, rawreading, ROW_NUMBER() OVER (ORDER BY ReadingDateTime desc) AS rn '+ CHAR(13) +
'FROM #ChannelReading'+ CHAR(13) +
') q '+ CHAR(13) +
'WHERE rn > ' + CAST(@s as NVARCHAR(10))+ CHAR(13) +
' ),'+ CHAR(13) +
' cte_ntile3 AS '+ CHAR(13) +
'('+ CHAR(13) +
'SELECT'+ CHAR(13) +
'ReadingDateTime, SIreading, rawreading, ' + CAST(@l as NVARCHAR(10)) + ' as Level ,'+ CHAR(13) ++ CHAR(13) +
'NTILE(' + CAST(@Ntile as NVARCHAR(10)) + ') OVER(ORDER BY [ReadingDateTime] desc) as [Group3]'+ CHAR(13) +
'from cte_ntile2'+ CHAR(13) +
')'+ CHAR(13) +
'INSERT INTO [#ChannelReading_NTILE_Results] ([MinValue],[MaxValue],[AvgValue],[DateTime],[Level])
SELECT
MIN([SIReading]) AS MinValue,
MAX([SIReading]) AS MaxValue,
AVG([SIReading]) AS AvgValue,
DATEADD(SECOND, DATEDIFF(second, min([ReadingDateTime]), max([ReadingDateTime]))/2, min([ReadingDateTime])) [DateTime],
[Level]
FROM
cte_ntile3
GROUP BY [Group3],[Level]
'
END
-- Print '*****************************PROCESS MAIN RECORDS***************************************************************************************'
EXEC sp_executesql @sql
-- (3) Create final group with s from the end of selection (Process Remainder Records)
-- Print '*****************************PROCESS REMAINDER RECORDS***************************************************************************************'
SET @SQL = ';with cte_ntile '+ CHAR(13) +
' as'+ CHAR(13) +
'('+ CHAR(13) +
' SELECT TOP ' + CAST(@s as NVARCHAR(10))+ CHAR(13) +
' ReadingDateTime,'+ CHAR(13) +
' SIReading,'+ CHAR(13) +
' RawReading'+ CHAR(13) +
' FROM ' + @ChannelReadingTableName+ CHAR(13) +
' ORDER BY ReadingDateTime DESC'+ CHAR(13) +
' )'+ CHAR(13) +
' INSERT INTO [#ChannelReading_NTILE_Results] ([MinValue],[MaxValue],[AvgValue],[DateTime],[Level])'+ CHAR(13) +
' SELECT'+ CHAR(13) +
'MIN([SIReading]) AS MinValue,'+ CHAR(13) +
'MAX([SIReading]) AS MaxValue,'+ CHAR(13) +
'AVG([SIReading]) AS AvgValue,'+ CHAR(13) +
'DATEADD(SECOND, DATEDIFF(second, min([ReadingDateTime]), max([ReadingDateTime]))/2, min([ReadingDateTime])) [DateTime],'+ CHAR(13) +
' ' +CAST(@l as NVARCHAR(10)) + ' AS [Level]'+ CHAR(13) +
' FROM'+ CHAR(13) +
'cte_ntile'
-- print @sql
-- Print '************************* PROCESS REMAINDER RECORDS*******************************************************************************************'
EXEC sp_executesql @sql
--SET @TimeFinished = CAST(SYSDATETIME() AS NVARCHAR(200))
--select @timeStarted, @TimeFinished,(datediff(millisecond,@timeStarted,@TimeFinished)) as TimeElapsed, 'PROCESS REMAINDER RECORDS - Line 1278' as Comment
SET @SQL = 'INSERT INTO ' + Quotename(@MainLevelDataTableName,'[]') +
' ([MinValue], [MaxValue], [AvgValue], [DateTime] ,[Level] )'+
' SELECT [MinValue], [MaxValue], [AvgValue], [DateTime] ,[Level] ' +
' FROM [#ChannelReading_NTILE_Results] '
-- print @SQL
EXEC sp_executesql @SQL
-- Delete null records out
SET @SQL = 'DELETE FROM ' + Quotename(@MainLevelDataTableName,'[]') +
' where IsNull(MinValue,0) = 0 and IsNull(MaxValue,0) = 0 and IsNull(AvgValue,0) = 0 '+
' and IsNull([DateTime],''01/01/1900'') = ''01/01/1900'' '
-- print @SQL
EXEC sp_executesql @SQL
--select count(1) from [#ChannelReading_NTILE_Results]
-- Extract current number of readings in table
SET @sql = 'SELECT @g = count(1) FROM ' + Quotename(@MainLevelDataTableName,'[]') + ' WHERE [Level] = ' + CAST(@l as NVARCHAR(10))
-- print @sql
EXEC sp_executesql @sql, N'@g int out', @g OUT
--select @g as g
/*******************************************************************************************************************************/
/*Aggregation - END*/
/*******************************************************************************************************************************/
TRUNCATE TABLE [#ChannelReading_NTILE_Results]
IF @G <= @NumInBatch
BEGIN
break ;
END
SET @l = @l + 1
-- 1.14 (22/12/2022)
SET @SQL = 'If Not Exists (Select top 1 1 from ' + Quotename(@MainLevelDataTableName,'[]') +' where level = ' + Cast(@l as nvarchar(20)) + ')' + CHAR(13) +
'Begin
-- Extract current number of readings in table into
SELECT @n = count(1) FROM ' + @ChannelReadingTableName + CHAR(13) +
'end'
--print @sql
EXEC sp_executesql @sql, N'@n int out', @n OUT
END -- PROCESSING RECORDS LOOP - End
/************************************************************************************************/
/*MERGE TABLE RECORD COUNTS- END */
/************************************************************************************************/
END
TRUNCATE TABLE [#ChannelReading]
TRUNCATE TABLE [#ChannelReading_NTILE_Results] --1.09 (04/11/2022)
TRUNCATE TABLE [#ChannelReadingDelta]
-- Reset variables before next Channels Readings are loaded
SET @count = 0
SET @NumRecords = 0
SET @NumGroups = 1
SET @Num_Current = 0
SET @Num_Added = 0
SET @Num_Total = 0
SET @RecordsToProcess = 0
SET @NTileValue = 0
SET @GroupLevel = 0
SET @SignatureDifferent = 0
SET @ChannelReadingTableName = NULL
SET @g = 0
SET @B = 0
SET @t = 0
SET @s = 0
SET @r = 0
SET @l = 1
FETCH NEXT FROM Test INTO @SerialNumber,
@DeviceID,
@ChannelID,
@ReadingDownloadID,
@SignatureDifferent,
@NewDownloadTable,
@DeviceChannelID,
@Tablename,
@ChannelReadingTableName,
@MainLevelDataTableName,
@TableRecordCountsTableName,
@DataHashDtoSource
-- SET @VinCounter2 = @VinCounter2 + 1
-- PRINT 'Channel readings loaded: ' + @SerialNumber + '_' + CAST(@ChannelID AS NVARCHAR(20))
END
CLOSE test
DEALLOCATE test
TheEnd:
Print 'TheEnd'
February 1, 2023 at 1:48 pm
Strong recommendation: work backwards.
Figure out what it is that you need to deliver. Then, get a set of scripts together to deliver it, that doesn't involve using a cursor. That's the best I've got. I'm not unpacking this entire thing.
I will point out though, that right after you INSERT stuff, you DELETE things. Put that into the WHERE clauses of your INSERT so that you don't move data more than once.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 1, 2023 at 5:15 pm
Agree with Grant. Something else I look at is how I can batch part of these things. Think about the process rather than the data. Sometimes temp tables and partial updates let me make something work in a batch way across steps. When I'm thinking cursor, usually I'm trying to get too involved in data rather than the batches of operations I need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply