Results 1 to 4 of 4

Thread: OUTPUT Performance Optimization

  1. #1
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

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

  2. #2
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152

    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
    You Have To Be Happy With What You Have To Be Happy With (KC)

  3. #3
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Looks Promising

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

  4. #4
    Join Date
    Feb 2009
    Posts
    3

    I have a question

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •