Reputation: 1404

SQL Query Optimization with million of records

I am currently using below query to get record based on senderid. I have 2 million record in messagein table and also entries comes parallel in this table as well. But it take morethen 5 sec to return result. This table having only one non-clustered index created on Providerid (include column priorityid, senderid,maskid) Can any one sql expert help me out on this.

ALTER PROCEDURE [dbo].[GetNextSmsQueue] @NoOfRow int,
                                        @GatewayId int
AS
    BEGIN TRY
        BEGIN TRAN;
        CREATE TABLE #SmsIn ([Id] [bigint] NOT NULL,
                             [UserId] [bigint] NOT NULL,
                             [MaskId] [varchar](50) NOT NULL,
                             [Number] [varchar](20) NOT NULL,
                             [Message] [nvarchar](1300) NOT NULL,
                             [SenderId] [varchar](20) NOT NULL,
                             [UDH] [nvarchar](50) NULL,
                             [Credit] [int] NOT NULL,
                             [CurrentStatus] [int] NOT NULL,
                             [CheckDND] [bit] NULL,
                             [CheckFail] [bit] NULL,
                             [CheckBlackList] [bit] NULL,
                             [ProviderId] [int] NULL,
                             [PriorityId] [int] NULL,
                             [ScheduleDate] [datetime] NOT NULL,
                             [CreatedDate] [datetime] NOT NULL,
                             [EsmClass] [nvarchar](10) NOT NULL,
                             [DataCoding] [int] NOT NULL,
                             [Priority] [int] NOT NULL,
                             [Interface] [int] NOT NULL);
        DECLARE @PriorityIn table ([PriorityId] [int] NOT NULL);
        DECLARE @COUNT bigint;

        INSERT INTO @PriorityIn
        SELECT PriorityId
        FROM PriorityProviders
        WHERE ProviderId = @GatewayId
          AND Type = 0;

        SELECT @COUNT = COUNT(*)
        FROM MessageIn m
             LEFT JOIN @PriorityIn o ON m.PriorityId = o.PriorityId
        WHERE ((ProviderId IS NULL
            AND o.PriorityId IS NOT NULL)
            OR ProviderId = @GatewayId);

        IF @COUNT > 0
        BEGIN
            INSERT INTO #SmsIn ([Id],
                                [UserId],
                                [MaskId],
                                [Number],
                                [Message],
                                [SenderId],
                                [UDH],
                                [Credit],
                                [CurrentStatus],
                                [CheckDND],
                                [CheckFail],
                                [CheckBlackList],
                                [ProviderId],
                                [PriorityId],
                                [ScheduleDate],
                                [CreatedDate],
                                [EsmClass],
                                [DataCoding],
                                [Priority],
                                [Interface])
            (SELECT [Id],
                    [UserId],
                    [MaskId],
                    [Number],
                    [Message],
                    [SenderId],
                    [UDH],
                    [Credit],
                    [CurrentStatus],
                    [CheckDND],
                    [CheckFail],
                    [CheckBlackList],
                    [ProviderId],
                    [PriorityId],
                    [ScheduleDate],
                    [CreatedDate],
                    [EsmClass],
                    [DataCoding],
                    [Priority],
                    [Interface]
             FROM MessageIn
             WHERE MaskId IN (SELECT MaskId
                              FROM (SELECT ROW_NUMBER() OVER (PARTITION BY SenderId ORDER BY ScheduleDate) AS RowNo,
                                           MaskId
                                    FROM MessageIn msg
                                         LEFT JOIN @PriorityIn o ON msg.PriorityId = o.PriorityId
                                    WHERE ((msg.ProviderId IS NULL
                                        AND o.PriorityId IS NOT NULL)
                                        OR msg.ProviderId = @GatewayId)) res
                              WHERE res.RowNo <= @NoOfRow));

            DELETE msgin
            FROM MessageIn msgin
                 INNER JOIN #SmsIn temp ON msgin.MaskId = temp.MaskId;
        END;
        SELECT *
        FROM #SmsIn;
        DROP TABLE #SmsIn;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;
    END CATCH;

Execution plan is available in Here: Execution Plan

Updated Query:

BEGIN TRY
begin tran;
CREATE TABLE #tmpMaskId (MaskId varchar(25) PRIMARY KEY)

INSERT INTO #tmpMaskId(MaskId)
SELECT DISTINCT MaskId From 
(SELECT  ROW_NUMBER() OVER ( PARTITION BY SenderId ORDER BY scheduledate ) AS RowNo, MaskId FROM MessageIn msg 
LEFT JOIN PriorityProviders o on o.ProviderId = @GatewayId AND o.Type = 0 and msg.PriorityId = o.PriorityId
WHERE  
((msg.ProviderId is null AND o.PriorityId is not null) OR msg.ProviderId = @GatewayId)
)as res WHERE res.RowNo <= @NoOfRow

Select [Id],[UserId],m.[MaskId],[Number],[Message],[SenderId],[UDH],[Credit],[CurrentStatus],[CheckDND],[CheckFail],[CheckBlackList],[ProviderId]
      ,[PriorityId],[ScheduleDate],[CreatedDate],[EsmClass],[DataCoding],[Priority],[Interface]
From MessageIn m inner join #tmpMaskId msk on m.MaskId = msk.MaskId

DELETE msgin
FROM MessageIn msgin
INNER JOIN #tmpMaskId temp ON msgin.MaskId=temp.MaskId

DROP TABLE #tmpMaskId
Commit;
END TRY
BEGIN CATCH
     IF @@TRANCOUNT > 0
     BEGIN
        ROLLBACK TRANSACTION;
     END
END CATCH;

Upvotes: 0

Answers (2)

KumarHarsh

Reputation: 5094

IMO, As per your requirement, I will only return record from this proc to send sms.After sms is successfully I send only require id from Message table to another proc to delete those records.

Technically it sound good.your existing proc is not slow because of delete.but its not ok to delete before sending sms and again trying to insert.

In my previous scipt,I pointed that you do not need Join on PriorityProviders.

I have revise my script(INNER if possible),

    SET NOCOUNT ON
    BEGIN TRY
    begin tran;

    CREATE TABLE #tmpMaskId (MaskId varchar(25) not null)

    INSERT INTO #tmpMaskId(MaskId)
    SELECT MaskId From 
    (SELECT  ROW_NUMBER() OVER ( PARTITION BY SenderId ORDER BY scheduledate ) AS RowNo, MaskId FROM MessageIn msg with(nolock)
    LEFT JOIN PriorityProviders with(nolock)
  o on o.ProviderId = msg.ProviderId and o.ProviderId= @GatewayId AND o.Type = 0 and msg.PriorityId = o.PriorityId

    WHERE  
    ((msg.ProviderId is null AND o.PriorityId is not null) OR msg.ProviderId = @GatewayId)
    )as res WHERE res.RowNo <= @NoOfRow

    CREATE TABLE #tmpMaskId (MaskId INT not null)
    create clusetered index ix_mask on #tmpMaskId


    Select [Id],[UserId],m.[MaskId],[Number],[Message],[SenderId],[UDH],[Credit],[CurrentStatus]
    ,[CheckDND],[CheckFail],[CheckBlackList],[ProviderId]
          ,[PriorityId],[ScheduleDate],[CreatedDate],[EsmClass],[DataCoding],[Priority],[Interface]
    From MessageIn m 
    inner join 
    #tmpMaskId msk 
    on m.MaskId = msk.MaskId

    DELETE msgin
    FROM MessageIn msgin
    where exists(select 1 from #tmpMaskId temp where msgin.MaskId=temp.MaskId)

    DROP TABLE #tmpMaskId

    Commit;
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0
         BEGIN
            ROLLBACK TRANSACTION;
         END
    END CATCH;

notice how I hv remove PK from Temp table and made it clustered Index. How I hv remove distinct ?

Now main culprit is this statement,

ROW_NUMBER() OVER ( PARTITION BY SenderId ORDER BY scheduledate ) AS RowNo

I think once if you comment it then proc will perform better.

Now you need only index.

Whichever column is most Selective make that column as Clustered Index.

Since I do not know selectivity of each column I can't say whether you should make composite clustered or composite Non clustered index.

If you go for Composite Non Clustered index then make ID as clustered index(PK) and keep the most selective column on left side and so on

Composite Non Clustered index can be (maskid,ProviderId,SenderId,PriorityId)Include(other columns of message table which are require in Resultset)

I am not telling you to remove Row_number().Create composite non clustered index and rebuild index as I have describe above.

With (nolock) :It has nothing to do with data duplicity. If there is no chance of getting uncommitted data. If there is not much concurrency issue in message table and is not very frequently insert/updated. Then you can safely use it.you can Google this "Advantage and disadvantage of with (Nolock)". In one or two places you can use it if it improve your important query.

Like you said if you create index on maskid then it create deadlock.That is because of faulty script in Insert.

Upvotes: 1

Deep patel

Reputation: 136

ALTER PROCEDURE [dbo].[GetNextSmsQueue]
@NoOfRow    INT
,@GatewayId INT
AS
BEGIN TRY
BEGIN TRAN;

CREATE TABLE #tmpMaskId (MaskId INT PRIMARY KEY)

DECLARE @PriorityIn TABLE ([PriorityId] [INT] NOT NULL)

INSERT INTO @PriorityIn
SELECT PriorityId
FROM PriorityProviders
WHERE ProviderId=@GatewayId AND Type=0

INSERT INTO #tmpMaskId (MaskId)
SELECT DISTINCT MaskId
FROM (
     SELECT ROW_NUMBER() OVER (PARTITION BY SenderId ORDER BY ScheduleDate) AS RowNo
         ,MaskId
     FROM MessageIn msg
     WHERE ((msg.ProviderId IS NULL AND o.PriorityId IS NOT NULL) OR msg.ProviderId=@GatewayId)
     ) res
WHERE res.RowNo<=@NoOfRow

SELECT [Id]
    ,[UserId]
    ,[MaskId]
    ,[Number]
    ,[Message]
    ,[SenderId]
    ,[UDH]
    ,[Credit]
    ,[CurrentStatus]
    ,[CheckDND]
    ,[CheckFail]
    ,[CheckBlackList]
    ,[ProviderId]
    ,[PriorityId]
    ,[ScheduleDate]
    ,[CreatedDate]
    ,[EsmClass]
    ,[DataCoding]
    ,[Priority]
    ,[Interface]
FROM MessageIn mi
WHERE EXISTS (SELECT 1 FROM #tmpMaskId AS tmi WHERE tmi.MaskId=mi.MaskId)

DELETE msgin
FROM MessageIn msgin
INNER JOIN #tmpMaskId temp ON msgin.MaskId=temp.MaskId

COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 
BEGIN
         ROLLBACK TRANSACTION;
END;
END CATCH;

DROP TABLE #tmpMaskId

Upvotes: 1

SQL Query Optimization with million of records

Answers (2)

Related Questions