OUTPUT Performance Optimization
[This thread is associated with Part 18 of the SQL Credit series.]
Do you see something that would make the OUTPUT clause work as fast as the trigger?
Wrapping the DestinationTableBase INSERT and the HistoryTableBase INSERT in a transaction helped performance at the numbers I was running, but it's quite possible that at much larger rowcounts, the transaction would actually hurt performance.
if you do not use table variable
Rob,
I am reading your articles just now and must say You really doing great job here. thank you.
I've just had an idea I wanted to share. The speed up here should be significant as generally I find table variable much slower for larger amount of data.
CREATE TABLE test (i UNIQUEIDENTIFIER DEFAULT (NEWID()))
CREATE TABLE test_hist (i UNIQUEIDENTIFIER)
GO
--insert test values and output data straight to history table
INSERT INTO test
OUTPUT inserted.i INTO test_hist (i)
DEFAULT VALUES
INSERT INTO test
OUTPUT inserted.i INTO test_hist (i)
DEFAULT VALUES
SELECT * FROM test
SELECT * FROM test_hist
GO
DROP TABLE test, test_hist