Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Question Unanswered: Why does this simple update take so long?

    I was doing a very simple update of one column in A with data from table B. Table B has 1000 records. Table A has maybe 50,000. I ran the following update and it ran for over 20 minutes, where I cancelled it. Server is 2005 w/ 4 GB of ram
    Code:
        UPDATE p 
           SET p.pe = p1.pe
          FROM people p
    INNER JOIN tmp_people p1
            ON p.record_id = p1.record_id
    There are 493 records where p.pe <> p1.pe, so I figured adding that line in would whittle the size down and make it quicker, but same thing... 20 minutes, then I cancelled.
    Code:
        UPDATE p 
           SET p.pe = p1.pe
          FROM people p
    INNER JOIN tmp_people p1
            ON p.record_id = p1.record_id
         WHERE p.pe <> p1.pe
    Now, table p1 has maybe 10 different values for p1.pe. So I do following, which updates 103 records, the update is done in 4 seconds:
    Code:
        UPDATE p 
           SET p.pe = p1.pe
          FROM people p
    INNER JOIN tmp_people p1
            ON p.record_id = p1.record_id
         WHERE p.pe <> p1.pe
           AND p1.pe = '4010'
    Now, I've solved my simple problem by just spliting my update between the different 'pe's, but I'm hoping someone can teach me to fish with a net instead of 5 fishing poles.

    1) Why the huge difference in time between the two queries when there's only a small difference in the number of rows? (4x the rows, +300x execute time)
    2) Is there a better way to do this?
    3) If I had 1000 different pe values from say 4000 to 5000, what would be the best way to set up an iterative query for this? (if that's even a good way to tackle it)

    Thank you,
    Dean

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are there any duplications in record_id of people and record_id of tmp_people?

    For example, what results did you got from these two queries?
    SELECT record_id , COUNT(*) FROM people GROUP BY record_id HAVING COUNT(*) > 1
    SELECT record_id , COUNT(*) FROM tmp_people GROUP BY record_id HAVING COUNT(*) > 1

    If nothing was returned, my guess would be off the point.
    Last edited by tonkuma; 04-13-12 at 02:08.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Some general things to check.

    How long does the SELECT version of the UPDATE take? Also 20 minutes?
    Code:
        SELECT p.record_id, p.pe, p1.pe
          FROM people p
    INNER JOIN tmp_people p1
            ON p.record_id = p1.record_id
    Are there indexes defined on p.record_id and p1.record_id? On p.pe?
    Are there any (foreign key) constraints defined on p.pe?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is there a trigger on the table?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2012
    Posts
    7
    Quote Originally Posted by tonkuma View Post
    Are there any duplications in record_id of people and record_id of tmp_people?

    For example, what results did you got from these two queries?
    SELECT record_id , COUNT(*) FROM people GROUP BY record_id HAVING COUNT(*) > 1
    SELECT record_id , COUNT(*) FROM tmp_people GROUP BY record_id HAVING COUNT(*) > 1

    If nothing was returned, my guess would be off the point.
    record_id is the PK for both tables, sorry for omitting this detail.

    Quote Originally Posted by Wim
    How long does the SELECT version of the UPDATE take? Also 20 minutes?
    < 0 sec

    Quote Originally Posted by Wim
    Are there indexes defined on p.record_id and p1.record_id? On p.pe?
    Are there any (foreign key) constraints defined on p.pe?
    Quote Originally Posted by blindman
    Is there a trigger on the table?
    record_id being a primary key, is indexed. p.pe is most likely indexed and also likely has FK constraints; There are triggers on p. If it is material to the problem, I will be able to double check when I have access to the db again on Tuesday. However, if any of these were the cause of the slow update, would they not also slow down the each of the subsets of updates I ran?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess would be some flavor of Cartesian join within the trigger.

    Cartesian joins often perform on the order of (x ** y) or (n * Log(n)), so going Cartesian on a small set of rows is much less painful than going Cartesian on a larger set of rows.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post
    Quote Originally Posted by blindman
    Is there a trigger on the table?
    There are triggers on p.
    Bingo. Post the code for the triggers.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2012
    Posts
    7

    Here are the triggers

    Quote Originally Posted by blindman View Post
    Bingo. Post the code for the triggers.
    There are three triggers, the most likely candidate follows. One doesn't refer to PE, and the other is fairly simple (I will post the simple one in a followup post, due to text limitations). dba.TB_CORE_GENERAL_PROGRAM is what has previously been referred to as the table 'people'. Sorry for taking so long to reply here, I haven't had the combination of time and access line up until now.

    TB_CORE_GENERAL_PROGRAM_TU01
    Code:
    USE [SBCEHSEC_Test]
    GO
    /****** Object:  Trigger [dba].[TB_CORE_GENERAL_PROGRAM_TU01]    Script Date: 04/18/2012 10:58:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER trigger [dba].[TB_CORE_GENERAL_PROGRAM_TU01] on [dba].[TB_CORE_GENERAL_PROGRAM] for update
    --not for replication
    as
    /*
    General notes:
      Do not remove --not for replication line from the top of this script,
      it may be used later
    
      Inserts or updates a scheduled invoice item record.
    
      When doing any maintenance on this trigger, please also see:
        TB_CORE_GENERAL_PROGRAM_TU01
        TB_SEPTIC_ONSITE_SYSTEM_TU01
        TB_UG_TANK_TU01
        TB_WATER_SYSTEM_TU01
    
    To Do:
      All the above trigges share almost the same code, but I didnt  move it to a
      separate stored procedure to avoid using a cursor inside a trigger. Probably
      the best place for this code - DBGenerator.
    
    Change Log:
    Name    Date      Changes
    --------------------------------------------------------------------------------
    Old history:
    JLE     10/26/01  Added PE to the where clause to avoid the key violations
                      arising when an update was performed on an existing Water
                      System Inventory record.  See defect 5371 and 5905.
    New history:
    LB      07/03/03  ##S2M Converted from the TAU_CORE_GENERAL_PROGRAM,
                      TAU_CORE_GENERAL_PROGRAM01, TAU_CORE_GENERAL_PROGRAM05
    LB      07/12/03  Removed error handling as unnecessary inside the trigger and
                      added PE to the where clause where it was missed during conversion
    LB      07/13/03  Added check for updating CURRENT_STATUS, FACILITY_ID, PE, UNITS
                      and DISCOUNT_CODE before updating scheduled invoice items
    ES      05/05/04  Suggestion #7420. Added logic to handle STATUS_CHANGED_DATE.
    MVEA		06/30/09	PBI #6399. Removed the code that deletes the SII and added code
    									to update CONTRIBUTOR.
    */
    begin
      --if we are inside the MSSQL replication OR client application does not use triggers - just return
      if ((dba.get_is_replication_agent() = 1) and (dba.get_servertype() = 'MSSQL')) OR (dba.get_disable_triggers_option() = 1)
        return
    
      -- Check for updating ENTERED_DATE field. It is updated only from the insert trigger,
      -- we dont want to fire update trigger in this special case
      -- Also check for the records in inserted table
      if UPDATE(ENTERED_DATE) or not exists(select * from inserted)
        return
    
      if UPDATE(CURRENT_STATUS) or UPDATE(FACILITY_ID) or UPDATE(PE) or UPDATE(UNITS) or UPDATE(DISCOUNT_CODE) or UPDATE(CONTRIBUTOR)
      begin
        --Insert new records when record doesnt exist
        --in TB_FIN_SCHEDULED_INVOICE_ITEM
        insert into dba.TB_FIN_SCHEDULED_INVOICE_ITEM(
          RECORD_ID, FACILITY_ID, PE, AID, NUM_UNITS, DISCOUNT_CODE, CONTRIBUTOR)
        select
          N.RECORD_ID, N.FACILITY_ID, N.PE, 'PR', N.UNITS, N.DISCOUNT_CODE, N.CONTRIBUTOR
        from
          inserted N, deleted O
        where
          N.RECORD_ID = O.RECORD_ID and
          N.CURRENT_STATUS = '01' and
          not exists(select * from dba.TB_FIN_SCHEDULED_INVOICE_ITEM T where T.RECORD_ID = N.RECORD_ID)
    
        --Update existing records when BILLING_STATUS has not changed
        update
          dba.TB_FIN_SCHEDULED_INVOICE_ITEM
        set
          dba.TB_FIN_SCHEDULED_INVOICE_ITEM.FACILITY_ID = N.FACILITY_ID,
          dba.TB_FIN_SCHEDULED_INVOICE_ITEM.PE = N.PE,
          dba.TB_FIN_SCHEDULED_INVOICE_ITEM.NUM_UNITS = N.UNITS,
          dba.TB_FIN_SCHEDULED_INVOICE_ITEM.DISCOUNT_CODE = N.DISCOUNT_CODE,
    			dba.TB_FIN_SCHEDULED_INVOICE_ITEM.CONTRIBUTOR = N.CONTRIBUTOR
        from
          dba.TB_FIN_SCHEDULED_INVOICE_ITEM, inserted N, deleted O
        where
          N.RECORD_ID = O.RECORD_ID and
          dba.TB_FIN_SCHEDULED_INVOICE_ITEM.RECORD_ID = O.RECORD_ID and
          dba.TB_FIN_SCHEDULED_INVOICE_ITEM.PE = O.PE
        --END From the old TAU_CORE_GENERAL_PROGRAM01 trigger
      end
    
      --From the old TAU_CORE_GENERAL_PROGRAM trigger
      if UPDATE(PE)
      begin
        update
          DBA.TB_CORE_PERMIT
        set
          DBA.TB_CORE_PERMIT.PE = n.PE
        from
          DBA.TB_CORE_PERMIT, inserted N
        where
          DBA.TB_CORE_PERMIT.RELATED_ID = n.RECORD_ID
      end
    
      --From the old TAU_CORE_GENERAL_PROGRAM05 trigger
      update
        DBA.TB_COMPLAINT_LOG
      set
        DBA.TB_COMPLAINT_LOG.RELATED_ID = null
      from
        DBA.TB_COMPLAINT_LOG, inserted N, deleted O
      where
        N.RECORD_ID = O.RECORD_ID and
        ISNULL(O.FACILITY_ID, '') <> ISNULL(N.FACILITY_ID, '') and
        DBA.TB_COMPLAINT_LOG.RELATED_ID = N.RECORD_ID
    
      --Suggestion #7420, if the STATUS_CHANGED_DATE is updated, then raise an
      --exception if the changed date is a future date.
      if update (STATUS_CHANGED_DATE) begin
        if exists (select *
                   from   inserted
                   where  dba.dateonly (STATUS_CHANGED_DATE) > dba.dateonly (getdate())) begin
          exec dba.sp_raiserror_trigger 21318, 'Change Status Date cannot be in the future.'
          return
        end
      end
    
      declare @StatusChanged datetime
      set @StatusChanged = dba.dateonly (getdate())
    
      --Make sure that there is no updated records that has STATUS_CHANGED_DATE set
      --as null.  This code will not update the existing record that has
      --STATUS_CHANGED_DATE as null prior to the update. This is to prevent
      --assigning date to the existing data (Data that exists prior to the
      --implementation of suggestion #7420).
      update DBA.TB_CORE_GENERAL_PROGRAM
      set    STATUS_CHANGED_DATE = @StatusChanged
      where  (RECORD_ID in (select inserted.RECORD_ID
                            from   inserted, deleted
                            where  inserted.RECORD_ID = deleted.RECORD_ID and
                                   inserted.STATUS_CHANGED_DATE is null and
                                   deleted.STATUS_CHANGED_DATE is not null))
    
      --Update the STATUS_CHANGED_DATE with todays date if the user does not
      --manually update the STATUS_CHANGED_DATE.  Please refer to the change request
      --7420 of why these two updates cannot be combined together.
      if (update (CURRENT_STATUS) and
          not update (STATUS_CHANGED_DATE))
        update DBA.TB_CORE_GENERAL_PROGRAM
        set    STATUS_CHANGED_DATE = @StatusChanged
        where  RECORD_ID in (select inserted.RECORD_ID
                             from   inserted  join
                                    deleted on ((inserted.RECORD_ID = deleted.RECORD_ID) and
                                                (inserted.CURRENT_STATUS <> deleted.CURRENT_STATUS)))
    
      -- We dont allow replication agent to update these fields (this is for ASA,
      -- for the  MSSQL we have skipped the whole trigger at the first line)
      if dba.get_is_replication_agent() = 0
      begin
        update
          dba.TB_CORE_GENERAL_PROGRAM
        set
          dba.TB_CORE_GENERAL_PROGRAM.UPDATE_BY = cast(suser_sname() as varchar(8)),
          dba.TB_CORE_GENERAL_PROGRAM.UPDATE_DATE = dba.dateonly(getdate()),
          dba.TB_CORE_GENERAL_PROGRAM.LAST_TOUCHED = getdate()
          --##RecordVersion reserved for future development, do not remove this line
        from
          dba.TB_CORE_GENERAL_PROGRAM, inserted N
        where
          dba.TB_CORE_GENERAL_PROGRAM.RECORD_ID = N.RECORD_ID
      end
    end

  9. #9
    Join Date
    Apr 2012
    Posts
    7

    Other trigger

    TB_CORE_GENERAL_PROGRAM_TI01
    Code:
    USE [SBCEHSEC_Test]
    GO
    /****** Object:  Trigger [dba].[TB_CORE_GENERAL_PROGRAM_TI01]    Script Date: 04/18/2012 10:58:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER trigger [dba].[TB_CORE_GENERAL_PROGRAM_TI01] on [dba].[TB_CORE_GENERAL_PROGRAM] for insert
    --not for replication
    as
    /*
    Change Log:
    Name    Date      Changes
    MVEA		06/30/09	PBI #6399. Modified to create the SII regardless of the status 
    									and to set the CONTRIBUTOR field of SII.
    */
    begin
      --if we are inside the MSSQL replication OR client application does not use triggers - just return
      if ((dba.get_is_replication_agent() = 1) and (dba.get_servertype() = 'MSSQL')) OR (dba.get_disable_triggers_option() = 1)
        return
    
      -- Check for the records in inserted table
      if not exists(select * from inserted)
        return
    
      --drb     4/28/99   Modified to include DISCOUNT_CODE
      insert into dba.TB_FIN_SCHEDULED_INVOICE_ITEM(
        RECORD_ID, FACILITY_ID, PE, AID, NUM_UNITS, DISCOUNT_CODE, CONTRIBUTOR)
      select
        N.RECORD_ID, N.FACILITY_ID, N.PE, 'PR', N.UNITS, N.DISCOUNT_CODE, N.CONTRIBUTOR
      from
        inserted N
    --  where
    --    N.CURRENT_STATUS = '01'
    
      update
        DBA.TB_CORE_FACILITY
      set
        DBA.TB_CORE_FACILITY.PROGRAM = (select count(*) from DBA.TB_CORE_GENERAL_PROGRAM T
                                        where T.FACILITY_ID = N.FACILITY_ID)
      from
        DBA.TB_CORE_FACILITY, inserted N
      where
        DBA.TB_CORE_FACILITY.FACILITY_ID = N.FACILITY_ID
    
      --Suggestion #7420, check for future date for the STATUS_CHANGED_DATE
      if exists (select *
                 from   inserted
                 where  dba.dateonly (STATUS_CHANGED_DATE) > dba.dateonly (getdate())) begin
        exec dba.sp_raiserror_trigger 21318, 'Change Status Date cannot be in the future.'
        return
      end
    
      --Suggestion #7420, update the STATUS_CHANGED field. ENTERED_DATE is
      --included in this update command to prevent the UPDATE trigger from
      --firing, otherwise, the logic in the UPDATE trigger will fire and might
      --cause unwanted side effect.
      update DBA.TB_CORE_GENERAL_PROGRAM
      set    STATUS_CHANGED_DATE = dba.dateonly (getdate()),
             ENTERED_DATE        = ENTERED_DATE
      where  (STATUS_CHANGED_DATE is null) and
             (RECORD_ID in (select RECORD_ID
                            from   inserted))
    
      --Set up ENTERED_BY and ENTERED_DATE fields
      update
        dba.TB_CORE_GENERAL_PROGRAM
      set
        dba.TB_CORE_GENERAL_PROGRAM.ENTERED_BY = cast(suser_sname() as varchar(8)),
        dba.TB_CORE_GENERAL_PROGRAM.ENTERED_DATE = dba.dateonly(getdate()),
        dba.TB_CORE_GENERAL_PROGRAM.UPDATE_BY = null,
        dba.TB_CORE_GENERAL_PROGRAM.UPDATE_DATE = null
      from
        dba.TB_CORE_GENERAL_PROGRAM, inserted N
      where
        dba.TB_CORE_GENERAL_PROGRAM.RECORD_ID = N.RECORD_ID
    end

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    That is quite a Trigger.

    I think you may shave off a few nanoseconds by replacing this code
    Code:
      update DBA.TB_CORE_GENERAL_PROGRAM
      set    STATUS_CHANGED_DATE = @StatusChanged
      where  (RECORD_ID in (select inserted.RECORD_ID
                            from   inserted, deleted
                            where  inserted.RECORD_ID = deleted.RECORD_ID and
                                   inserted.STATUS_CHANGED_DATE is null and
                                   deleted.STATUS_CHANGED_DATE is not null))
    by
    Code:
      update DBA.TB_CORE_GENERAL_PROGRAM
      set    STATUS_CHANGED_DATE = @StatusChanged
      where  EXISTS (select 1
                     from   inserted, deleted
                     where  DBA.TB_CORE_GENERAL_PROGRAM.RECORD_ID = inserted.RECORD_ID and
                            inserted.RECORD_ID = deleted.RECORD_ID and
                            inserted.STATUS_CHANGED_DATE is null and
                            deleted.STATUS_CHANGED_DATE is not null))
    I have never seen so much business logic in a trigger before.

    I consider triggers to be the dark matter in a database. When you change something in table A and weird things happen to table X, Y and Z, blame it on the Dark matter in your database. Hard to test, hard to maintain, hard to control (your company has found a way with if (dba.get_disable_triggers_option() = 1) then return), when altering tables they sometimes get inactivated without anyone noticing it, ...

    You could consider writing a Stored Procedure to put all that business logic in.
    Last edited by Wim; 04-18-12 at 18:28.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Apr 2012
    Posts
    7
    Unfortunately, the triggers are, for all intents and purposes, off-limits to me and I will need to work around them as they are. This database supports a software product we use and the vendor allows us to do certain things, like the update I have listed above, but altering the triggers on our own is a no-no; and, in any case, would be overwritten on incremental version updates. I could, of course, ask them to change it - but I don't have a lot of confidence that this would be handled in a timely matter.

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    OK.

    I don't know the answer to your first two questions.
    3) If I had 1000 different pe values from say 4000 to 5000, what would be the best way to set up an iterative query for this? (if that's even a good way to tackle it)
    You may try this:
    Code:
    DECLARE @peInt	INT
    DECLARE @pe	CHAR(4)
    
    SET @peInt = 4000
    
    WHILE @peInt <= 5000
    BEGIN
     SET @pe = CAST(@peINT as CHAR(4))
     
        UPDATE p 
           SET p.pe = p1.pe
          FROM people p
    INNER JOIN tmp_people p1
            ON p.record_id = p1.record_id
         WHERE p.pe <> p1.pe
           AND p1.pe = @pe
           
      SET @peInt = @peInt + 1
    
    END
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Apr 2012
    Posts
    7
    Wim,

    Thank you. I believe that will be effective for many of my future needs. Worked on 1100 records in 3 seconds on an unloaded test db.

    To the others who have been so kind to reply, I'd still love to learn more about this if anyone could answer #1 & #2.

    /3 awarded.
    2/3 still available.


  14. #14
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by DeanH View Post
    /3 awarded.
    2/3 still available.

    Thanks and challenge accepted. (I would do anything for beer)

    1) Why the huge difference in time between the two queries when there's only a small difference in the number of rows? (4x the rows, +300x execute time)
    Because of the heavy triggers and the way SQL Server handles them. (This could be the answer of a clueless student to an exam question)
    2) Is there a better way to do this?
    Yes. Get rid of those heavy triggers or see my solution to question 3).

    You may feel less satisfied with these answers, but trust me, that is just your personal feeling. How about those extra beers?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The triggers can stay, but need to be optimized. However I'm confused that the triggers are on table TB_CORE_GENERAL_PROGRAM, when the sample update statement you provided refers to a table named "People".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •