Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scullee
Posting Yak Master
103 Posts |
 Posted - 2003-05-01 : 21:05:03
|
| I have a problem with a design i am looking at.We currently receive SMS's from cars and the system we are replacing has about 2,000,000 SMS's to and from cars in the last 2 years. We are seeing about a 20% growth of this per month so this figure is growing very very rapidly. We have a table here that stores all of the sms's received for logging and this data has to be accessable for a few years (data might be used in court). This is where the problem comes about. I can store the SMS's without a problem but inside each of these SMS's contains a variable list of status data. The number of pieces of data average between 30 and 50 different pieces for each SMS (And there is about 120 current possible values). I need to store this data but i am a little afraid of creating a table to store each of these values as a separate record. As you can see we will have a massive number of records in this table.I am lucky in that we dont need to sort or search on this data. We only need to access it when we access the parent record for display. I know in a perfect world i should normalise this into the separate table but i think it will just get too big to manage. I am thinking of creating a text data field in the sms table and just storing the status data in a csv or xml grid inside the field.What do you all think? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-01 : 21:25:17
|
| I don't think storing the status values in one column is a good idea, because it'll be more difficult to search and index.How long are the status codes/data pieces? If they're only 10-12 characters, or shorter, much better to store them separately, one row per status. You'll have a nice, skinny table that'll store a lot of rows per page, and you might just get by with only 2 or 3 columns in the table. Easy to index and search. You're gonna have millions of rows anyway, and you'll want to have small, well-indexed rows that SQL Server can query easily, even if it costs you a little bit of extra space.It would help to post some sample data and how you'd plan on storing it, we might be able to give better advice. |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-05-01 : 22:03:24
|
| To back up Rob's comments...The number of rows should not be your primary concern. Worry about usability. Are you really sure that a future requirement will never be to search on this data? You concern here is more of administration.. Disk space, Backups etc... Consider multiple file groups and files and partitioningWith a good index, SQL Server hardly nows the difference between 1000 and a million rows..DavidM"SQL-3 is an abomination.." |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-05-01 : 22:14:37
|
| scullee - can't help but wonder are you from fuelwatch.com.au ??Since you don't need to do much with the data other than store it with a reference to the "person/user/customer" then why not microfiche? (just kidding!)In a given string of data, are the status codes on/off bits or characters or something? It sounds interesting and I'd like to see some details if you can post them.Ultimatley I think you should aim for a design that meets your requirement now (ie: no search/sorting) but will allow extraction/conversion to a normailsed structure in future if/when the requirements change. |
|
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2003-05-01 : 22:32:20
|
| I think the problem is that im not talking about a million rows. We are expecting about 2-4 million SMS's in the next year and whole lot more after that (about 200% growth per year) then 30-50 status records per sms which is a whole lot of data. Doesnt there get a point where SQL really doesnt like that amount of rows?As for the data, sometimes its a bit, sometimes a date, sometimes a string and even some numbers just to make it more annoying. As for searching and sorting on the data, its a tough call. I doubt we will ever search on it because we extract the stuff we need (ie location of the car etc) and store and use that data somewhere else before we log it. I would love to store it separatly in a table but the thought of more than 160 million+ records per year scares me. I dont really care about disc space as this is cheap.And Tim, not its not fuel watch, its another australian company that puts stuff in cars. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-01 : 22:55:35
|
quote:
I dont really care about disc space as this is cheap
That was kind of my point. What's easier to manage if you had to manage it: one huge monolithic chunk of data or several smaller pieces? You're gonna end up using about the same amount of SPACE regardless, why not make that space easier to handle? If anything, you'll make better use of the space you have because, being smaller, the rows will more completely fill the pages than one large column of text will, therefore the data will fragment less, therefore you'll have more efficient I/O. Also, it's a lot easier to put smaller pieces together to make a bigger piece, when needed, than it is to slice a large piece into smaller chunks when needed.As David said, if it's indexed right SQL Server will slice through 1 million rows just as easily as a few thousand. 100 million will only be the slightest bit more work. You have to remember that a set based operation treats the set (or sub-set, depending on how you look at it) as ONE UNIT, and treats that set as if NOTHING ELSE exists. You'll never be searching the entire table unless you want to return all of the rows anyway. It'll find what it needs in the index and *ping* it'll just go there and get the data. Just like flipping through a dictionary, couple hundred thousand words, and you can find any word with only a few page flips. And you've indicated that it's not something you'll be searching all that often either, so the performance doesn't have to be screaming. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
|
|
|
|
|
Design Question (Lots of Rows)