Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    13

    Question Unanswered: Storage strategy

    Hi guys.

    I am currently developing a system thats stores exchange stats in a db. Since our customers are companies with 20 employees up to 5 000 there a a big difference in the volume of data needed to be stored.

    We currently thinking of supplying a SQL Server Express DB to the small customers and suggest a SQL Server to the bigger.

    But since I would like to use the same structure for both types of customers I wonder how should i design the storeage.

    Since the could be from 500 records a day up to 20 000. There are quite simple recordes with only simple datatypes. about 15 fields with no more than 10 chars each, mostly 2.

    Should i separate the data in diffrent tables for a week or a day etc.
    Since I am only going to filter data on 1 or 2 fields the data will be easly indexed.

    The reports generated will almost always only use 1-3 months of data, but historical reports have to be possible.

    My question are ofcourse:
    Whats the best solution for me?

    Thanks in advance

    /Johan Wendelstam
    Sweden

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by wendelstam
    Should i separate the data in diffrent tables for a week or a day etc.
    No.
    How far back do you need to maintain a history? You might consider archiving data more than a year old, for instance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2005
    Posts
    13
    Since the more data there is in the db the more realistic figures will be provided, for example long term trends and things like that, I'm going to store atleast 3 years, maby more, depends on the customers need of statistics.

    But how should i implement that techincally? Have never worked with a db that size before?

    And since the old data only will be used sometimes is the best solution to group it by year or is it enough to have one with the "active" and one big table with all the old?

    Whats the recomended max amount of rows in a table in SQL Server 2005?

    If you have any links to articles concerning this matter dont hesitate to share =)

    Thanks in advance, you really make my work easier=)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, three years is a minimum for certain trending methods, such as Winter's Exponential Smoothing model.

    How often are you going to be running and recalculating trends? I really dislike logical table partioning (by time span). Try to keep your data in one table if possible. SQL Server can handle data up to a terabyte in size, so the scaling is not a problem, though performance may be. The was around performance issues in situations like this is to preaggregate/precalculate some of the data. Does every person loggin on need to recalculate the same trends, or could you calculate them once in a nightly process and store them in a dedicated table?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2005
    Posts
    13
    The reports that cover several years back will probably be run no more than 10 times a year, but they have to be there.
    Most likley the daily reports will only go no longer than 2 months back, since the short term data is quite important as well for the users.

    But is it a good solution to have a table say with the records with a
    "timestamp" that are no more than a year from today in the main table and each night move the obsolete records to the archive table. And set a dynamical parameter that can change the number of days to store in the main table, say only 100 days in a case where the are 20 000 records a day, and maby 365 for a smaller customer?

    But if so, whats the best way to combine these tables when calculating reports?

    If I combine them in a view and Index them so they are in "order" are that a good solution?
    I am open for suggestions=)

    As before, thanks in advance.

  6. #6
    Join Date
    May 2006
    Posts
    16
    use one table enough
    use olap service to build your report (easy and simple way)

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can store production (recent) data in one table and archive (historic) data in another, and then use a UNION query to join them together when necessary. Or you could store a complete and up-to-date set of data in your archive table, plus a subset of the recent data in your production table, which entails duplicating records between tables but eliminates the need for a UNION query.

    But I have to wonder if you are worrying about this too much. You can start by putting all of your data in one table, and as long as you use stored procedures and views to extract your data you can split this data however you want in the future and just modify your sprocs and views to keep from affecting the application.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2005
    Posts
    13
    Your answer sounds like music, no complications.
    The only real need in terms of speed are that the latest months are retrived quick, if a four year report takes some time do make, it doesnt matter.

    The reason why I worry is that the servers will be administrated by our customers and the will be atleast 50 of them in diffrent places so it can prehaps be hard to change in the future(?)

    Thanks alot Blindman and scott.x.lu. You made my work real simple=)

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Go with the plan of storing recent data in your production table, and then nightly or weekly pare off old data to an archive table. Then use a UNION query to combine both tables for your periodic reports and store the results of those calculations in separate tables. I think this will give you the best performance across all of your clients.
    Because this appears to be an transaction processing system rather than a datamart, I would avoid duplicating individual records across two tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2005
    Posts
    13
    [QUOTE=blindman]Then use a UNION query to combine both tables for your periodic reports and store the results of those calculations in separate tables. QUOTE]

    Do you meen that I should perform calculations for the "archived" data and store the result in a table and access the old reports through that table.

    What confuses me are that since the diffrent reports are filtered by alot of parameters then it seem like it would be hard to store "results", or do I misunderstod your answer?

    Is it a bad idea to do the calculations on the archive table directly, is that a bad practice for the future?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Record daily transaction in your production table, and run your short-term queries against this table.

    Every night, move old records from your production table to your archive table.

    Create a view which combines both tables using UNION:
    Code:
    create view FullData as
    Select [ColumnList] from ProductionTable
    UNION
    Select [ColumnList] from ArchiveTable
    Run your large periodic queries against this view, and store the output in "results" tables. Index the results tables and filter them as necessary when they are accessed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •