Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Posts
    88

    Question Unanswered: updating daily information in a history table (was "Help-Brainstorming")

    Hi everyone,
    I have a big table which contains approx. 31,524,044 rows. The structure of the table look like this:
    date ID A B C D
    1/1/65 X Null Null Null Null
    1/4/65 X 1 2 3 4
    ...
    2/25/05 X 2 3 4 5
    1/1/65 Y Null Null Null Null
    1/4/65 Y Null Null Null Null
    ...
    2/25/05 Y 2 3 4 5
    ...

    The number of distinct(ID) is approx 3200 and each one has daily historical A, B, C, and D back for 40 years. For going forward I need to update daily information for 3200 ids. Currently, I am runing query against to this table is ok. I am thinking by the time pass by the table will be hudge since "stored historical information". It probably takes "long long long" time to run the query against this table. Any suggestion or comments... what is the best/better solution? Or it is not problem at all?

    Thank you everyone for the help.
    shiparsons

  2. #2
    Join Date
    Feb 2005
    Posts
    8

    ..

    Is the query you are talking about which will take a "long long long" time one which appends data, updates data, or selects data? If you're just appending it probably does not make a big difference depending on whether indexes must be updated. If you are updating or selecting records it could affect the speed of the query depending on how the table is (or isn't) indexed.

    Why not split the historical table up into several historical tables according to date? It's probably not necessary to have all 40 years of data on one table. That way when you're only working with the most recent data in a physical table. If you have a need to select across the entire history you can UNION all of the historical tables together in a view.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, it make a difference whether you are adding 3200 records or updating 3200 records.

    Either way, with indexes this shouldn't take too long to do.

    And 40 YEARS WORTH OF DATA?

    What kind of project are you working on that you have data going back to the 1960s? Are you migrating punch cards?
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Feb 2005
    Posts
    88
    Sort of.
    I do have index created on this table, DateID which is combined two fields- Date and ID. I need to update this table daily which means it will grow 3000 rows, 27,000(3000*9 fields) records per day. Should not I worry about it since I have index created on this table?

    Now I just run into another problem which I posted ten minutes ago. Please review.

    Truely appreciate any help.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you should either have ID indexed separately, or have it as the first component of your composite index, not the second.

    Try it and see if it improves performance.

    What kind of response times are you getting right now?
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2005
    Posts
    88
    I did not see any difference since (the number of ID=the number of date) both ID and date has duplicates. only the combination of DateID makes unique records.
    Does it make sense?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It doesn't have to be unique to be an index, just to be a primary key.

    You can have (practically) as many indexes on a table as you want, but only one of the can be the primary key, and it has to be unique.
    Last edited by blindman; 02-25-05 at 16:36.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And can not contain any nulls
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2005
    Posts
    88
    You are right. I have defined this table to not allow nulls on these two fields Date and ID.

Posting Permissions

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