Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: How long to create a non-clustered index?

    I need to know how long it will take to create a non-clustered index given:
    I know how many columns the index will affect, the column width in bytes, how many rows, an average row size, the number of hard disks, their speed in RPM, and they are setup in RAID 5.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    depends on lots of stuff
    “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
    Mar 2003
    Posts
    130
    What else do you need to know?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    there are too many variables. it could be 2 seconds to x number of hours. Hardware. configuration. is the table a terabyte in size or a megabyte in size. Is the column an int or varchar etc...(numbers process faster), what is already happening on the machine. Are the moons of saturn in alignment. is there a MLB conspiracy to have the cubs win the series this year now that red sox won.

    just do it. if you are worried about what is going to ahppen to the computer, do it when everyone goes home or schedule it as job to run at midnight.
    “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
    Mar 2003
    Posts
    130
    The non-clustered index is created on 10 columns - each 100 bytes wide. The table is 6 X 10^6 rows and average 2000 bytes per row - so 12 X 10^9 bytes total. I have 5 15,000 RPM disks in RAID 5.

    Take everything else out of the picture. I just need a mathematical way to estimate a best case/worst case scenario.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by wey97
    I just need a mathematical way to estimate a best case/worst case scenario.

    No you don't.

    It's a bad idea in the first place

    Don't do it.
    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.

  7. #7
    Join Date
    Mar 2003
    Posts
    130
    Can you explain how you came to this humble conclusion?

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am tiddy bit curious about why anyone would need a 10 column index but I decided to keep my mouth shut.
    “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.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if memory serves (and it often does'nt) multi column idexes only do any good if all of the columns are searched in the same query and are queried in the same order that they appear in the index.

    Seems to me something like this would be a performance hit on inserts and deletes and updates and such.

    Am I close Brett.
    “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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by wey97
    Can you explain how you came to this humble conclusion?

    Let's see, a thousand byte + wide index....

    I would think that would be very ineffecient.

    Also, if you needed anything else from the row, I imagine, the optimizer might choose to ignore it anyway.

    humble, not humble, whatever, it has nothing to do with it.

    It's a bad idea.

    Post the DDL of this table.

    And who told you that you need that index?
    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.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus
    Seems to me something like this would be a performance hit on inserts and deletes and updates and such.


    Ya think?

    Am I close to Brett.
    Let's not start picking out wallpaper or anything...

    But if you're buying...I'll have a 'rita on the rocks....
    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.

Posting Permissions

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