Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2006
    Posts
    7

    Unanswered: Writing 1mill records to MSSQL 2000 per day

    Hi,

    Has anyone worked on a system that needs per day around 1 Million writes, almost that many reads and deletes once every 1-2h. File sizes range from 2KB-1MB only that 85% of the files are in the 1-8KB range.

    This is all done in a single table with a GUID key.

    If yes could you tell me what problems did you encounter, any suggestions etc.

    Thank you in advance

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    be sure to have an unique index on your guid.

    managing backups and indices become more complicated in high volume systems. reporting too becomes an issue once your database gets real large and you want to start thinking about OLAP or whatever they are calling it this year.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2006
    Posts
    7
    Hi

    why do you think i need a unique index on the guid? isn't that unique enough. I don't need to store the data or to backup it. I delete everything that is more than 2h old. Its for persisting viewstates in DB. so its safe to assume that after 2h i don't need the records.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    forget all of the stuff I said about reporting, indices and backups then.

    however, and i have seen this happen, and they do hint at it in Books Online that a uniqueidentifier column is not neccessarily unique without a unique index. This of course is taken care of if this is your primary key.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A unique index allows the SQL Engine to make different choices in how it looks for things, and iproves the chances of getting a seek versus a scan. A formally declared PRIMARY KEY actually allows even better choices. They will help with performance.

    There are quite literally thousands of possible problems, but nearly all of them can be worked around if they happen to occur. Be sure to use both the SQL Profiler and the index wizard on your development database and on your staging database too. At 1M/hr you won't hit serious performance bottlenecks, but as you scale up you might, especially if you scale up quickly.

    -PatP

  6. #6
    Join Date
    Dec 2006
    Posts
    7
    At 1M/hr you won't hit serious performance bottlenecks, but as you scale up you might, especially if you scale up quickly.
    i have 1M/day not hour so i guess that should work fine?

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it seems to me if you are using uniqueidentifier as your pk column, you will have fragmentation issues, because newids will fall randomly throughout your index, causing page splits.

    in 2000, you can avoid this with Gert Drapers' xpguid.dll library.

    in 2005 you can avoid it by using newsequentialid().

  8. #8
    Join Date
    Dec 2006
    Posts
    7
    i need the guid to send it as a ticket to the user so that the viewstate can be retrieved later from the db. on the other hand if i give them a sequential id then one could read other's viewstate because the viewstate's tickets would be in a sequential order.
    it says in the xpguild.dll also that:
    "If privacy is a concern, do not use this function. It is possible to guess the value of the next generated GUID, and therefore access data associated with that GUID."

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that's true about sequential guids and privacy.

    you'll have to just deal with the fragmentation then, with an appropriate fill factor on the index pages.

  10. #10
    Join Date
    Dec 2006
    Posts
    7
    Quote Originally Posted by jezemine
    that's true about sequential guids and privacy.

    you'll have to just deal with the fragmentation then, with an appropriate fill factor on the index pages.

    any more detailed suggestions?

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    maybe rebuild the index during your maintenance window.

  12. #12
    Join Date
    Aug 2005
    Posts
    6
    Who says you can only have 1 key column?

    Keep the GUID and make a CLUSTERED INDEX using an IDENTITY column... the data wouldn't become as fragmented chronologically which will speed up deletes. It wouldn't have to be a UNIQUE index because IDENTITY is already unique and you have to go out of your way to insert something into an IDENTITY column.

  13. #13
    Join Date
    Dec 2006
    Posts
    7
    Quote Originally Posted by Jeff Moden
    Who says you can only have 1 key column?

    Keep the GUID and make a CLUSTERED INDEX using an IDENTITY column... the data wouldn't become as fragmented chronologically which will speed up deletes. It wouldn't have to be a UNIQUE index because IDENTITY is already unique and you have to go out of your way to insert something into an IDENTITY column.
    wouldn't that make SELECT slower since i would be searching using the guid but have the IDENTITY column as the primary key?

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Jeff means make a composite key out of the int identity and the guid.

    If you only lookup by guid though, my instinct is that the index would not be used, because it would not be ordered by the guid.

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by jezemine
    Jeff means make a composite key out of the int identity and the guid.

    If you only lookup by guid though, my instinct is that the index would not be used, because it would not be ordered by the guid.
    Maybe that could be remedied using a non-clustered index built on the clustered index?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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