October 5, 2023 at 9:52 am
i try to understand why my procedure do not respect the loop in batches of 10 until the end. i must lauche 3 time to finish.
CREATE OR ALTER PROCEDURE dbo.XXX_RestoreTryenreFromGeusa_VISUAL (@DateParam DATE = NULL)
AS
/********************************************************************************************************************************************/
/* The purpose of this procedure is to insert logs. There are 11 common columns */
/* Parameters: */
/*Input: @DateParam */
/* Output: */
/* Version: 1.0 Date: 15/09/2023 Author: */
/********************************************************************************************************************************************/
/*EXECUTIONexec dbo.XXX_RestoreTryenreFromGeusa_VISUAL @DateParam='20210813'49939*/
/**/
/********************************************************************************************************************************************/
BEGIN
DECLARE @Status INT
DECLARE @ErrorMessage NVARCHAR(4000)
-- Determines the start and end date based on the @DateParam parameter
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @TryenreID INT
DECLARE @BatchSize INT = 10; -- Batch size
DECLARE @RowCount INT; -- Total number of records
DECLARE @StartRow INT = 1; -- Starting row of the batch
DECLARE @EndRow INT; -- Ending row of the batch
BEGIN TRY
IF @DateParam IS NULL
BEGIN
SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
END
ELSE
BEGIN
SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, @DateParam))
SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, @DateParam))
END
-- Get the total number of records in the Geusa table
SELECT @RowCount = COUNT(*)
FROM dbo.Geusa u
WHERE u.code = 0
AND u.status IN (0, 6, 23, 30)
AND u.UseTime >= @StartDate AND u.UseTime < @EndDate
AND u.visualid ='40131001110124597401239'
AND NOT EXISTS (SELECT 1
FROM Tryenre r
WHERE r.VisualID = u.VisualID
and r.EntryTime = u.UseTime
and r.Status = u.Status
and r.acp = u.acp
)
-- While there are records to process
WHILE @StartRow <= @RowCount
BEGIN
BEGIN TRANSACTION DLRP_RestoreTryenreFromGeusa;
-- Calculate the end row of the batch
SET @EndRow = @StartRow + @BatchSize - 1;
--INSERT operation into the Tryenre table
INSERT INTO dbo.Tryenre
(TryenreID
,EntryTime
,VisualID
,AccessCode
,ACP
,Status
,Qty
,SerialNo
,Override
,BankNo
,RecordVersion
,LastUpdate
,LastUpdatedBy
,OperationID
,ScannedVisualID
,Attraction
,Facility
)
SELECT NEXT VALUE FOR TryenreIDSequence AS TryenreID
,UseTime
,VisualID
,AccessCode
,ACP
,Status
,Qty
,SerialNo
,Override
,BankNo
,RecordVersion
,LastUpdate
,LastUpdatedBy
,OperationID
,ScannedVisualID
,AttractionID
,FacilityID
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
,u.UseTime
,COALESCE(s.VisualID ,u.VisualID)VisualID
,u.AccessCode
,u.ACP
,u.Status
,u.Qty
,u.SerialNo
,u.Override
,u.BankNo
,u.RecordVersion
,u.LastUpdate
,u.LastUpdatedBy
,u.OperationID
,u.ScannedVisualID
,u.AttractionID
,u.FacilityID
FROM Geusa u
LEFT OUTER JOIN dbo.PersuKetsi s ON CONVERT(VARCHAR(40),s.BaseID) = u.VisualID
WHERE u.code = 0
AND u.status IN (0, 6, 23, 30)
AND u.UseTime >= @StartDate AND u.UseTime < @EndDate
AND u.visualid ='40131001110124597401239'
AND NOT EXISTS (SELECT 1
FROM dbo.Tryenre r
WHERE r.VisualID = u.VisualID
AND r.EntryTime = u.UseTime
AND r.Status = u.Status
AND r.acp = u.acp
)
) AS SubQuery
WHERE RowNum BETWEEN @StartRow AND @EndRow
-- Update the starting row for the next batch
SET @StartRow = @EndRow + 1;
-- Commit the transaction
COMMIT TRANSACTION DLRP_RestoreTryenreFromGeusa;
END
END TRY
BEGIN CATCH
-- In case of error, rollback the transaction if it was started
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION DLRP_RestoreTryenreFromGeusa;
SET @ErrorMessage = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
-- Propagate the error for further handling by the application
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
-- Set output parameters for Update success
SET @Status = 0
END;
GO
October 5, 2023 at 10:40 am
that's because you don't start from the same set of data due to the "not exists" credential. ( which checks for existance in the target table !! )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 5, 2023 at 10:45 am
I would say that your left outer join to PersuKetsi results in you having more rows being processed than on the query you get the count(*) from.
change your first query to include it
October 5, 2023 at 12:43 pm
i take all your remarks and it's working and it's works . thanks
CREATE OR ALTER PROCEDURE dbo.XXX_RestoreTryenreFromGeusa_VISUAL (@DateParam DATE = NULL)
AS
/********************************************************************************************************************************************/
/* The purpose of this procedure is to insert logs. There are 11 common columns */
/* Parameters: */
/*Input: @DateParam */
/* Output: */
/* Version: 1.0 Date: 15/09/2023 Author: */
/********************************************************************************************************************************************/
/*EXECUTIONexec dbo.XXX_RestoreTryenreFromGeusa_VISUAL @DateParam='20210813'49939*/
/**/
/********************************************************************************************************************************************/
BEGIN
DECLARE @Status INT
DECLARE @ErrorMessage NVARCHAR(4000)
-- Determines the start and end date based on the @DateParam parameter
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @TryenreID INT
DECLARE @BatchSize INT = 10; -- Batch size
DECLARE @RowCount INT; -- Total number of records
DECLARE @StartRow INT = 1; -- Starting row of the batch
DECLARE @EndRow INT; -- Ending row of the batch
BEGIN TRY
IF @DateParam IS NULL
BEGIN
SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
END
ELSE
BEGIN
SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, @DateParam))
SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, @DateParam))
END
-- Get the total number of records in the Geusa table
SELECT @RowCount = COUNT(*)
FROM dbo.Geusa u
WHERE u.code = 0
AND u.status IN ( 0, 6, 23, 30 )
AND u.UseTime >= @StartDate
AND u.UseTime < @EndDate
AND u.visualid = '40131001110124597401239'
-- While there are records to process
WHILE @StartRow <= @RowCount
BEGIN
BEGIN TRANSACTION DLRP_RestoreTryenreFromGeusa;
-- Calculate the end row of the batch
SET @EndRow = @StartRow + @BatchSize - 1;
--INSERT operation into the Tryenre table
INSERT INTO dbo.Tryenre
(
TryenreID,
EntryTime,
VisualID,
AccessCode,
ACP,
Status,
Qty,
SerialNo,
Override,
BankNo,
RecordVersion,
LastUpdate,
LastUpdatedBy,
OperationID,
ScannedVisualID,
Attraction,
Facility
)
SELECT NEXT VALUE FOR TryenreIDSequence AS TryenreID,
,SubQuery.UseTime
,coalesce(convert(varchar(40),s.BaseID) ,SubQuery.VisualID)VisualID
,SubQuery.AccessCode
,SubQuery.ACP
,SubQuery.Status
,SubQuery.Qty
,SubQuery.SerialNo
,SubQuery.Override
,SubQuery.BankNo
,SubQuery.RecordVersion
,SubQuery.LastUpdate
,SubQuery.LastUpdatedBy
,SubQuery.OperationID
,ScannedVisualID
,SubQuery.AttractionID
,SubQuery.FacilityID
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
u.UseTime,
u.VisualID,
u.AccessCode,
u.ACP,
u.Status,
u.Qty,
u.SerialNo,
u.Override,
u.BankNo,
u.RecordVersion,
u.LastUpdate,
u.LastUpdatedBy,
u.OperationID,
u.ScannedVisualID,
u.AttractionID,
u.FacilityID
FROM Geusa u
WHERE u.code = 0
AND u.status IN ( 0, 6, 23, 30 )
AND u.UseTime >= @StartDate
AND u.UseTime < @EndDate
AND u.visualid = '40131001110124597401239'
) AS SubQuery
LEFT JOIN dbo.SuperTickets s On convert(varchar(40),s.BaseID) = SubQuery.VisualID
WHERE RowNum BETWEEN @StartRow AND @EndRow
AND NOT EXISTS (SELECT 1
FROM dbo.Tryenre r
WHERE r.VisualID = SubQuery.VisualID
AND r.EntryTime = SubQuery.UseTime
AND r.Status = SubQuery.Status
and r.acp = SubQuery.acp
)
-- Update the starting row for the next batch
SET @StartRow = @EndRow + 1;
-- Commit the transaction
COMMIT TRANSACTION DLRP_RestoreTryenreFromGeusa;
END
END TRY
BEGIN CATCH
-- In case of error, rollback the transaction if it was started
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION DLRP_RestoreTryenreFromGeusa;
SET @ErrorMessage = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
-- Propagate the error for further handling by the application
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
-- Set output parameters for Update success
SET @Status = 0
END;
GO
October 5, 2023 at 5:14 pm
I prefer to use OFFSET/FETCH NEXT to run batches of records in a loop. I've updated your query to reflect that.
ROW_NUMBER(), because we don't need a row number using this method.Here is the updated query. Note, that this is completely UNTESTED.
CREATE OR ALTER PROCEDURE dbo.XXX_RestoreTryenreFromGeusa_VISUAL (@DateParam DATE = NULL)
AS
/********************************************************************************************************************************************/
/* The purpose of this procedure is to insert logs. There are 11 common columns */
/* Parameters: */
/*Input: @DateParam */
/* Output: */
/* Version: 1.0 Date: 15/09/2023 Author: */
/********************************************************************************************************************************************/
/*EXECUTIONexec dbo.XXX_RestoreTryenreFromGeusa_VISUAL @DateParam='20210813'49939*/
/**/
/********************************************************************************************************************************************/
BEGIN
DECLARE @Status INT
DECLARE @ErrorMessage NVARCHAR(4000)
-- Determines the start and end date based on the @DateParam parameter
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @TryenreID INT
DECLARE @BatchSize INT = 10; -- Batch size
/* Replace three separate variables with just one. */
DECLARE @CurCount INT = @BatchSize;
BEGIN TRY
IF @DateParam IS NULL
BEGIN
SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
END
ELSE
BEGIN
SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, @DateParam))
SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, @DateParam))
END
/* Removed unnecessary query to calculate total rows. */
-- While there are records to process
WHILE @CurCount = @BatchSize
BEGIN
BEGIN TRANSACTION DLRP_RestoreTryenreFromGeusa;
/* Removed extraneous parameter setting here. */
--INSERT operation into the Tryenre table
INSERT INTO dbo.Tryenre
(
TryenreID,
EntryTime,
VisualID,
AccessCode,
ACP,
Status,
Qty,
SerialNo,
Override,
BankNo,
RecordVersion,
LastUpdate,
LastUpdatedBy,
OperationID,
ScannedVisualID,
Attraction,
Facility
)
SELECT NEXT VALUE FOR TryenreIDSequence AS TryenreID,
,u.UseTime
,u.VisualID -- Why use the complex formula here?
,u.AccessCode
,u.ACP
,u.Status
,u.Qty
,u.SerialNo
,u.Override
,u.BankNo
,u.RecordVersion
,u.LastUpdate
,u.LastUpdatedBy
,u.OperationID
,u.ScannedVisualID
,u.AttractionID
,u.FacilityID
FROM Geusa u
LEFT JOIN dbo.SuperTickets s On convert(varchar(40),s.BaseID) = SubQuery.VisualID -- Do you really need to do the conversion here?
WHERE u.code = 0
AND u.status IN ( 0, 6, 23, 30 )
AND u.UseTime >= @StartDate
AND u.UseTime < @EndDate
AND u.visualid = '40131001110124597401239'
AND NOT EXISTS (SELECT 1
FROM dbo.Tryenre r
WHERE r.VisualID = SubQuery.VisualID
AND r.EntryTime = SubQuery.UseTime
AND r.Status = SubQuery.Status
and r.acp = SubQuery.acp
)
ORDER BY u.VisualID
OFFSET 0 ROWS
FETCH NEXT @BatchSize ROWS ONLY;
-- Changed the WHILE loop conditions update
SET @CurCount = @@ROWCOUNT;
-- Commit the transaction
COMMIT TRANSACTION DLRP_RestoreTryenreFromGeusa;
END
END TRY
BEGIN CATCH
-- In case of error, rollback the transaction if it was started
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION DLRP_RestoreTryenreFromGeusa;
SET @ErrorMessage = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
-- Propagate the error for further handling by the application
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
-- Set output parameters for Update success
SET @Status = 0
END;
GO
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 5, 2023 at 6:00 pm
Why don't you take the loop out and do it all in one statement?
October 5, 2023 at 9:18 pm
i applicate your remarks but i raise the following error
The NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified
USE [galaxytymtest]
GO
CREATE OR ALTER PROCEDURE dbo.XXX_RestoreyrteeneRFromegasuXX (@DateParam DATE = NULL)
AS
/********************************************************************************************************************************************/
/* Parametre : */
/*Entree : @DateParam*/
/* sortie :*/
/* Version : 1.0 Date : 15/09/2023 Auteur:*/
/********************************************************************************************************************************************/
/*
EXECUTION
exec dbo.XXX_RestoreyrteeneRFromegasuXX @DateParam='20210813'--49939 joinutre 47102
select count(*) from yrteeneR
*/
/********************************************************************************************************************************************/
BEGIN
DECLARE @ApplicationName VARCHAR(4) = 'RestoreyrteeneR'
DECLARE @ProcessName VARCHAR(30) = OBJECT_NAME(@@PROCID)
DECLARE @EventDataIn VARCHAR(200) = ''
DECLARE @EventDataOut VARCHAR(200) = ''
DECLARE @EventStatusDescr VARCHAR(100) = ''
DECLARE @Status INT
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @BatchSize INT = 10000; -- Taille du lot
DECLARE @CurCount INT = @BatchSize;
--truncate table dbo.yrteeneR
BEGIN TRY
IF @DateParam IS NULL
BEGIN
SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
END
ELSE
BEGIN
SET @StartDate = DATEADD(HH, 3, CONVERT(DATETIME, @DateParam))
SET @EndDate = DATEADD(HH, 27, CONVERT(DATETIME, @DateParam))
END
-- Changed the WHILE loop conditions update
WHILE @CurCount = @BatchSize
BEGIN
BEGIN TRANSACTION XXX_RestoreyrteeneRFromegasu;
INSERT INTO dbo.yrteeneR
(yrteeneRID
,EntryTime
,VisualID
,AccessCode
,ACP
,Status
,Qty
,SerialNo
,Override
,BankNo
,RecordVersion
,LastUpdate
,LastUpdatedBy
,OperationID
,ScannedVisualID
,Attraction
,Facility
)
SELECT NEXT VALUE FOR yrteeneRIDSequence AS yrteeneRID
,u.UseTime
,u.VisualID
,u.AccessCode
,u.ACP
,u.Status
,u.Qty
,u.SerialNo
,u.Override
,u.BankNo
,u.RecordVersion
,u.LastUpdate
,u.LastUpdatedBy
,u.OperationID
,u.ScannedVisualID
,u.AttractionID
,u.FacilityID
FROM egasu u
WHERE u.code = 0
AND u.status IN (0, 6, 23, 30)
AND u.UseTime >= @StartDate
AND u.UseTime < @EndDate
AND NOT EXISTS (SELECT 1
FROM dbo.yrteeneR r
WHERE r.VisualID = u.VisualID
AND r.EntryTime = u.UseTime
AND r.Status = u.Status
and r.acp = u.acp
)
ORDER BY u.VisualID
OFFSET 0 ROWS
FETCH NEXT @BatchSize ROWS ONLY;
-- Changed the WHILE loop conditions update
SET @CurCount = @@ROWCOUNT;
-- Commit the transaction
COMMIT TRANSACTION XXX_RestoreyrteeneRFromegasu;
END;
END TRY
BEGIN CATCH
-- In case of error, rollback the transaction if it was started
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION XXX_RestoreyrteeneRFromegasu;
SET @ErrorMessage = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
-- Propagate the error for further handling by the application
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
-- Set output parameters for Update success
SET @Status = 0
END;
GO
October 6, 2023 at 7:33 pm
Here are some recommendations:
A couple of things I would also change:
SET @StartDate = DATEADD(HH, 3, DATEADD(day, DATEDIFF(day, 0, COALESCE(@DateParm, GETDATE())), 0));
SET @EndDate = DATEADD(HH, 27, DATEADD(day, DATEDIFF(day, 0, COALESCE(@DateParm, GETDATE())), 0));
Example loop:
WHILE 1 = 1
BEGIN
INSERT TOP (@BatchSize) INTO dbo.yrteeneR
(yrteeneRID
,EntryTime
,VisualID
,AccessCode
,ACP
,Status
,Qty
,SerialNo
,Override
,BankNo
,RecordVersion
,LastUpdate
,LastUpdatedBy
,OperationID
,ScannedVisualID
,Attraction
,Facility
)
SELECT NEXT VALUE FOR yrteeneRIDSequence AS yrteeneRID
,u.UseTime
,u.VisualID
,u.AccessCode
,u.ACP
,u.Status
,u.Qty
,u.SerialNo
,u.Override
,u.BankNo
,u.RecordVersion
,u.LastUpdate
,u.LastUpdatedBy
,u.OperationID
,u.ScannedVisualID
,u.AttractionID
,u.FacilityID
FROM egasu u
WHERE u.code = 0
AND u.status IN (0, 6, 23, 30)
AND u.UseTime >= @StartDate
AND u.UseTime < @EndDate
AND NOT EXISTS (SELECT 1
FROM dbo.yrteeneR r
WHERE r.VisualID = u.VisualID
AND r.EntryTime = u.UseTime
AND r.Status = u.Status
and r.acp = u.acp
);
SET @CurCount = @@ROWCOUNT;
IF @CurCount < @BatchSize BREAK --No more rows to be inserted
END
As a further optimization, I would set the default constraint in the table to use NEXT VALUE FOR and eliminate it from the procedure. If you did that, you could then use an ORDER BY in the query to specify the order to be inserted and it will be generated in that order.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply