September 3, 2010 at 3:24 am
Hi
When I am trying to run the below procedure, getting the below problem:
1. Getnextkey procedure is called in the below procedure which is used to get tne next event number. The same procedure (getnextkey) is called from different areas(like from different procedures or applications). For ex: event no is the primary key and when i ran the procedre and at the same time getnextkey is also called from applications getting the duplicate event nos which is causing th problem.
USE [KRM]
GO
/****** Object: StoredProcedure [dbo].[KNLEventCopy] Script Date: 09/03/2010 11:08:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[KNLEventCopy] (@startdatetime datetime, @enddatetime datetime)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
/*
Deleting all temporary tables used during the migration script
*/
--Getting the hours irrespective of summer or winter time difference
DECLARE @hhtime as int
--SELECT @hhtime=time from navcountry where country=@cntry and season='Summer'
--select @hhtime =
select @hhtime=datediff(hh,getutcdate(),getdate())
select @hhtime=@hhtime+1
--patch end
IF OBJECT_ID('tempdb..#Events') is not null DROP TABLE #Events
DECLARE @Seed bigint
SELECT @Seed = ISNULL(MAX(Event_PK) + 1, 1) FROM Event_1
--/* Get list of Events to copy */
SELECT [EquipmentNumber_FK]
,[StartTimestamp]
,[RemoteMonitoringType_FK]
,[MessageType_FK]
,[CEN]
,[Floor]
,[EndTimestamp]
,[FailureClassDelay]
,[EquipmentMovingStatus]
,[OperationMode]
,[DoorZone]
,[DoorStatus]
,[DriveStatus]
,[CarLoad]
,[Comment]
,[DriveDetectedFault1]
,[DriveDetectedFault2]
,[DriveDetectedFault3]
,[FaultClass]
,[SourceUpper]
,[SourceLower]
,[SourceLeft]
,[SourceRight]
,[FaultCurrentlyActive]
,[FaultShutsDown]
,[FaultNeedsManualReset]
,[FaultIsEmergencyStop]
,[FaultInDevice1]
,[FaultInDevice2]
,[FaultInDevice3]
,[FaultInDevice4]
,[FaultInDevice5]
,[FaultInDevice6]
,[FaultInDevice7]
,[FaultInDevice8]
,[BrakeSlidingDistance]
,[StoppingDistanceData]
,[Knx3ExternalInput1]
,[Knx3ExternalInput2]
,[Knx3SystemFailureReason_FK]
,[ReportCall]
,[ReportCallHandledby]
,[CalculatedEndTimestamp]
,[FrontEndID]
,[CEN_EquipmentStatus]
,[CreateDate]
,[Creator]
INTO #Events
FROM Event_1
WHERE1=2
ALTER TABLE #Events
ADD Event_PK int IDENTITY(1,1)
DBCC CHECKIDENT (#Events,RESEED, @Seed)
--code for thesting needs to be removed after that
SELECT EquipmentNumber_FK, DATEADD(hh, - @hhtime, StartTimestamp) AS StartTimestamp, RemoteMonitoringType_FK, MessageType_FK, CEN, Floor,
DATEADD(hh, - @hhtime, EndTimestamp) AS EndTimestamp, FailureClassDelay, EquipmentMovingStatus, OperationMode, DoorZone, DoorStatus, DriveStatus,
CarLoad, Comment, DriveDetectedFault1, DriveDetectedFault2, DriveDetectedFault3, FaultClass, SourceUpper, SourceLower, SourceLeft, SourceRight,
FaultCurrentlyActive, FaultShutsDown, FaultNeedsManualReset, FaultIsEmergencyStop, FaultInDevice1, FaultInDevice2, FaultInDevice3,
FaultInDevice4, FaultInDevice5, FaultInDevice6, FaultInDevice7, FaultInDevice8, BrakeSlidingDistance, StoppingDistanceData, Knx3ExternalInput1,
Knx3ExternalInput2, Knx3SystemFailureReason_FK, ReportCall, ReportCallHandledby, CalculatedEndTimestamp, FrontEndID, CEN_EquipmentStatus,
DATEADD(hh, - @hhtime, CreateDate) AS CreateDate, 'KNXMCopy' AS Creator INTO Test
FROM TNLNTS30.KRM.dbo.Event AS Event_1
WHERE (NOT ((CAST(EquipmentNumber_FK AS nvarchar) + CAST(CreateDate AS nvarchar)) IN
(SELECT TOP (100) PERCENT CAST(EquipmentNumber_FK AS nvarchar) + CAST(CreateDate AS nvarchar) AS EventID
FROM Event
ORDER BY CreateDate DESC))) AND (DATEADD(hh, - @hhtime, CreateDate) > CONVERT(DATETIME, @startdatetime, 102) AND (DATEADD(hh, - @hhtime, CreateDate) < CONVERT(DATETIME, @enddatetime, 102)))
ORDER BY DATEADD(hh, - @hhtime, CreateDate) DESC
--end of testing code
INSERT INTO [#Events]
([EquipmentNumber_FK]
,[StartTimestamp]
,[RemoteMonitoringType_FK]
,[MessageType_FK]
,[CEN]
,[Floor]
,[EndTimestamp]
,[FailureClassDelay]
,[EquipmentMovingStatus]
,[OperationMode]
,[DoorZone]
,[DoorStatus]
,[DriveStatus]
,[CarLoad]
,[Comment]
,[DriveDetectedFault1]
,[DriveDetectedFault2]
,[DriveDetectedFault3]
,[FaultClass]
,[SourceUpper]
,[SourceLower]
,[SourceLeft]
,[SourceRight]
,[FaultCurrentlyActive]
,[FaultShutsDown]
,[FaultNeedsManualReset]
,[FaultIsEmergencyStop]
,[FaultInDevice1]
,[FaultInDevice2]
,[FaultInDevice3]
,[FaultInDevice4]
,[FaultInDevice5]
,[FaultInDevice6]
,[FaultInDevice7]
,[FaultInDevice8]
,[BrakeSlidingDistance]
,[StoppingDistanceData]
,[Knx3ExternalInput1]
,[Knx3ExternalInput2]
,[Knx3SystemFailureReason_FK]
,[ReportCall]
,[ReportCallHandledby]
,[CalculatedEndTimestamp]
,[FrontEndID]
,[CEN_EquipmentStatus]
,[CreateDate]
,[Creator])
SELECT EquipmentNumber_FK, DATEADD(hh, - @hhtime, StartTimestamp) AS StartTimestamp, RemoteMonitoringType_FK, MessageType_FK, CEN, Floor,
DATEADD(hh, - @hhtime, EndTimestamp) AS EndTimestamp, FailureClassDelay, EquipmentMovingStatus, OperationMode, DoorZone, DoorStatus, DriveStatus,
CarLoad, Comment, DriveDetectedFault1, DriveDetectedFault2, DriveDetectedFault3, FaultClass, SourceUpper, SourceLower, SourceLeft, SourceRight,
FaultCurrentlyActive, FaultShutsDown, FaultNeedsManualReset, FaultIsEmergencyStop, FaultInDevice1, FaultInDevice2, FaultInDevice3,
FaultInDevice4, FaultInDevice5, FaultInDevice6, FaultInDevice7, FaultInDevice8, BrakeSlidingDistance, StoppingDistanceData, Knx3ExternalInput1,
Knx3ExternalInput2, Knx3SystemFailureReason_FK, ReportCall, ReportCallHandledby, CalculatedEndTimestamp, FrontEndID, CEN_EquipmentStatus,
DATEADD(hh, - @hhtime, CreateDate) AS CreateDate, 'KNXMCopy' AS Creator
FROM TNLNTS30.KRM.dbo.Event AS Event_1
WHERE (NOT ((CAST(EquipmentNumber_FK AS nvarchar) + CAST(CreateDate AS nvarchar)) IN
(SELECT TOP (100) PERCENT CAST(EquipmentNumber_FK AS nvarchar) + CAST(CreateDate AS nvarchar) AS EventID
FROM Event
ORDER BY CreateDate DESC))) AND (DATEADD(hh, - @hhtime, CreateDate) > CONVERT(DATETIME, @startdatetime, 102)AND (DATEADD(hh, - @hhtime, CreateDate) < CONVERT(DATETIME, @enddatetime, 102)))
ORDER BY DATEADD(hh, - @hhtime, CreateDate) DESC
INSERT INTO Event_1
([EquipmentNumber_FK]
,[StartTimestamp]
,[RemoteMonitoringType_FK]
,[MessageType_FK]
,[CEN]
,[Floor]
,[EndTimestamp]
,[FailureClassDelay]
,[EquipmentMovingStatus]
,[OperationMode]
,[DoorZone]
,[DoorStatus]
,[DriveStatus]
,[CarLoad]
,[Comment]
,[DriveDetectedFault1]
,[DriveDetectedFault2]
,[DriveDetectedFault3]
,[FaultClass]
,[SourceUpper]
,[SourceLower]
,[SourceLeft]
,[SourceRight]
,[FaultCurrentlyActive]
,[FaultShutsDown]
,[FaultNeedsManualReset]
,[FaultIsEmergencyStop]
,[FaultInDevice1]
,[FaultInDevice2]
,[FaultInDevice3]
,[FaultInDevice4]
,[FaultInDevice5]
,[FaultInDevice6]
,[FaultInDevice7]
,[FaultInDevice8]
,[BrakeSlidingDistance]
,[StoppingDistanceData]
,[Knx3ExternalInput1]
,[Knx3ExternalInput2]
,[Knx3SystemFailureReason_FK]
,[ReportCall]
,[ReportCallHandledby]
,[CalculatedEndTimestamp]
,[FrontEndID]
,[CEN_EquipmentStatus]
,[CreateDate]
,[Creator]
,[Event_PK])
Select * From #Events
DECLARE @Event_PK INT
EXEC KRM.dbo.GetNextKeyValue_p 'Event', @Event_PK OUTPUT,@@rowcount
Select * From #Events
Can anyone tell me how to overcome this behaviour. Is it right to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the procedure.
Thanks in Advance
Regards,
Naveen
September 3, 2010 at 3:31 am
You have forgotten to describe the problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2010 at 3:48 am
why are you using a GETNEWKEY functionality to generate a number for your PK column instead of allowing an identity column to generate the value for you? the identity property on a column is the proven, fastest and best way to handle this without concurrency issues you are tripping over. it is also the recommended best practice.
is the getnewkey issueing unique ID's on a per table basis, or is it the same series being used for more than one table ? ie i could generate id #1 for table1, but id #2 is used by table2?
Lowell
September 3, 2010 at 3:48 am
The problem I am facing is:
EventID is the primary key in the EVENT table. Whenever an event occurs, getnextkey is generated and an next eventid is generated from getnextkey procedure and it will inserts into event table.
But when i am running the above procedure, it is taking 45 secords to complete it and by the time same event id is generated from application as well because of which duplicate eventids are inserted into event table.
Hope you got the problem
September 3, 2010 at 3:52 am
Might be we can use identity but not supposed to use this now. The code for getting next key is:
ALTER PROCEDURE [dbo].[GetNextKeyValue_p]
----------------------------------------------------------------------------------
-- Version Date: 22.03.2006
-- Copyright © 2006 Kone Oyj
----------------------------------------------------------------------------------
(
@TableNameVARCHAR(50)
,@KeyValueINT OUTPUT
,@Add2KeyValueINT = 1
)
AS
BEGIN TRAN T1
-- IF @Add2KeyValue is null SET @Add2KeyValue = 1
UPDATE dbo.NextKeyValue
SET KeyValue = KeyValue + @Add2KeyValue
WHERE TableName_PK = @TableName
SELECT
@KeyValue = KeyValue
FROM dbo.NextKeyValue
WHERE TableName_PK = @TableName
COMMIT TRAN T1
Need some help on how to overcome this. Also how to runthe procedure in single user mode.
September 3, 2010 at 3:58 am
mostly your problem is your design is not using an identity...but it is also procedurally based, and not thinking in terms of SET based solutions.
what if i were to insert TWO or more rows at the same time...for example, i might copy a weeks worth of events to be the new events for next week; what if i wanted to copy a years worth?
your code requires row by agonizing row processing...replacing the code which is generating the next event id with an identity allows concurrency(more than one person at the same time) and allows higher thruput.
i've fixed this exact same issue with another agency before... it's a core design issue which may or may not make it thru the chain of command.
also, your getnewkey will fail for new tables....it's assuming that an entry already exists for any table you call it for.
Lowell (9/3/2010)
why are you using a GETNEWKEY functionality to generate a number for your PK column instead of allowing an identity column to generate the value for you? the identity property on a column is the proven, fastest and best way to handle this without concurrency issues you are tripping over. it is also the recommended best practice.is the getnewkey issueing unique ID's on a per table basis, or is it the same series being used for more than one table ? ie i could generate id #1 for table1, but id #2 is used by table2?
Lowell
September 3, 2010 at 4:16 am
Lowell, Same problem I am facing when trying to insert 1 month data I am getting duplicate records. Can you tell me how to overcome this?
Naveen
September 3, 2010 at 5:03 am
ekknaveen (9/3/2010)
Lowell, Same problem I am facing when trying to insert 1 month data I am getting duplicate records. Can you tell me how to overcome this?Naveen
Read about sequential tables in this thread.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply