Optimizing T-SQL Insert -- temptable, CTEs, WHILE loops_问答_开发者

I have a situation where I need to process and finally insert 1000s of records from a temptable into a database table. Before each insert, I need to ensure that conditions are met and right after each insert, I need to update a second table in my database. My problem is that currently, it takes approximately 25 minutes to run the query and I would like to drastically cut that time so my application can be more responsive. How can I go about doing this please?

DECLARE @rowcounter as INTEGER 


                    CREATE TABLE #temporary_phonetable 
                    (
                          rownumber int  not null  identity(1,1), 
                          record_no BIGINT,
                          phone_name BIGINT, 
                          phone_number Varchar(25)  not null  ,
                           responsemessage Varchar(200)  not null  ,
                          messagepriority Varchar(14)  not null  , 
                          phone_id BIGINT, 
                          AD_show  BIGINT, 
                          power_show Varchar(400), 
                          service_provider VARCHAR(30),
                          Phone_flag  VARCHAR(30),
                          questionMessage BIGINT,
                          PRIMARY KEY (phone_id, phone_number, rownumber)
                        ,UNIQUE (questionMessage, record_no, rownumber) 
                    )
                    --GET PHONE DATA 
                                --if phone numbers are sent in from the client, then we want to process those instead 
            IF ( ( ( @listofphones IS NULL OR LEN(@listofphones) <1) AND LEN(@peoplegroups) >0)  )
             BEGIN 
                   --NO PHONENUMBER BUT THERE ARE GROUPS AVAILABLE                        
                              INSERT INTO #temporary_phonetable(phone_name, phone_number, messagepriority, phone_id, AD_show, power_show, responsemessage) 
                              SELECT n.phone_name, n.phone_number,u.messagepriority, n.phone_id , u.AD_show, u.power_show , CASE  @includegreetings   WHEN 1 THEN LTRIM(RTRIM(phone_name)) + @responsemessages 
                                      ELSE @responsemessages END as text_message
                              FROM user u WITH(NOLOCK) 
                              INNER JOIN Phonenumbers n  WITH(NOLOCK) ON n.user_no = u.user_no
                                INNER JOIN PeopleGroupRelations g ON  g.phone_id=n.phone_id  
                                INNER JOIN ( Select items FROM Split(@peoplegroups, @listofphonesdelimiter)) gg ON g.group_no = gg.items 
                              WHERE n.user_no=@userid 
                              AND n.status=''active''
                              SET @rowcounter = @@ROWCOUNT
             END 
             ELSE  IF ( LEN(@listofphones) >1  AND LEN(@peoplegroups) >0)
              BEGIN  
                      --PHONENUMBER AND GROUPS 
                              INSERT INTO #temporary_phonetable(phone_name, phone_number, messagepriority, phone_id, AD_show, power_show, responsemessage) 
                              SELECT n.phone_name, n.phone_number,u.messagepriority, n.phone_id , u.AD_show, u.power_show , CASE  @includegreetings   WHEN 1 THEN LTRIM(RTRIM(phone_name)) + @responsemessages 
                                      ELSE @responsemessages END as text_message
                              FROM  Split(@listofphones, ''|'')  s
                              INNER JOIN PhoneNumbers n  WITH(NOLOCK) ON n.phone_number = s.items
                              INNER JOIN User u WITH(NOLOCK)  ON n.user_no =u.user_no
                              INNER JOIN PeoplegroupRelations g ON  g.phone_id=n.phone_id  
                              INNER JOIN ( Select items FROM Split(@peoplegroups, @listofphonesdelimiter)) gg ON g.gr开发者_如何学运维oup_no = gg.items 
                              WHERE n.user_no=@userid 
                              AND n.status=''active''
                              SET @rowcounter = @@ROWCOUNT
             END 
              ELSE  IF ( LEN(@listofphones) >1  AND LEN(@peoplegroups) >0)
              BEGIN  
                      --PHONENUMBER AND NO GROUPS 
                              INSERT INTO #temporary_phonetable(phone_name, phone_number, messagepriority, phone_id, AD_show, power_show, responsemessage) 
                              SELECT n.phone_name, n.phone_number,u.messagepriority, n.phone_id , u.AD_show, u.power_show , CASE  @includegreetings   WHEN 1 THEN LTRIM(RTRIM(phone_name)) + @responsemessages 
                                      ELSE @responsemessages END as text_message
                              FROM  Split(@listofphones, ''|'')  s
                              INNER JOIN PhoneNumbers n  WITH(NOLOCK) ON n.phone_number = s.items
                              INNER JOIN User u WITH(NOLOCK)  ON n.user_no =u.user_no
                              INNER JOIN PeopleGroupRelations g ON  g.phone_id=n.phone_id  
                              INNER JOIN ( Select items FROM Split(@peoplegroups, @listofphonesdelimiter)) gg ON g.group_no = gg.items 
                              WHERE n.user_no=@userid 
                              AND n.status=''active''
                              SET @rowcounter = @@ROWCOUNT
             END  
             ELSE 
                    BEGIN 
                          -- NO PHONENUMBER NO GROUP --- IE. SEND TO ALL PHONE NUMBERS
                                 INSERT INTO #temporary_phonetable(phone_name, phone_number, messagepriority, phone_id, AD_show, power_show,responsemessage) 
                                SELECT   n.phone_name, n.phone_number,u.messagepriority, n.phone_id , u.AD_show, u.power_show , CASE  @includegreetings   WHEN 1 THEN LTRIM(RTRIM(phone_name)) + @responsemessages 
                                      ELSE @responsemessages END as text_message
                                FROM User u  
                                          INNER JOIN PhoneNumbers n ON n.user_no = u.user_no
                                WHERE
                                        n.status=''active''
                                        AND  n.user_no=@userid 
                                    SET @rowcounter = @@ROWCOUNT 
                    END



                  IF( @rowcounter>0)
                  BEGIN 
                                    DECLARE @service_provider as Varchar(30)
                                    DECLARE @PhoneType as Varchar(30)

                                    IF (LOWER(RTRIM(LTRIM(@sendresponseswhen))) ='now')
                                    BEGIN 
                                                SET @dateresponsessent = GETDATE()
                                     END

                                                      DECLARE @rownumber int
                                                      DECLARE @power_show BIT
                                                      DECLARE  @AD_show BIT 
                                                      set @rownumber = 0 
                                                      WHILE @rownumber < @rowcounter
                                                      BEGIN
                                                                    set @rownumber = @rownumber + 1
                                                                    -- THE VARIABLES 
                                                                                DECLARE @record_no as BIGINT
                                                                                DECLARE @phone_name VARCHAR(30)
                                                                                DECLARE @messagepriority as INTEGER
                                                                                DECLARE @phone_number VARCHAR(30)
                                                                                DECLARE @phone_id BIGINT
                                                                                DECLARE @questionMessage BIGINT

                                                                     SELECT 
                                                                              @phone_name = n.phone_name, @phone_number =n.phone_number, @messagepriority =n.messagepriority, @phone_id=n.phone_id , 
                                                                              @AD_show=n.AD_show, @power_show=n.power_show
                                                                        FROM 
                                                                              #temporary_phonetable n WITH(NOLOCK) 
                                                                        WHERE n.rownumber = @rownumber

                                                                        SET @record_no = AddMessageToQueue(@phone_number, @responsemessages, @dateresponsessent, @savednames, @userid, un.messagepriority, @responsetype, 
                                                                        un.AD_show, un.power_show, @service_provider, @PhoneType)  


                                                                        If(@questionid > 0)
                                                                        BEGIN 
                                                                                SET @questionMessage = AddQuestionMessage(@questionid,@phone_id,  @record_no, DATEADD(d, 30, GETDATE()) )
                                                                        END 


                                                UPDATE #temporary_phonetable SET record_no = @record_no, questionMessage=@questionMessage WHERE phone_number = @phone_number  AND rownumber = @rownumber
                                    END 
                                    IF( @power_show >0)
                                    BEGIN 
                                          SET @responsemessages = @responsemessages + dbo.returnPoweredBy()
                                    END
                                    IF( @AD_show > 0)
                                    BEGIN 
                                          SELECT @responsemessages = @responsemessages + CASE 
                                                                                                            WHEN (LEN(@responsemessages)  + 14)<  160 THEN    dbo.returnAD(@responsemessages) 
                                                                                                            ELSE '''' END 
                                    END


                                    RETURN @rowcounter
                        END

I believe this is the place where the bulk of the issue resides.

WHILE @rownumber < @rowcounter BEGIN set @rownumber = @rownumber + 1 -- THE VARIABLES DECLARE @record_no as BIGINT DECLARE @phone_name VARCHAR(30) DECLARE @messagepriority as INTEGER DECLARE @phone_number VARCHAR(30) DECLARE @phone_id BIGINT DECLARE @questionMessage BIGINT

                                                                 SELECT 
                                                                          @phone_name = n.phone_name, @phone_number =n.phone_number, @messagepriority =n.messagepriority, @phone_id=n.phone_id , 
                                                                          @AD_show=n.AD_show, @power_show=n.power_show
                                                                    FROM 
                                                                          #temporary_phonetable n WITH(NOLOCK) 
                                                                    WHERE n.rownumber = @rownumber

                                                                    SET @record_no = AddMessageToQueue(@phone_number, @responsemessages, @dateresponsessent, @savednames, @userid, un.messagepriority, @responsetype, 
                                                                    un.AD_show, un.power_show, @service_provider, @PhoneType)  


                                                                    If(@questionid > 0)
                                                                    BEGIN 
                                                                            SET @questionMessage = AddQuestionMessage(@questionid,@phone_id,  @record_no, DATEADD(d, 30, GETDATE()) )
                                                                    END 


                                            UPDATE #temporary_phonetable SET record_no = @record_no, questionMessage=@questionMessage WHERE phone_number = @phone_number  AND rownumber = @rownumber
                                END

Add a unique constraint to rownumber in your temp table. Rewrite the WHILE as a CTE. Use APPLY to call the functions.

You also might want to consider using a table variable rather than a temporary table. You won't be writing to the tempdb and as table variables are created in memory they're faster.

This article has a nice comparison.