April 4, 2014 at 1:12 pm
Hello,
We have a table with records of > 46081427 , and as part of our performance fine tuning we have altered the composite primary key order/sequence in the primary Key and hence its doing a Index Rebuild, as we are using VSTF incremental script its generated automated script for doing this.
1. Create a temp table with new rearranged primary key but primary key name as temp table convenient
2.insert into temp table from original table and order by new primary column re arranged
3.Drop the original table
4.Rename the temp table back to original name
5.Rename the temp table constraint back to original name
As this is taking > 30 MINUTES to do it..is there any other better method than this.
April 7, 2014 at 6:12 am
You could try doing the insert into the new table without having the key in place and then apply the key after the data is loaded. In some situations you can see a pretty substantial performance improvement from that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply