Have a change log for tables but need to know about performance hits

[noodle22]

Hi,

I've created a change log for when data in a table changes (I'm actually using it for a wiki type app). I have two ways of doing this
a) copy all the new data and all the old data into the change log table
b) copy only the changes

The advantage of (a) is that it doesn't compare anything and just spits all the old and new data into a change log table. Unfortunately, lots of data that doesn't need to be copied will be if only a small change takes place,

The advantage of (b) is that only the changes are copied. The disadvantage is that for updates only, I have to compare all the fields from the inserted and deleted to see if they are changed (so, I almost need a really fast equals comparison...although, maybe an equals comparison already is pretty fast), use dynamic sql, copy inserted/deleted into temp tables

So, here is the code. I'm sure it is useful for other projects but I still don't know which method to use. I just put a trigger on the tables that I need to track (there are only a small number of these tables

Method A

CREATE TRIGGER [dbo].[TableName_HISChanges]
ON [dbo].TableName
FOR UPDATE, DELETE, INSERT AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Action VARCHAR(6)
	DECLARE @inserted VARCHAR(MAX)  
	DECLARE @deleted VARCHAR(MAX)   

	SET @Action = 'UPDATE'
	SET  @inserted = (SELECT * FROM inserted as TableName FOR XML AUTO,  ELEMENTS)
	SET  @deleted = (SELECT * FROM deleted as TableName  FOR XML AUTO,  ELEMENTS) 

	IF @inserted IS NULL
		SET @Action = 'DELETE'

	IF @deleted IS NULL
		SET @Action = 'INSERT'

	IF ((NOT @inserted IS NULL) OR (NOT @deleted IS NULL))
		INSERT INTO HIS_TableName(CommandType,Changes, OldData) VALUES
		(@Action, @inserted,@deleted )
END

And here is method b

CREATE TRIGGER [dbo].[trig_HISTableName2]
ON [dbo].TableName
FOR UPDATE, DELETE, INSERT AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Action VARCHAR(6)
	DECLARE @inserted VARCHAR(MAX)  
	DECLARE @deleted VARCHAR(MAX)   
	DECLARE @InsertCount INT
	DECLARE @DeleteCount INT
	DECLARE @Xml VARCHAR(MAX)
	DECLARE @SQL NVARCHAR(MAX)

	SELECT @InsertCount = Count(*) FROM inserted
	SELECT @DeleteCount = Count(*) FROM deleted

	IF @InsertCount = 0
	BEGIN
		SET @Action = 'DELETE'
		SET  @Xml = (SELECT * FROM deleted as TableName FOR XML AUTO,  ELEMENTS)
	END
	ELSE IF @DeleteCount = 0
	BEGIN
		SET @Action = 'INSERT'
		SET  @Xml = (SELECT * FROM inserted as TableName FOR XML AUTO,  ELEMENTS)
	END
	ELSE
	BEGIN
		SET @Action = 'UPDATE'

		DECLARE @compareFields VARCHAR(MAX)
		SET @compareFields =
		(	SELECT 
				CASE 
					WHEN NOT cu.COLUMN_NAME IS NULL THEN --is a primary key, we need this column
						'''<PKColumn Name="' + c.Column_Name + '">'' + CAST(i.' + c.Column_Name + ' AS VARCHAR(MAX)) + ''</PKColumn>'' + '
					ELSE --just a regular column that might have been changed
						'CASE WHEN d.' + c.Column_Name + ' != i.' + c.Column_Name + 
						' THEN ''<column name="' + c.Column_Name + '"><new>'' + CAST(i.'+ c.Column_Name + ' as VARCHAR(MAX)) + ''</new><old>'' + CAST(d.'  + c.Column_Name + ' as VARCHAR(MAX)) + ''</old></column>''ELSE '''' END+'
				END
			FROM 
			--Get the Column Names
			INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
			INNER JOIN
			INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
			ON 
			cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME AND
			cu.TABLE_NAME = pk.TABLE_NAME AND 
			pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
			RIGHT OUTER JOIN
			INFORMATION_SCHEMA.COLUMNS c 
			ON cu.COLUMN_NAME = c.COLUMN_NAME AND
			c.TABLE_NAME = cu.TABLE_NAME
			WHERE c.TABLE_NAME = 'TableName'
			FOR XML PATH('')
		) + ' '''' '

		SET @compareFields = REPLACE(@compareFields,'&lt;','<')
		SET @compareFields = REPLACE(@compareFields,'&gt;','>')

		SELECT * INTO #deleted FROM deleted
		SELECT * INTO #inserted FROM inserted

		SET @SQL = 'SELECT @Xml=' + @compareFields  + ' FROM #inserted i
		INNER JOIN
		#deleted d ON i.CourseID = d.CourseID 
		'
	
		EXEC sp_executesql @SQL,N'@Xml VARCHAR(MAX) OUT', @Xml OUT
	END	

	INSERT INTO HIS_TableName2(CommandType,Changes) VALUES
		(@Action, @Xml)
END

 

[RyanEK]

If you're concerned about performance, method B will always always be slower. We use method A ourselves and have no problem dumping a copy of the entire row into the history table.

We also have a process in place that deletes old history records periodically.

 

[SQLSister]

If you want performance, never use dynamic sql. It is an extremely poor programming practice. Especially in a trigger! Inserts can and should be done in a set based fashion.

Plus if you are going to create audit tables you need to have them set up in such a fashion as you can easily find the data and recover it if need be. It looks to me as if you are putting all the records into one field. This will make it hard when you need to fix one column of one record.

It is better to have separate triggers, one for deletes only, one for inserts only and one for updates. We have two audit logs for each table, one that stores information about when the action occurred and who did it and the other stores the details of what change occurred. And while you areinteh process of developing this, I'd write and test the recovery scripts becasue it is better not to have to do that with the CEO staring down your back because someone accidentally deleted an entire table and all business in the company is at a standstill. I'd rethink your whole process.



"NOTHING is more important in a database than integrity." ESquared

 

[noodle22]

After thinking about this some more, I can do method (b) by not using dynamic SQL and by not having to copy inserted/deleted into temp tables. I would just make a special case of the trigger for each table I am auditing (just hard code the columns rather then get them from the information schema). This means that the only extra step I have to do now compared to a is compare all the fields...which I think could be faster then copying a bunch of fields I don't need into the audit tables. Do you agree?

As for dynamic sql, I don't agree that it is extremely poor programming practice. Some problems can only be solved with dynamic sql (ie, if you want to do operations on a list of Ids in a stored procedure passed from your dao...it may be faster to use dynamic SQL on the list then to go back and forth to the database and repeat an operation n times. Also, I have read that dynamic sql is much better in 2005 with some caching). I do agree that it should only be used when necessary, either to improve performance for certain situations, or improve DB maintenance by making some complicated processes easier.

Putting the records in one field put me off at first, but SQL Server 2005 has very good XML select tools and it is actually very easy to filter through the data in those fields.

I will definately write recovery scripts before the release. That is always a good idea

 

[noodle22]

Ok, so I came up with a way to do it without using dynamic sql but then I started thinking about the use of this implementation. What I am really going to want is to not just compare changes, but compare one version of the row with a previous version (and possible set the previous as the new current version). So, really, even though storing only the changes seems more efficient, it is going to bite me down the road when I want to compare to versions of a row that are not consecutive.