jwcolby
jwcolby at colbyconsulting.com
Fri Dec 11 13:29:32 CST 2009
Thanks Francisco. I went ahead and did it with the index in place and it finished reasonably fast. I am guessing around 1/2 hour though I didn't time it. I kinda figured it would be many hours. All these years I have been working with this huge table on the heap. With luck this will have a positive impact on future operations. John W. Colby www.ColbyConsulting.com Francisco Tapia wrote: > Ime I've had better performance with the latter solution when the > records are already in place. That's because when you sort a table if > it's too big to go in memory then the sort occurs in the tempdb. If > you have the tempdb on flash drives then maybe the sort would be > faster. Ymmv > > Sent from my mobile > > On Dec 11, 2009, at 8:08 AM, jwcolby <jwcolby at colbyconsulting.com> > wrote: > >> I don't have a clustered index on my database from hell main table, >> the 50 million record 600 field >> table. >> >> I am going to do that. What I thought I would do is simply make a >> new database, create a new table, >> and append the existing data into the new table. There is a unique >> integer PK which will be the key >> of the index. >> >> My question is whether it is faster to create the clustered index >> and then insert the records, >> already sorted in PKID, or create the table and then create the index. >> >> Logically it seems that it would be faster to do the insert sorted >> with the index already in place, >> but I have heard opinions that it is "often faster" to drop the >> index and recreate", i.e. do the >> index after the records are in place. >> >> -- >> John W. Colby >> www.ColbyConsulting.com >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >