why update statement take too much time ?

  • I working on SQL server 2012 I face issue update statement take too much time

    and it is very slow

    my query is

    update t set t.Revision_ID = r.rec_id 
    FROM extractreports.dbo.TempROHS t
    INNER join Z2urlSystem.zsrc.Local_URL l (NOLOCK) ON isnull(t.ZURLSource1,'N/A') = l.local_url
    INNER join Z2urlSystem.zsrc.Online_URL o (NOLOCK) ON ISNULL(t.OnlineSource1,'N/A') = o.url
    INNER join Z2urlSystem.zsrc.Revision r (NOLOCK) ON l.rec_id = r.local_id AND o.Rec_ID = r.online_id
    WHERE t.[Status] IS NULL

     

    and execution plan as below :

    https://www.brentozar.com/pastetheplan/?id=rJfvx1mSK

     

    Table [zsrc].[Local_URL]

    CREATE TABLE [zsrc].[Local_URL](
    [rec_id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [local_url] [nvarchar](2048) NOT NULL,
    CONSTRAINT [Key_Rec_ID] PRIMARY KEY NONCLUSTERED
    (
    [rec_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    Table [zsrc].[Online_URL]

    CREATE TABLE [zsrc].[Online_URL](
    [Rec_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [nvarchar](500) NOT NULL,
    CONSTRAINT [PK_Rec_ID_oL] PRIMARY KEY CLUSTERED
    (
    [Rec_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    Table Revision

    CREATE TABLE [zsrc].[Revision](
    [rec_id] [bigint] NOT NULL,
    [online_id] [bigint] NOT NULL,
    [local_id] [bigint] NOT NULL,
    CONSTRAINT [PK_Rec_ID] PRIMARY KEY CLUSTERED
    (
    [rec_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    Table extractreports.dbo.TempROHS

    CREATE TABLE extractreports.dbo.TempROHS
    (
    ID INT IDENTITY ,
    ZURLSource1 NVARCHAR(3000) ,
    OnlineSource1 NVARCHAR(3000) ,
    Revision_ID BIGINT ,
    [status] NVARCHAR(300)
    )

    sample data extractreports.dbo.TempROHS

    ID ZURLSource1 OnlineSource1 Revision_ID STATUS
    14254 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N1.pdf N/A NULL NULL
    14255 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N2.pdf N/A NULL NULL
    14256 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N3.pdf N/A NULL NULL
    14257 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N4.pdf N/A NULL NULL
    14258 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N5.pdf N/A NULL NULL
    14259 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N6.pdf N/A NULL NULL
    14260 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NA.pdf N/A NULL NULL
    14261 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NB.pdf N/A NULL NULL
    14262 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NC.pdf N/A NULL NULL
    14263 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405ND.pdf N/A NULL NULL
  • looking at it, to me it appears that it is slow because you are looking at a LOT of data to review and then update a single row.  Mind you that is all ESTIMATED, not actual.

    It appears you have no FK constraints set up (guessing here based on what you posted), but those function calls on your JOINs are really hurting performance as you are turning that into a row based operation which you can see on the execution plan:

    Estimated Number of Executions

    108576

    Estimated Number of Rows

    1

    it is running that one at a time for a total of 108576 for one of the 2 tables where you are using functions in the join.  The other tables in the JOIN appear to be suspicious too as they are only estimating 1-2 rows coming back.

    My GUESS is that SQL is guessing wrong and coming up with bad estimates which is resulting in tempdb spill and possibly other issues causing slowness.  Could be blocking as well causing the slowness.  I would try your query on a test system and let it run to completion (capturing the actual execution plan) so you can see:

    A - the actual plan

    B - the actual execution time on a mostly unused system

    Something that MAY help (may not) is pulling the data out of the tables into temp tables or table variables rather than relying on NOLOCK which can give bad results (missing or duplicate).  NOLOCK MAY be the appropriate answer to your specific use case and I am not recommending removing it, just might want to evaluate other options.

    You also appear to be using cross-database queries which I've seen cause performance issues.  You may benefit from having all of the data on the same database.  What I mean is you could clone the tables from Z2urlSystem to extractreports that you need to do that update on and then use the tables in extractreports and remove the cross database queries.

    A statistics update may help as well.

    Just some things you can try;  I would test some things on a test/dev environment and move to live once you have determined why it is slow and corrected the issue.  I would start by getting it running on test so you can see an actual execution plan even if it means waiting 10 hours for it to complete.  At least then you can see the estimated vs actual execution plans to determine if statistics is an issue which is a "quick win" in most cases.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • so can you help me on code

    and tell me what statement update should be to run faster

  • you didn't supply all the indexes of the tables - table revision has another index (IDX_Online_Id)

    just as a glance I would suggest trying the following changes

    Table revision - Index IDX_Online_ID - add column local_id as an include column - would remove the need for a KEY lookup

    table local_url - index Key_rec_ID - convert to clustered index (its non clustered at the moment and table is a HEAP) - would remove the need for a RID lookup

     

  • i make all indexes and still issue exist

    can we rewrite these statement or what

  • Remove all the ISNULL with some other construct.

     

    ISNULL is not SARGable and will cause RBAR which is what your seeing.

  • thank you for reply

    can you write these statement below by joining using id

    without join by url text

    are there are any way to do that

    update t set t.Revision_ID = r.rec_id

    FROM extractreports.dbo.TempROHS t

    INNER join Z2urlSystem.zsrc.Local_URL l (NOLOCK) ON t.ZURLSource1 = l.local_url

    INNER join Z2urlSystem.zsrc.Online_URL o (NOLOCK) ON t.OnlineSource1 = o.url

    INNER join Z2urlSystem.zsrc.Revision r (NOLOCK) ON l.rec_id = r.local_id AND o.Rec_ID = r.online_id

    WHERE t.[Status] IS NULL

  • If that works for you great.

    If not look at coalesce instead of isnull or some other construct.

    You know your own data better than anyone here so you will need to verify any changes in a dev/test environment.

Viewing 8 posts - 1 through 8 (of 8 total)

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