Return most recent non null field values for multiple unknown fields

Question

I am attempting to create a stored procedure that looks at one table and imports changes in that table to another.

This stored procedure is going to be preformed on multiple source tables and will dump out into multiple log tables. The way the data in the log table is stored looks like this:

ID | Source_PK_field | field_1 | field_2 | field_3 | Update_Type | Updated
01 |        001      |  value  |  value  |  value  |      C      | 2015-02-04
02 |        001      |   null  |  change |   null  |      U      | 2015-02-05
03 |        001      |  change |   null  |  change |      U      | 2015-02-06 

The idea being that if a record changes the log table would indicate all the fields that changed with their new values but leave fields that didn't change for the record null.

In order for this to work I need to grab a copy of what the record looked like in the most recent log version vs the current source version (the problem being that some of the values in the most recent log will be null)

In order to accomplish that I am using the current query inside my stored procedure:

DECLARE @latest_sql VARCHAR(MAX) = 'SELECT [ID], [' + @relationKey + '] '

SELECT @latest_sql = @latest_sql + ', (SELECT TOP 1 [' + COLUMN_NAME + '] FROM (SELECT * FROM ' + @changelog + ' x WHERE x.[' + @relationKey + '] = CT.[' + @relationKey +'] AND [' + COLUMN_NAME + '] IS NOT NULL) q ORDER BY UPDATED DESC) AS [' + COLUMN_NAME +'] '
FROM #COLUMN_NAMES
WHERE COLUMN_NAME != @relationKey

SET @latest_sql = @latest_sql + ' INTO ##LATEST_UPDATES FROM ' + @changelog + ' CT'

EXEC(@latest_sql)

The stored procedure requires you to pass it @changelog the log table, @source the source table and @relationKey the primary key that ties the two tables together (usually the source PK). And #COLUMN_NAMES is a temp table created before this is executed in the stored procedure that simply stores column names that match between the source table and the log table (not all fields from the source are being logged).

Now this code behaves as intended, the problem is the query takes about 10-20min to complete (just this part of the stored procedure). The heaviest query this is used on is looking at 48 fields in a table that only stores 300K records. There has got to be a way I can make this query faster.

Answer 1

Since you are using a stored procedure, it is not much of a stretch to extend that to be a trigger.

Triggers are the natural tool to use for this type of problem, and are designed to have two 'virtual' copies of the data, one copy is the value before the change, the other copy is the value after the change. In your case, you would have a trigger for insert (create), delete, and update.

Read up on triggers here: DML Triggers and the syntax here: CREATE TRIGGER (Transact-SQL)

In the trigger, you would scan the inserted and deleted virtual tables for the data that is changing, and use that to maintain the audit logs in your logging table. Note that thoese virtual tables contain only the data affected, so scanning them is fast.

For more information on those tables, see The Inserted/Deleted table documentation

Using the above mechanism, you can track the changes as they happen. Each instance will be relatively quick, though the work will happen inside the same transaction as the inserts to the main table.

Using a stored procedure like you have, is a good first step, but you should transfer the relevant logic in to the trigger instead.