3
\$\begingroup\$

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.

\$\endgroup\$
5
  • \$\begingroup\$ Is your schema set in stone, or is it negotiable? \$\endgroup\$ Feb 4, 2015 at 21:03
  • \$\begingroup\$ @200_success it is set in stone unfortunately. The source tables also have 0 indexes and I don't have the rights to change that :( \$\endgroup\$ Feb 4, 2015 at 21:04
  • \$\begingroup\$ Relevant indexes exist on the table, I hope? \$\endgroup\$ Feb 4, 2015 at 21:06
  • \$\begingroup\$ @200_success so did I. The log tables are mine and I can change them at will, and yes they're indexed. The source tables are not mine, are in a totally separate database and come with absolutely no indexes or PK's. \$\endgroup\$ Feb 4, 2015 at 21:08
  • \$\begingroup\$ Not really an answer as such, but you could convert variable = variable + somethingElse into variable += somethingElse without losing much readability. The += syntax is incredibly common and I'm pretty sure anyone that comes into contact with code is going to come across it very quickly. \$\endgroup\$ Feb 5, 2015 at 0:47

1 Answer 1

2
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ a trigger was my initial solution but as I stated in the comments I do not have administrative access to the source tables. \$\endgroup\$ Feb 5, 2015 at 0:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.