October 12, 2011 at 11:32 pm
What options do I need to consider to make this pk creation go as fast as possible?
ALTER TABLE Cevent ADD Ceventpk INT IDENTITY
Go
ALTER TABLE Cevent ADD CONSTRAINT PK_Cevent PRIMARY KEY(Ceventpk)
can compress or partitioning help me?
Today it takes 9 minutes. I would like to have it take half or less time if possible.
October 13, 2011 at 1:13 am
Compression may help, you need to test it, it will depend very much on the rest of the table definition and how compressible it is. More details on table structure, typical data and volumes would help. Compresion can have impact elsewhere as well of course.
For what you are doing you need to work out what the limiting factor is, and I would guess with those queries it is probably disc io.
So:
Logs on different disc to data tables
Tempdb on different discs to real db and logs
tempdb logs on different discs to tempdb data
Faster dics (15k rpm SCSI)?
optimum raid level (raid 5 slow for writes so not great for logs)
Is disc local or san?
And of course:
Is there a clustered index already on the table? If so what fillfactor. Adding the column will cause each row to grow, so depending on current fillfactor and row length the stoirage engiune could be moving rows to new pages a lot. Lower fillfact on an existing clustered index would help.
Which of the two queries takes longest?
Mike John
October 13, 2011 at 10:13 am
Thank you for the reply. I am not in a position to test all the things atm. I'll try to get back with empiric studies on the matter later on.
October 13, 2011 at 2:19 pm
Might it be that you don't have a clustered index on the table already? Primary keys are by default clustered unless you specify nonclustered. Creating a clustered index on a large table will cause a lot of IO (essentially a full table scan and writing the whole table to new pages). Creating a nonclustered primary key instead would possibly speed up the creation, but that may not be what you want.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply