Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Ability to Archive Data
It would be nice to be able to Archive data from time to time (or by regular schedule). As another user has experienced, tables with masses of data seem to struggle when particular views are executed. See:

Improve performance of summary columns

My idea would perform the following:

A new Archive table would be created for the main (offending) table.
When a record reaches a certain 'age' or status (set by the administrator), it would create a new identical record in the Archive table - either by periodic trigger or by button. Maybe by just adding a "-1" to the original Id (like the new Dev mode does with the App Id)...
All records related to this table would then be reassigned to the new archived record (via a workflow).
Then the original record will be deleted....

With this setup, the most current data is easy to work with and easy to get reports on. Generally speaking, we rarely look at data that is over 5 years old - and if we did, it'd still be there, just archived.
ID
688
Category
TeamDesk
Author

basenine
Date Created
11/7/2013 3:20:30 AM
Date Updated
11/8/2013 2:41:21 AM
Status
New Idea
Score
20
Promoted By
martin oliverbasenine
Comments
martin oliver 11/7/2013 5:34:41 AM
How would this affect running balances in a table where some of the the data used to calculate this running balance is derived from related tables. I am interested in the concept since potentially all tables will eventually become large and what is the long term effect of this, or does it not really matter.
Nathan Phillips (CW) 11/7/2013 7:36:50 AM
This might be a place where table partitioning could be useful:
http://msdn.microsoft.com/en-us/library/dd578580(SQL.100).aspx

I could see splitting a transaction table by month, or by year, if the current month or year is all that's normally accessed.
basenine 11/7/2013 5:24:31 PM
@Martin - I'm proposing that all the related data is transferred to the new Archived record. Having read @Nathan's post though, it seems there is a better way.

Having said all that....

The reason for this post was because I was getting a timeout error on a particular view in a table. It turns out that a couple of columns were confusing(?) the view. Using the new <Ask the User> function initially made things worse but after careful trial and error, I was able to find which columns were making the timeout happen. They were quite innocuous columns - a date field and a checkbox field.

It was weird because another view in the same table had more 'resulting data' and performed the execution quickly.

I've put in a support request so we'll see what transpires from that.
Cheers
martin oliver 11/8/2013 2:41:21 AM
@baseline

We also had a timeout issue on the recycle bin which, thanks to support, was identified as being caused by two columns in a view. I have promoted the idea as I am unsure at what point a database in Teamdesk becomes too heavy, if ever. Maybe Teamdesk support can answer this. If there is a potential problem going forward then @nathan makes sense.
Thanks
Feedback
 
Back to Search Results