Performance issue - using try catch to return data to service & how to use partition table with unique index
0
votes
2
answers
136
views
I'm working on enviorment SQL azure.
I've got a stored procedure that runs from several AKS simultaneously
and insert a message.
So it's a race for who writes the message first and the rest fails.
The insert fails due to Unique index and send the Id of the failed message to the service.
here is the stored procedure:
CREATE OR ALTER PROCEDURE Saga.AddMessage
(
@SagaIdentity NVARCHAR(256),
@MessageId NVARCHAR(64),
@SagaMessage NVARCHAR(MAX),
@Created DATETIME2(7),
@Updated DATETIME2(7),
@SubscriptionStatus SMALLINT
)
AS
BEGIN
BEGIN TRY
INSERT INTO [Saga].[SubscribersMessages] ([SagaIdentity], [MessageId], [SagaMessage], [Created], [Updated], [SubscriptionStatus])
SELECT @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus
WHERE NOT EXISTS (SELECT 1 FROM [Saga].[SubscribersMessages] WHERE MessageId = @MessageId);
SELECT [Id] AS Results
FROM [Saga].[SubscribersMessages]
WHERE MessageId = @MessageId
END TRY
BEGIN CATCH
SELECT [Id] AS Results
FROM [Saga].[SubscribersMessages]
WHERE MessageId = @MessageId
END CATCH;
END
and here is the unique index :
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Saga.SubscribersMessages','U') AND name = 'UX_SubscribersMessages_MessageId')
BEGIN
CREATE UNIQUE INDEX UX_SubscribersMessages_MessageId
ON [Saga].[SubscribersMessages] (MessageId);
END
I have a couple of problems with this service:
1. Peformance issue - I don't like the fact the the process rely CATCH on failed processed to return the Id.
2. I want to Partition the table using the Created field in order to truncate partition in the future and i get an error -
Column 'Created' is partitioning column of the index 'UX_SubscribersMessages_MessageId'. Partition columns for a unique index must be a subset of the index key
I thought about the MessageId as my Primary Key as it is unique
but there are downsides to that as the MessageId is NVARCHAR(64).
Here is the execution plan:
Asked by dexon
(65 rep)
May 7, 2024, 08:12 AM
Last activity: May 9, 2024, 06:41 AM
Last activity: May 9, 2024, 06:41 AM