SQL Server Performance

What is the best method to archive data?

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by preethi, Jan 27, 2010.

  1. preethi Member

    Hi all,
    I have a particular situation:
    We have a process (process 1) which goes through all the data inserted today, from one table and do a complex calculation using multiple tables. If a particular condition is not met, I copy the data into a table (ProcessData). This process will go though > 100K of rows but will insert <1000 rows into the table.
    Then I have another process (Process B), Which takes the rows from ProcessData table one by one and executes another complex processing. Process B uses some huge and heavily used tables (30+ million rows and around 300 K different user connections daily) but it works only with the very small subset of the data.
    Both Process A and Process B are windows services working at off peak time.
    Now what is the best way of archiving the data from ProcessData table.
    Suggestion 1: When Process B completes a row delete the row and move the data into archive table
    Suggestion 2: Wait until Process B completes all rows and move all rows to Archive table and truncate the table
    Suggestion 3: When Process B completes a row mark the row (using a flog). Create another scheduled job to move the data using the flag periodically.
    Please let me know what is the best method?
  2. FrankKalis Moderator

    Since this is a daily job working only on a few thousands of rows, I would go for option 2. Option 1 seems like overhead and maybe risky in case something unexpected happens to Process B and the windows service needs to be restarted. Option 3 looks also good to me, but seems to be more complex than what seems to be necessary.
  3. preethi Member

    Thank you Frank for your input.
    I am wondering, whether there will be any benefit the 3rd method has over the second method?
  4. pyale New Member

    Are you using Enterprise edition? If so, have you thought of using partitioned tables to switch the archive data out of the ProcessData table and into the archive table? This switch is instantaneous, has no log impact, and is a very elegant solution when implemented.
  5. Luis Martin Moderator


    Welcome to the forums!. Your collaboration is very important for all of as.
    Only one recommendation: check threads date. You use to answer olds ones.

  6. pyale New Member

    I had noticed that - but the idea of using partitioning might still have been relevent to other people who hadn't considered it, so I thought it was worth putting it on there.
  7. FrankKalis Moderator

    Yes and no. :)
    It most likely won't help the original poster after 1.5 years, but will likely help others experiencing the same or a similar situation.
  8. pyale New Member

    Exactly. Like I said : ".... might still have been relevent to other people who hadn't considered it....." So the answer was "Yes".
  9. satya Moderator

    Unless there is a difference of the feature..such as version difference, it is better to leave the old threads where the OP hasn't been able to follow it up.

    However .. we would like you to encourage posting more out of experiences on the latest threads... keep going. :)

Share This Page