Split Huge table to several table, will be faster ?

• asi0ng

  SSC Rookie

  Points: 48

  More actions

  December 1, 2005 at 2:29 am

  #89985

  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

   

   

  Thanks and Regards,

   

   

  Jonny

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  December 1, 2005 at 10:26 am

  #607129

  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.

   

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  December 1, 2005 at 2:59 pm

  #607209

  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.

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  December 2, 2005 at 12:29 am

  #607260

  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

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  December 2, 2005 at 6:54 am

  #607326

  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.