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
Best Answer
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.
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:
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:
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.