Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP Job
This sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>