Sample Header Ad - 728x90

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