December 1, 2005 at 2:29 am
Hi All,
I have a huge of transaction data every day. My table will grow very fast.
See below scenario, let say I split my table to each table per month.
What is the advantage and disavantange ?
Original table | |||
trx_tbl | |||
trxno | trxdate | custname | total |
1100001 | 1/11/2005 | A | 1000 |
1100002 | 2/11/2005 | B | 850 |
1100003 | 3/11/2005 | D | 650 |
1200001 | 1/12/2005 | A | 120 |
1200003 | 2/12/2005 | E | 235 |
1200002 | 5/12/2005 | A | 452 |
1000001 | 3/10/2005 | B | 125 |
1000003 | 8/10/2005 | W | 450 |
0900010 | 1/9/2005 | C | 30 |
split table by month | |||
trx_tbl_nov | |||
trxno | trxdate | custname | total |
1100001 | 1/11/2005 | A | 1000 |
1100002 | 2/11/2005 | B | 850 |
1100003 | 3/11/2005 | D | 650 |
trx_tbl_dec | |||
trxno | trxdate | custname | total |
1200001 | 1/12/2005 | A | 120 |
1200003 | 2/12/2005 | E | 235 |
1200002 | 5/12/2005 | A | 452 |
trx_tbl_oct | |||
trxno | trxdate | custname | total |
1000001 | 3/10/2005 | B | 125 |
1000003 | 8/10/2005 | W | 450 |
trx_tbl_sept | |||
trxno | trxdate | custname | total |
0900010 | 1/9/2005 | C | 30 |
Create View trx_tbl_all | |||
as | |||
select * from trx_tbl_sept | |||
union | |||
select * from trx_tbl_oct | |||
union | |||
select * from trx_tbl_nov | |||
union | |||
select * from trx_tbl_dec |
December 1, 2005 at 10:26 am
It will help in the fact that the indexes you build on each table will be smaller.
But will mainly help if you query by the item you separate the tables by, in your case your separating by month, it will help queries you you query by month. but in order for the optimizer to figure out which table to select the data from, you need to create check constraints on the trxDate column.
You will not be able to create an index on the view, because you are using a Union.
If you do not usually query by Date, but more commonly by Customer, then you might want to create tables by smaller segment of customer. and not by date.
December 1, 2005 at 2:59 pm
This is known as partitioning.
See the BOL section titled "Partioned Views" for more info. You view needs to use UNION ALL, not UNION and as mentioned, you need a check constraint on the column being used to partition.
Advantages: If partioned by date, historical partitions can be indexed to 100% FillFactor since they are static. Purging can be done by altering the view to drop partitions, rather than deleting. You can "federate" by placing some of the tables on a different linked server.
December 2, 2005 at 12:29 am
I would name the tables trx_MMYYYY as in trx_112005, we all know it is a tbl, depending on the number of rows you could also consider trx_QYYYY as in trx_42005. Easier to manage IMHO.
The down side is when you need to union great numbers of tables to accomplish the query as in a 10 year AVG.
Andy
December 2, 2005 at 6:54 am
Just being curious, how many rows will be inserted into this table per day? "Huge" is rather vague...
Anyway, dividing the data into many tables is something I would try to avoid if possible. I would prefer to have one table (trx_tbl) that holds all current data and then one or more archive tables, where the data would be moved after some time - a month, quarter, year, whatever suits your needs. Hundred thousand rows per day could still work fine with archiving data only once in a year.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply