June 24, 2008 at 11:43 am
Friends, I have a issue with a BIG Table, 12 M rows.
I Copied over the table,so the Indexes did not get carried over.
Now when i am trying to Create Clustered INDEX on the Table, It Takes more than 3 hours, is this usual. and is there a FASTER Method or solution to Creating a INDEX on a BIG table.
Syntax for Creating Clustered INDEX is:
ALTER TABLE [dbo].[F0911] WITH NOCHECK ADD
CONSTRAINT [F0911_PK] PRIMARY KEY CLUSTERED
(
[GLDCT],
[GLDOC],
[GLKCO],
[GLDGJ],
[GLJELN],
[GLLT],
[GLEXTL]
) WITH FILLFACTOR = 90
Let me know if you need more info.
Maninder
www.dbanation.com
June 24, 2008 at 11:55 am
I figured out, what the issue might be.. but not sure.
here are the Datatypes for the Columns
[GLDCT] = char
[GLDOC] = float
[GLKCO] = char
[GLDGJ] = decimal
[GLJELN] = float
[GLLT] = char
[GLEXTL] = char
clustered index on a char is not Suggested.
but what about FLOAT/DECIMAL is it recommended or suggested if required.
Maninder
www.dbanation.com
June 24, 2008 at 12:10 pm
I'd suggest to make the primary key NONclustered !
Choose a clustered index that suites your usage ! and is as narrow as possible, preferable unique.
Keep in mind the clustered indexes columns will be added to any other indexes you create on the table.
If you still want to create that PK clustered, order it according to the filterfactors of the participating columns. (PK is unique, so the column order can be tossed around)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 24, 2008 at 12:48 pm
This is large for a clustered key, I suggest that you find a smaller one (on none at all as previously suggested). ReOrdering 12M rows will take a while.
I am not aware of an problem with CHAR's on Clustered keys, unless they are large. Floats are definitely less than desirable.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 24, 2008 at 12:50 pm
The other thing that I would suggest for building Clustered Keys on big tables like this is to make sure that you MDF, LDF and TempDB's are all on seperate physical disks. This can make a huge difference.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 25, 2008 at 1:50 pm
June 25, 2008 at 2:18 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply