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.