# Thread: How long to create a non-clustered index?

1. Registered User
Join Date
Mar 2003
Posts
130

## 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. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
Provided Answers: 6
depends on lots of stuff

3. Registered User
Join Date
Mar 2003
Posts
130
What else do you need to know?

4. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
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.

5. Registered User
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. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
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.

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

8. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
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.

9. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
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.

10. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
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?

11. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
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....

#### Posting Permissions

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