Michael Maddison
michael at ddisolutions.com.au
Thu Nov 1 18:42:37 CDT 2012
Guys, IIRC you can disable indexes before insert and enable after. I don't recall the command but it's there somewhere :-) Cheers Michael M -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Friday, 2 November 2012 6:30 AM To: 'Discussion concerning MS SQL Server' Subject: Re: [dba-SQLServer] SQL Log file usage As far as I know, it is always faster, a lot faster, to insert data into tables without indexes or keys. (Knew a system that took a week to insert all the data but removing the keys first and inserting the data took less than 3 hours) Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, November 01, 2012 11:22 AM To: Access Developers discussion and problem solving; Sqlserver-Dba Subject: [dba-SQLServer] SQL Log file usage I attempted to insert 150 million records into a table, from one database to another. The destination table was empty but had a bunch of indexes on it. I ran out of room on the log file disk and the process hung. I ended up stripping off all of the indexes except the PK clustered index and the insert used less than 1/2 of the previous log file size for the insert. Furthermore the build of the indexes as a second step is essentially not using the log file at all. So the whole argument about "which is faster" also needs to include a component about log file usage. I have no idea which would have been faster - with or without the indexes already in place - but in this case at least stripping off the indexes and building them after the fact seems to make the whole process work without a (log file size) hitch. -- John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it _______________________________________________ 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