[dba-SQLServer] Add records to large file

Francisco Tapia fhtapia at gmail.com
Fri Dec 11 13:07:10 CST 2009


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
>



More information about the dba-SQLServer mailing list