I have a table which is used to store and handle messages and I also have two stored procudure about it,one is GetBatchMessage another is UpdateMessage(you can see from the following schema).
the column DelvieryGroup has just 4 vales in the table,the column DelvierStauts has near 200 values,the DeliveryStatus,DeliveryGroup and ProcessFlag will be updated very frquently,so I have not created any index on them.But the GetBatchMessage is very slow,500 per second.I tried to add index on DeliveryType and
ChannelId which are stable during the whole process,but it has not promoted GetBatchMessage performance a lot.I try to add some index on DeliveryStatus,DeliveryType,ChannelId,
ProcessFlag,GetBatchMessage can get 5000 records/sec but dead lock might happen during GetBatchMessage and UpdateMessage, one locking it to select,another try to lock it to update the index.
Anybody please help me.
CREATE TABLE [dbo].[MobileMessage](
[MessageId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ChannelId] [smallint] NULL,
[Message] [varbinary](max) NULL,
[DeliveryStatus] [smallint] NOT NULL,
[DeliveryGroup] [tinyint] NOT NULL,
[DeliveryType] [tinyint] NOT NULL,
[ProcessFlag] [bit] NOT NULL,
CONSTRAINT [PK_MobileMessage_MessageID] PRIMARY KEY CLUSTERED
(
[MessageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GetBatchMessage:
BEGIN
UPDATE TOP (@BatchSize) MobileMessage
SET
LastUpdatedTime = @Time,
ProcessFlag=1
OUTPUT INSERTED.Message
WHERE
DeliveryStatus=@DeliveryStatus AND
DeliveryType=@DeliveryType AND
ChannelId=@ChannelId AND
ProcessFlag =0
END
UpdateMessage:
UPDATE
dbo.MobileMessage
SET
ChannelId = ISNULL(@ChannelId, ChannelId),
Message = ISNULL(@Message, Message),
DeliveryStatus=@DeliveryStatus,
DeliveryType=@DeliveryType,
DeliveryGroup=@DeliveryGroup,
ProcessFlag =0
WHERE
MessageId = @MessageId