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 |
|
MargusJ
Starting Member
1 Post |
 Posted - 2010-03-13 : 16:01:03
|
| Hi,I'm taking over one solution witch is not working very well and it haven't maintained systematically.The plan is to create new table structure/logic and I would appreciate Yours help here.Input for planning:data from network, what is need to insert into db, comes like:"date", "parameter name", "parameter value"same parameter name, but might be different value, comes approx. after every 3-5 sec. (197 different parameter names)there's existing application, what inserts this data as one row into db.that kind of rows comes approx. 2,5 to 3,2 mil. per each day.Indexes are built on column date and parameter name.Problem:Business wants to get every kind of online and calculated data based on this raw data, so we need to insert all sent data.And if there are more that one week data in one table, queries are getting very slow. (online queries, what should run like once after 30 sec)also calculations are running from cron, to calculate one hour average, one day average, etc data.My idea:create one table, with same indexes, but hold there only last day + current day data.copy every night now-2 day data to another table. then i can keep so called online table max 6 mil rows and then online queries should be very ok.second table is for week analyze, also with same indexes, and every night i will copy older than now-7 day data to month table.month table would be for room saving purpose without indexes and it's actually only archiving.Questions:1. how my proposed solution feels?2. how would be best way to organize nightly coping process?with thanks.Margus |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-13 : 16:50:53
|
| That's only ~35 rows per second being inserted, which isn't massive. To support this type of system, you need good hardware (CPU, memory, and I/O), proper indexes and a well thought out database design. I wouldn't have a table that stores just two days of data. SQL Server can support very large tables efficiently. I'd consider partitioning your data instead, perhaps by month. Have you looked into table partitioning?Could you describe your hardware? How are your disks setup? Do you have separate disks for your data files, transaction logs, and tempdb? Is tempdb optimized with one data file per CPU?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
|
|
|
|
|
DB structure for massive data inserts every day