OUTPUT Performance Optimization

[rgarrison]

[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.

[YuckFou]

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

[rgarrison]

This looks like a good change. I'm going to modify my scripts to test it. I'll post an update soon.

[krypto69]

I read your article on using OUTPUT and was wondering if it could help my situation.

I have 16 column table that gets 1000 inserts per second.

I have developers that need to run ad-hoc queries on this table. Problem is it take too long for these queries to come back.

What if I were to implement what you did with the two tables? Could the devs query the second/new/'History' table?

Or is the impact the same because there are now two tables instead of one? Is there still a good chance I would get blocking on that second/history table?