| Author |
Topic |
|
duhaas
Constraint Violating Yak Guru
310 Posts |
 Posted - 2008-02-19 : 10:47:25
|
| I currently have a database thats 25GB in size, I load information on a daily basis (550,000 records/day). My table design is the followingTable Name: PositionsColumns: AccountID - PK - Varchar 50 SecurityID - PK - Varchar 50 position_date - PK - Datetime quantity - Decimal(18,5) security_price - Decimal(18,5)The table currently has 66 million rows in it after about 6 months of time. My question revolves around things I should make sure I do maintain decent performance. I have built several indexs on the database, and queries run ok as of right now, but my fear is as it continues to grow @ its current pace whats going to happen. Tried actually running a dbshrink, and the job eventually failed after a couple hours. Any help you can provide would be grealy appreciated. In looking @ it, I should probably reduce the VarChar(50) to more accuratly reflect the typical size of that field (example accountid(9) securityid(10). I would assume making that change should help with reducing the size going forward a little bit??? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-19 : 10:49:58
|
| Have you thought in terms of partitioning the table? |
 |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2008-02-19 : 10:53:01
|
| To be honest no, not too familar with the topic, any sites you can point me to would help. When you say partition are you talking about spreading the DB over multiple files???? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-19 : 10:58:26
|
| i meant horizontal partitioning of table based on a field, say your datefield. if you are mostly interested in only a subset of data (after a particular period) , you can create an index on only that partition of table.http://weblogs.sqlteam.com/dmauri/archive/2005/07/18/7147.aspxhttp://www.sqljunkies.com/article/f4920050-6c63-4109-93ff-c2b7eb0a5835.scuk |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-19 : 10:59:57
|
quote:
I should probably reduce the VarChar(50) to more accuratly reflect the typical size of that field (example accountid(9) securityid(10). I would assume making that change should help with reducing the size going forward a little bit???
Actually no. Since the column is VARchar rather than CHAR, reducing the limit will have no effect on the space used (assuming there are no changes to the existing values).Be One with the OptimizerTG |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2008-02-19 : 11:03:52
|
| WOW, I appreciate the quick responses and you guys have given me plenty of information to start looking @. |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-19 : 11:45:49
|
| Are the IDs REALLY varchar data? If not, use int. If they absolutely must be varchar, changing the varchar size does not affect storage size. From BOL, "The storage size is the actual length of data entered + 2 bytes."http://msdn2.microsoft.com/en-us/library/ms176089.aspxCan position_date be a smalldatetime?http://msdn2.microsoft.com/en-us/library/ms187819.aspxIs quantity going to really be up to 18 digits? Decimal(18, 5) is #############.##### and takes up 9 bytes. If you don't really need that much, you could go with Decimal(9,5) which would give you ####.##### or Decimal(9, 3) which would give you ######.###. If you can go with a Decimal(9,x) it only takes 5 bytes. At 66m records, just those 4 bytes on the quantity field saves you 257mb If you can do it for both decimal fields, that is 514mb. If you can switch to smalldatetime, you would be at 771mb. If you can make the IDs int, you would save 5 and 6 bytes (according to your typical size) for a grand total of ~1,482mb. These are just estimates based on the info you've provided. Your indexes will also be smaller if your data types are smaller. Can you archive or summarize old data after some period? What is the fill factor on your indexes? Are you making updates or just inserts? Are your inserts at the end of the table or somewhere in between? If the order of your index puts the new records at the end, you will not be page splitting and fragmenting your indexes on insert. For example:Inserting:111111113, 2222222223, 20080102To the table with the following records:AccountID, SecurityID, position_date111111111, 2222222222, 20080101111111112, 2222222223, 20080102111111114, 2222222224, 20080103 <-- This record (and following records) in the index MAY have to be moved on disk for the new record to be inserted. It CAN cause page splitting and index fragmentation depending how full the pages are.If this is the case, you need to make sure that your indexes have the correct fill factor. The default is 0 meaning that the indexes fill the pages to the max. If you are inserting new records at the end of the index, you are fine because the record will go at the end and a new page will be created if needed. If not you will most likely cause a page split.You can read about how Fill Factor works here:http://msdn2.microsoft.com/en-us/library/ms177459.aspx |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-19 : 12:16:08
|
| Ok I see Where you are.Table partitioning is best solution for this issues. It lets your performance to go up and you can put partition in seperate disk in different filegroup. You can switch out the unwanted partition. Partition according to date.Microsoft has good white paper on Table partitioning. |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2008-02-19 : 12:58:28
|
| Once again you guys rock |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2008-02-19 : 13:03:37
|
| The small datetime I am going to change cause it suites my needs, question is does it free up that space as soon as I alter the table |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2008-02-19 : 13:23:38
|
| Another question would be what command can i use to look @ the db stats before and after i alter the datetime column to see the true difference in size |
|
|
|
|
Just throwing it out there....