March 21, 2012 at 7:08 am
Hi guys, I have inherited a system that has, as part of a stored procedure, the following logic
TRUNCATE Table_2
Insert into Table_2 from Table_1
TRUNCATE Table_1
Insert into Table_1 from Table_0
Truncate Table_0
it then loads data into Table_0 using an SSIS package
The Idea is to keep three days data 'alive' This is required because we have to calculate balance movements between the days. There is approximately 3.6M records per day. the three tables have an identical structure
and has approximately 40 columns, most of which are decimal(10,2)
I am thinking of merging the tables and adding a column to manage the countback (may be an integer or a loaded date), but if I do this then I can't use TRUNCATE to delete older data but I save myself the hassle of copying records between tables.
I am interested to hear what other users think is the best way to handle this. I don't need data after it is 3 days old and I don't particularly want it cluttering up the database as it quickly gets out of control (I already have another table [don't ask!] with the same data in with 465M rows with is about to break 1TB on the disk
Before anyone suggests just appending the data to this table, I have plans to remove redundant records so that I only keep records where the values have changed over the previous load (about 6% of the current records)
Should I go with my revised structure and have a separate job that deletes the old data or stick with the current process, or something else/
March 21, 2012 at 10:35 am
I was thinking partitioning, but that would be overkill for the three tables. I would however look into partitioning for getting your arms around your 1TB table.
Back to the three tables, why not use a process that drops, renames and (re)creates? These DDL operations will be quicker than even a TRUNCATE for that many rows.
1. Begin transaction
2. Drop Table_2
3. Rename Table_1 to Table_2
4. Rename Table_0 to Table_1
5. Create a new Table_0
6. Commit transaction
That should take milliseconds. It has the downside of issuing a CREATE TABLE in code, but you could make it dynamic if you generate the CREATE TABLE statement from the definition of one of the other tables at runtime.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2012 at 8:46 am
Great idea!
One other issue would be that the index names would not correlate to the table. SQL doesn't care but a hint optimiser might 😛
your point about the DDL in code is a good one, and would also apply to indexes. The other idea I had on the way in to work today was to create Synonyms and programatically rotate the synonymns through the underlying tables. This has the advantage of not having to drop or rebuild the tables and indexes. The only problem with this is that ODBC does not support symonymns so you cannot (for example) write an Excel database query or an Access PassThru query directly against the Synonym. for this reason, if implementing this solution I would also reccomend creating views which use the synonyms with the same names as the original tables so that all existing queries will still work
March 22, 2012 at 10:23 am
aaron.reese (3/22/2012)
Great idea!One other issue would be that the index names would not correlate to the table. SQL doesn't care but a hint optimiser might 😛
You can rename your indexes at the same time you rename your tables if that is a concern. Or better yet, decide on a basic set of index names and use those on all three tables so the hints will work regardless of the table being referenced...wait, why are you thinking of using index hints 😀
your point about the DDL in code is a good one, and would also apply to indexes. The other idea I had on the way in to work today was to create Synonyms and programatically rotate the synonymns through the underlying tables. This has the advantage of not having to drop or rebuild the tables and indexes. The only problem with this is that ODBC does not support symonymns so you cannot (for example) write an Excel database query or an Access PassThru query directly against the Synonym. for this reason, if implementing this solution I would also reccomend creating views which use the synonyms with the same names as the original tables so that all existing queries will still work
With Views or synonyms you would potentially have the same issue with mismatched index names.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2012 at 5:20 pm
I'm not thinking of using optimiser hints, just trying to make sure that all bases are covered for future thread viewers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply