Hard delete vs soft delete performance

Question

I've got this table that is intended to have a lot of activity. Anything that's in it indicates that a user action is still pending. Once deleted, it would indicate that action is no longer pending. Other areas of the system depend on this, so the table on its own doesn't really explain the scenario. However, the question I have is whether I should keep the table as is and delete, when the pending action is done, or whether I should add a flag column in and update.

Note, a record may be deleted in the same second it is inserted. I'm hoping to support up to 100 per second, but would not like to have that as the limit.

I am using SQL Server 2014 Enterprise Edition.

Here's the table's definition (all indexes are based on select queries using this table):

CREATE TABLE [dbo].[OpenRounds](
    [OpenRoundId] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [GameActivityId] [bigint] NOT NULL,
    [VendorId] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [UserBonusId] [bigint] NULL,
    [VendorRoundId] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_GamesOpenRounds] PRIMARY KEY CLUSTERED 
(
    [OpenRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsUserIdUserBonusId] ON [dbo].[OpenRounds]
(
    [UserId] ASC,
    [UserBonusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsUserIdVendorIdVendorRoundId] ON [dbo].[OpenRounds]
(
    [UserId] ASC,
    [VendorId] ASC,
    [VendorRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsVendorIdVendorRoundId] ON [dbo].[OpenRounds]
(
    [VendorId] ASC,
    [VendorRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Answer 1

If you are designing your system with the intention of supporting thousands of transactions per second, you probably want to design a queuing system that does not rely on inserts. Inserts are costly, and depending on the design of the queue table may result in the last page of the queue table being a "hot spot" that limits the rate of throughput for the system.

The following design allows for up to 100,000 concurrent transactions through the Rounds table.

CREATE TABLE dbo.OpenRounds
(
    OpenRoundID BIGINT NOT NULL
        CONSTRAINT PK_GamesOpenRounds 
        PRIMARY KEY CLUSTERED
    , UserID INT NULL
    , GameActivityID BIGINT NULL
    , VendorID INT NULL
    , RoundDate DATETIME NULL
    , UserBonusID BIGINT NULL
    , VendorRoundID NVARCHAR(50) NULL
    , ReferenceCount INT NOT NULL
        CONSTRAINT DF_OpenRounds_ReferenceCount
        DEFAULT ((0))
);

INSERT INTO dbo.OpenRounds (OpenRoundID)
SELECT TOP(100000) /* top 100,000 -> we're creating 100,000 slots */
    rn = ROW_NUMBER() OVER (ORDER BY o1.object_id)
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3;

GO
CREATE SEQUENCE dbo.RoundSlotSequence
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000
CYCLE
CACHE 10000;
GO

The sequence created above is designed to roll-over once it reaches 100,000. This coincides with the number of rows we created in the dbo.OpenRounds table.

To push messages into the OpenRounds table, you can use the following proc:

CREATE PROCEDURE dbo.PushRound
(
    @UserID INT
    , @GameActivityID BIGINT
    , @VendorID INT
    , @RoundDate DATETIME
    , @UserBonusID BIGINT
    , @VendorRoundID NVARCHAR(50)
)
AS
BEGIN
    DECLARE @SlotID INT;
    DECLARE @SequenceID INT;
    DECLARE @TryCount INT = 0;
    SELECT @SequenceID = NEXT VALUE FOR dbo.RoundSlotSequence;
    WHILE @SlotID IS NULL AND @TryCount < 50
    BEGIN
        UPDATE dbo.OpenRounds WITH (ROWLOCK)
        SET ReferenceCount = ReferenceCount + 1
            , @SlotID = OpenRoundID
            , UserID = @UserID
            , GameActivityID = @GameActivityID
            , VendorID = @VendorID
            , RoundDate = @RoundDate
            , UserBonusID = @UserBonusID
            , VendorRoundID = @VendorRoundID
        WHERE ReferenceCount = 0
            AND OpenRoundID = @SequenceID;
        /* If @SlotID IS NULL the slot was not available
            - wait 5 milliseconds before checking again 
            to see if the slot is open
        */
        IF @SlotID IS NULL WAITFOR DELAY '00:00:00.005';
        SET @TryCount += 1;
    END
    IF @SlotID IS NULL
        RETURN 1
    ELSE 
        RETURN @SlotID
END;
GO

This procedure can be used to obtain the next message from the OpenRounds table. Typically this would be ran inside a loop that continuously looks for rows to process:

CREATE PROCEDURE dbo.PopRound
(
    @UserID INT OUTPUT
    , @GameActivityID BIGINT OUTPUT
    , @VendorID INT OUTPUT 
    , @RoundDate DATETIME OUTPUT
    , @UserBonusID BIGINT OUTPUT
    , @VendorRoundID NVARCHAR(50) OUTPUT
    , @MaxRetries INT = 2000 /* 10 seconds 
                default maximum wait time */
)
AS
BEGIN
    DECLARE @SlotID INT;
    DECLARE @TryCount INT = 0;
    WHILE @SlotID IS NULL AND @TryCount < @MaxRetries
    BEGIN
        UPDATE dbo.OpenRounds WITH (ROWLOCK)
        SET ReferenceCount = ReferenceCount - 1
            , @SlotID = OpenRoundID
            , @UserID = UserID
            , @GameActivityID = GameActivityID
            , @VendorID = VendorID
            , @RoundDate = RoundDate
            , @UserBonusID = UserBonusID
            , @VendorRoundID = VendorRoundID
        WHERE ReferenceCount > 0;
        IF @SlotID IS NULL WAITFOR DELAY '00:00:00.005';
        SET @TryCount += 1;
    END
END;
GO

This design is loosely based upon concepts I saw at the Exadat.co.uk Super-scaling SQL Server site, by Chris Adkin. His site provides exceptional material and guidance around pushing SQL Server to its limits, including very in-depth details about the hardware and how SQL Server interacts with it. I am not associated in any way with Chris, or his website.