none
Update Trigger and COLUMNS_UPDATED()

    Question

  • Let me rephrase my question:

    I want to know if there is a faster way (probably using bit operators) to see if COLUMNS_UPDATED() in an update trigger on a table with more than 8 columns contains any column other than 1 specific column I will refer to as 'columnToIgnore'. I don't care about which columns are included or if the value has actually changed, only that there are other columns present.

    Original question:

    I have inherited a Sql Server database + schema and corresponding application and I have been tasked with speeding up the batch update of users in the database. After running some profiling I discovered that the reason it was slow is because of an update trigger on the users table. I did some “cleaning up” of the trigger and now it runs faster but I still think it could be more efficient. The objective of the trigger is to write to an Audit table any time an update takes place and any column is updated (it does not actually matter if the value has changed or not) with the exception of 1 specific column. Now I have the following code in the trigger which I had refactored from something much uglier.

    DECLARE @ColumnsUpdated VARBINARY(100)
    SET @ColumnsUpdated = COLUMNS_UPDATED()
    
    DECLARE @onlyIgnoreColumnChanged int
    SET @onlyIgnoreColumnChanged = 1
    
    SET @onlyIgnoreColumnChanged = 1
    IF (EXISTS(SELECT 1 
    		FROM INFORMATION_SCHEMA.COLUMNS Field
    		WHERE TABLE_NAME = 'users' 
    		AND sys.fn_IsBitSetInBitmask(@ColumnsUpdated, COLUMNPROPERTY(OBJECT_ID('dbo.users'), COLUMN_NAME, 'ColumnID')) <> 0 
    		AND column_name !='columnToIgnore')) BEGIN
    	SET @onlyIgnoreColumnChanged = 0
    END

    Ideally I would change the code to disable the trigger and do the insert myself BUT the code is old Classic ASP ( ie. VBScript with ODBC database connections) so I am not going to change any of the calling code because that would cost too much time and probably create more problems than I am solving.

    My proposed fix which I am unsure of how to code in SQL:

    It seems to me that there should be a way to get the proper id or position for the column and see if the column was used in the COLUMNS_UPDATED().

    1. If it was not used then we can set @onlynieuwchanged to 0 for the remaining code (not shown)
    2. If it was used then remove the bit from the COLUMNS_UPDATED() and see if it is empty
      1. If its empty then then we can set @onlynieuwchanged to 1 for the remaining code (not shown)
      2. If it’s not empty then then we can set @onlynieuwchanged to 0 for the remaining code (not shown)

    My problem is I am not sure how to go about coding this logic in SQL. I would think it would be possible with BIT operators? It would seem this could be more efficient than what I have now as it would get rid of the select statement which could slow down a large batch update.

    Notes

    • My table has 30+ some columns so more than 8 which is relevant when working with COLUMNS_UPDATED() from what I have read. It also means that testing each column with UPDATE() would probably be more inefficient than what I have now.
    • The update is called from the front end code (VBScript) which currently times out although not as much as it used to with my latest change. I cannot alter the code to run async mode and show progress, it would be easier for me to update the trigger.
    • Reason for audit - we only record the user id in an audit table. This is then used to force a sync to another system outside of our source control that is used for various other tasks (mailings, views, etc). Again, I do not want to change how this system works, I only want to speed up the existing trigger with minimal effort.
    • Sql Server version 2012, database Schema is set to Sql server 2005 compatibility mode.

    Any help would be greatly appreciated. Thank you in advance!

    -Igor

    • Edited by IWolbers Tuesday, April 08, 2014 1:59 PM Receiving answers not relevant to question.
    Tuesday, April 08, 2014 1:01 PM

Answers

  • Thank you again everyone for your input. As I mentioned there is much out of my control. This is an active application worked on by many developers and has been pieced together over the past 10+ years. It is NOT well written, the database schema has MUCH to be desired, and the code is the classic definition of 'spaghetti' code and multiple platforms are used to access the data, and there are millions of lines of code. There is nothing I can do about all of this which is why I am ignoring much of the advice. Sure, if this was a new application or something that was well written and possible to do some modifications then I would BUT it's not. If I was given 1 year of dedicated time to fix everything... well, I would probably throw it all away and start from scratch.

    Here is what I finally came up with. It is probably not  much faster, if at all, but when I started this I thought there would be a simple solution that I did not see. I will probably leave my code the way it was but will include the following for reference.

    DECLARE @bit int, @field int, @char int, @comparisonField VARBINARY(100)
    -- the following segment recreates the value contained in COLUMNS_UPDATED() if only the field nieuw was altered and stores that value in field @comparisonField
    SET @field = (SELECT COLUMNPROPERTY(OBJECT_ID('dbo.users'), 'columnToIgnore', 'ColumnID')) -- geth the field id for column nieuw
    select @bit = (@field - 1 )% 8 + 1 
    select @bit = power(2,@bit - 1) 
    select @char = ((@field - 1) / 8) + 1
    -- select @field AS [Field number], @char AS [Char], @bit AS [Bit] -- debug code to check the bits that are tested.
    -- Recreate the binary value of just having the field present in the columns_updated
    SELECT @comparisonField = CONVERT(VARBINARY(100),'0x' + RIGHT('000000000000000000' + RIGHT(CONVERT(VARCHAR(30), CONVERT(varbinary(1), @bit), 1), 2), @char*2), 1)
    
    -- if the generated value in @comparisonField is the same as COLUMNS_UPDATED() then only nieuw was updated
    IF @comparisonField <> COLUMNS_UPDATED() BEGIN
    	-- code to add to the tracking table 
    END

     

    -Igor

    • Marked as answer by IWolbers Tuesday, April 08, 2014 7:19 PM
    Tuesday, April 08, 2014 7:18 PM

All replies

  • What is @@version?

    It is better to use the OUTPUT clause for audit trail:

    http://www.sqlusa.com/bestpractices2005/auditwithoutput/

    If you stay with the trigger, the simplest audit trail is to write the deleted & inserted tables.

    For column update checking, it is more advantageous to use the newer  UPDATE() function than COLUMNS_UPDATED().


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, April 08, 2014 1:23 PM
    Moderator
  • The COLUMNS_UPDATED() function does NOT validate if the column actually changed values, but if the column was passed in the UPDATE statement.  It will most likely save you nothing because if you pass it, even with the same value it will flag it.

    Please post the actual trigger code and someone may be able to help speed it up.

    Tuesday, April 08, 2014 1:48 PM
    Moderator
  • In addition to Tom's comments:

    Sql Server version 2012, database Schema is set to Sql server 2005 compatibility mode

    Please, read the documentation regarding the compatibility mode.  It is intended as a migration tool and does not guarantee complete compatibility (and duplicate functionality) as the selected target.  Given that you are using a compatibility mode, there are likely many more things that should have been done during the initial development and the following migrations that have been avoided - like taking advantage of new features that improve the system efficiency, replacing old or obsolete application interfaces with those more suited to the current environment, etc.  A Band-aid on band-aid approach will generally expose itself over time - and it seems like this is your time.

    Secondly, I will guess that the trigger code either assumes that a single row is affected or attempts to handle multi-row updates with a cursor.  Either one is a problem; the cursor can be a performance killer.

    Lastly, using logic that is based on the position of a column is generally asking for problems.  Typically these problems occur much later in time, often long after the original developer has moved on.  Would you want to be the one assigned to the task of tracking such an issue (and, on the flip side, the one to be blamed for causing such an issue)?

    Tuesday, April 08, 2014 2:50 PM
  • Thank you for your answer but OUTPUT does not solve my problem. My goal is to do as little as possible with this trigger, just alter the existing code slightly to make it faster. Using OUTPUT means re-writing the ASP (VBScript) application and I mentioned above that is not an option.

    As for UPDATE() I agree it would be better IF the code cared about was the value changed but it does not. All I want to know, as efficiently as possible, is are there any columns included in the update other than the one I am ignoring.


    -Igor

    Tuesday, April 08, 2014 4:07 PM
  • UPDATE() - I agree it would be better IF the code cared about was the value changed but it does not. All I want to know, as efficiently as possible, is are there any columns included in the update other than the one I am ignoring.


    -Igor

    Tuesday, April 08, 2014 4:07 PM
  • Thank you for your answer.

    1. I agree but as I mentioned before it is not my job to do this. I am sure you can walk into any office with systems that they had in place for over a decade and create a list of stuff that needs to be done. The problem comes down to time, resources, and priorities. At the moment I have a little time to speed up this trigger, then my boss will ask me to do something else. 

    2. The code, outside of my control, does single update statements.

    3. Using the following statement (was included in the original post) the id/position of the column should not be a problem as it will always be retrieved inside of the cursor. I have no intention of hard coding it.

     
    COLUMNPROPERTY(OBJECT_ID('dbo.spaarprogramma_spaarders'), COLUMN_NAME, 'ColumnID')


    -Igor

    Tuesday, April 08, 2014 4:13 PM
  • The code, outside of my control, does single update statements

    This is always an assumption. And inevitably the assumption fails because the table is not updated in the same exact fashion forever.  There is NEVER a good reason to make this assumption in a trigger.  When the situation does occur, the trigger will either fail and generate an error (which hopefully the application will see and report) or it will logically fail (but succeed from an error-handling POV) since it does something with the values from only 1 of the updated rows. 

    And I'll repeat what Tom said - it is unlikely that a different approach to the "updated rows" logic will provide you any significant performance boost.  But I will give you a suggestion based on a different viewpoint. 

    insert into dbo.audit (...)
    select ... from deleted as d inner join dbo.mytable as main
    on d.id = main.id ...
    where d.columna <> main.columna ...

    The where clause would only need to include the columns that mattered - and this query could easily be generated (and re-generated) with a simple query against the columns catalog view.  The query needs to be coded to handle nullable columns correctly.  You may argue about having the trigger "automatically" respond to changes to the columns of the table.  My counter argument would be that any schema change MUST involve a review of the relevant code - especially triggers. 

    Lastly, I will toss in another thought.  Does the trigger logic ACTUALLY need to be aware of changes?  I ask because a well-written application should be coded to avoid pointless updates - a situation where a row is updated but nothing actually changes (e.g., update my table set col1 = 5 where id = 4 and col1 = 5).  Sometimes we over-engineer for no real benefit. 

    Tuesday, April 08, 2014 6:37 PM
  • Thank you again everyone for your input. As I mentioned there is much out of my control. This is an active application worked on by many developers and has been pieced together over the past 10+ years. It is NOT well written, the database schema has MUCH to be desired, and the code is the classic definition of 'spaghetti' code and multiple platforms are used to access the data, and there are millions of lines of code. There is nothing I can do about all of this which is why I am ignoring much of the advice. Sure, if this was a new application or something that was well written and possible to do some modifications then I would BUT it's not. If I was given 1 year of dedicated time to fix everything... well, I would probably throw it all away and start from scratch.

    Here is what I finally came up with. It is probably not  much faster, if at all, but when I started this I thought there would be a simple solution that I did not see. I will probably leave my code the way it was but will include the following for reference.

    DECLARE @bit int, @field int, @char int, @comparisonField VARBINARY(100)
    -- the following segment recreates the value contained in COLUMNS_UPDATED() if only the field nieuw was altered and stores that value in field @comparisonField
    SET @field = (SELECT COLUMNPROPERTY(OBJECT_ID('dbo.users'), 'columnToIgnore', 'ColumnID')) -- geth the field id for column nieuw
    select @bit = (@field - 1 )% 8 + 1 
    select @bit = power(2,@bit - 1) 
    select @char = ((@field - 1) / 8) + 1
    -- select @field AS [Field number], @char AS [Char], @bit AS [Bit] -- debug code to check the bits that are tested.
    -- Recreate the binary value of just having the field present in the columns_updated
    SELECT @comparisonField = CONVERT(VARBINARY(100),'0x' + RIGHT('000000000000000000' + RIGHT(CONVERT(VARCHAR(30), CONVERT(varbinary(1), @bit), 1), 2), @char*2), 1)
    
    -- if the generated value in @comparisonField is the same as COLUMNS_UPDATED() then only nieuw was updated
    IF @comparisonField <> COLUMNS_UPDATED() BEGIN
    	-- code to add to the tracking table 
    END

     

    -Igor

    • Marked as answer by IWolbers Tuesday, April 08, 2014 7:19 PM
    Tuesday, April 08, 2014 7:18 PM