Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
agulino
Starting Member
6 Posts |
 Posted - 2001-12-11 : 16:45:44
|
| Need to have a bet settled:Importing a million records into an empty SQL Server table: is it better to delete the index-insert the records-add the index OR leave the index in place during import?Anthony Gulinoagulino@stonesix.comStone Six Solutions, Inc.www.stonesix.com |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-11 : 16:50:03
|
| Is that a clustered or non-clustered index?-------------------It's a SQL thing... |
 |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2001-12-11 : 16:57:24
|
| I've never tried it in SQL, but I know in Informix, there was a huge improvement in dropping the index, importing, and reapplying the index. We were working with 10 million rows |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2001-12-11 : 16:59:08
|
I maybe missing something (and if I am Ilya will nail me as usual  ) but if the table is empty then SQL Server shouldn't have to shuffle a bunch of pre-existing data to balance the index tree so I would guess it would take the same amount of time. I'm assuming that we are talking clustered index since that is what is created by default for the pk.Justin |
|
|
|
agulino
Starting Member
6 Posts |
Posted - 2001-12-11 : 17:09:30
|
| I would be interested in an opinion on both scenarios: clustered and non-clustered.Anthony Gulinoagulino@stonesix.comStone Six Solutions, Inc.www.stonesix.com |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-11 : 18:07:29
|
| Unfortunately it's not that simple. Hardware and disk configurations will affect this, and whether or not there are several non-clustered indexes (and if they're stored on different drives from the data).I would lean towards dropping and then rebuilding, but you may find that keeping the index and loading it will run faster. You'd really have to test each scenario before you can say which is faster (and win or lose your bet). There's no other way to know for sure. |
|
|
|
PiecesOfEight
Posting Yak Master
200 Posts |
Posted - 2001-12-11 : 18:20:39
|
| It depends on how many indexes and the type of index(es), but here's what BOL says under Optimizing Bulk Copy Performance:Additionally, if your table has a clustered index and the data in the data file is ordered to match the clustered index key columns, bulk copy the data into the table with the clustered index already in place and specify the ORDER hint. This is significantly faster than creating the clustered index after the data is copied into the table.If nonclustered indexes are also present on the table, drop these before copying data into the table. It is generally faster to bulk copy data into a table without nonclustered indexes, and then to re-create the nonclustered indexes, rather than bulk copy data into a table with the nonclustered indexes in place. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-11 : 19:08:50
|
quote:
I maybe missing something (and if I am Ilya will nail me as usual )
Justin, I'm actually going to agree with you here!!!  (but don't get used to it -- this ain't gonna last  ). Starting with version 7, SQL Server offers both index-level and table-level data modification strategies. I am pretty sure that regardless of the number/type of indexes in this case it will select index-level strategy. As a result, dropping/re-creating indexes most likely should not yield any performance gain vs simply loading the data straight in. On an older version of SQL Server index-level strategy was not available, so dropping indexes before loading the data was always a recommended approach.Index-level and table-level data modification strategies are explained in Kalen Delaney's "Inside SQL Server" book. And here is her article on the subject (which seems to be taken straight out of the book): http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8031&pg=2Edited by - izaltsman on 12/11/2001 19:10:49 |
|
|
|
|
|
|
|
|
Indexing