Validation queries running too long

  • I have a table that contains approx 200 thousand records that I need to run validations on. Here's my stored proc:

    CREATE

    PROCEDURE [dbo].[uspValidateLoadLeads]

    @sQuotes

    char(1) = null, @sProjectId varchar(10) = null, @sErrorText varchar(1000) out

    AS                                                                                                                                                                                     BEGIN

    DECLARE @ProcName sysname, @Error int, @rc int, @lErrorCode bigint, DECLARE @sql varchar(8000)

    IF @sQuotes = '0'

    BEGIN

    UPDATE dbo.prProjectDiallingList_staging

    SET sPhone = RTrim(LTrim(Convert(varchar(30), Convert(numeric(20, 1), phone))))

    END

    ELSE

    BEGIN

    UPDATE dbo.prProjectDiallingList_staging

    SET sPhone = phone

    END

    --2. Remove quotes

    UPDATE dbo.prProjectDiallingList_staging

    SET sphone = REPLACE(sphone,'"' , '')

    --3. Remove decimal, comma, dashes, parenthesis

    UPDATE dbo.prProjectDiallingList_staging

    SET sphone = replace(replace(replace(replace(replace(replace(sphone,'.',''),',','' ),'-',''), ' ',''), '(', ''), ')', '')

    --4. Update failed Validation column if not 10 digits

    UPDATE dbo.prProjectDiallingList_staging

    SET sFailedValidation = 'X'

    WHERE(Len(RTrim(LTrim(sPhone))) <> 10)

    --5. Dedup

    UPDATE a

    SET a.sFailedValidation = 'X'

    FROM dbo.prProjectDiallingList_staging a (nolock)

    INNER JOIN dbo.prProjectDiallingList_staging b

    ON a.sPhone= b.sPhone

    WHERE(a.iList_StagingID > b.iList_StagingID)

    --6. Update failed Validation column if not numeric

    UPDATE dbo.prProjectDiallingList_staging

    SET sFailedValidation = 'X'

    WHERE(IsNumeric(RTrim(LTrim(sPhone))) = 0)

    --7. Update time zones

    UPDATE s

    SET s.sTimeZone =z.sTimeZone

    FROM dbo.prProjectDiallingList_staging s (nolock)

    LEFT OUTER JOIN dbo.prPhoneTimeZone z

    ON left(rtrim(ltrim(s.sphone)),3) = z.sPhoneAreaCode

    --8. Insert into dialing table only records that have not failed the validation

    INSERT dbo.prProjectDiallingList(iPrProjectId, sPhoneNumber, sTimeZone)

    SELECT @sProjectId,sPhone, sTimeZone

    FROM dbo.prProjectDiallingList_staging

    WHERE ISNULL(sFailedValidation,'1') = '1'

    UPDATE d

    SET d.bProcessReporting = 1

    FROM dbo.prProjectDialling d

    WHERE d.iPrProjectId = @sProjectId

    END

    When I execute this stored proc it runs for more than 5 minutes. Is there anything i can do to speed it up? Maybe there is a faster way of writing these queries?

    Thanks,

    Ninel

  • What I usually do when hit with a problem like this is to first work out which bit of the procedure is taking the most time.  I do this by putting print statements before and after each insert or update e.g.

    Print 'UPDATE dbo.prProjectDiallingList_staging ' +  convert(varchar,GetDate(),108)  -- start time

    Once you have isolated the part of the procedure that is causing the problem, look at the indexes on the tables to see if that will help.

     

     

  • One of the things I see is that you do a validation on all the phones every time. Since you are going to use only the ones associated with the project you can eliminate others. And you could combine some of your steps.

    I don't know all the tables but I modified your procedure. Here is how it should be.

    CREATE PROCEDURE [dbo].[uspValidateLoadLeads]

     @sQuotes char(1) = null,

     @sProjectId varchar(10) = null,

     @sErrorText varchar(1000) out

    AS                                                                                                                                                                                     BEGIN

     DECLARE @ProcName sysname, @Error int, @rc int, @lErrorCode bigint, DECLARE @sql varchar(8000)

     IF @sQuotes = '0'

     BEGIN

      UPDATE dbo.prProjectDiallingList_staging

       SET sPhone = RTrim(LTrim(Convert(varchar(30), Convert(numeric(20, 1), phone))))

      where iPrProjectId = @sProjectId

     END

     --2. Remove quotes and Remove decimal, comma, dashes, parenthesis

     UPDATE dbo.prProjectDiallingList_staging

       SET sphone = replace(replace(replace(replace(replace(replace(REPLACE(sphone,'"' , ''),'.',''),',','' ),'-',''), ' ',''), '(', ''), ')', '')

     where iPrProjectId = @sProjectId

     --5. Dedup

     

     UPDATE a

     

     SET a.sFailedValidation = 'X'

     

     FROM dbo.prProjectDiallingList_staging a (nolock)

     

     INNER JOIN dbo.prProjectDiallingList_staging b

     

     ON a.sPhone= b.sPhone

     

     WHERE(a.iList_StagingID > b.iList_StagingID)

     and  a.iPrProjectId = @sProjectId

     --6. Update failed Validation column if not numeric

     

     UPDATE dbo.prProjectDiallingList_staging

     

     SET sFailedValidation = 'X'

     

     WHERE(IsNumeric(RTrim(LTrim(sPhone))) = 0) or (Len(RTrim(LTrim(sPhone))) <> 10)

     and iPrProjectId = @sProjectId

     --7. Update time zones

     

     UPDATE s

     

     SET s.sTimeZone =z.sTimeZone

     

     FROM dbo.prProjectDiallingList_staging s (nolock)

     

     LEFT OUTER JOIN dbo.prPhoneTimeZone z

     

     ON left(rtrim(ltrim(s.sphone)),3) = z.sPhoneAreaCode

     where  s.iPrProjectId = @sProjectId

     --8. Insert into dialing table only records that have not failed the validation

     

     INSERT dbo.prProjectDiallingList(iPrProjectId, sPhoneNumber, sTimeZone)

     

     SELECT @sProjectId,sPhone, sTimeZone

     

     FROM dbo.prProjectDiallingList_staging

     

     WHERE ISNULL(sFailedValidation,'1') = '1'

     and iPrProjectId = @sProjectId

     UPDATE d

     

     SET d.bProcessReporting = 1

     

     FROM dbo.prProjectDialling d

     

     WHERE d.iPrProjectId = @sProjectId

    END

    Hope this helps.

    Thanks

    Sreejith

     

  • The projectId doesn't matter because this is a staging table all of the records have the same projectid.

  • How many rows are there in the table. Can you create an Index on ProjectID cos you use it so many places?

    Thanks

    Sreejith

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply