Performance question about indexing large amounts of data. I have a large table (~30 million rows), with 4 of the columns indexed to allow for fast searching. Currently I set the indexs (indices?) up, then import my data. This takes roughly 4 hours, depending on the speed of the db server. Would it be quicker/more efficient to import the data first, and then perform index building?
+2
A:
Inserting data while indices are in place causes DBMS to update them after every row. Because of this, it's usually faster to insert the data first and create indices afterwards. Especially if there is that much data.
(However, it's always possible there are special circumstances which may cause different performance characteristics. Trying it is the only way to know for sure.)
af
2010-10-28 12:46:45
**@af** On what assumptions is your generalization based? I recently tried both, and found it much faster to bulk insert with the indexes in place than to drop and recreate after, which took around 20 minutes longer on a dataset of a few million rows.
Winston Smith
2010-10-28 12:59:31
Yes, this all depends on specific data, order of the rows, and indices. It's entirely possible that while the DBMS has to do more work in inserting stuff row-by-row, if everything is in a right order, DBMS can just write the stuff and never end up reordering data or balancing the index data structures. These situations are usually exceptions, not the norm. It depends. Usually things are not lined up "just right".
af
2010-10-28 14:11:49
+2
A:
It will depend entirely on your particular data and indexing strategy. Any answer you get here is really a guess.
The only way to know for sure, is to try both and take appropriate measurements, which won't be difficult to do.
Winston Smith
2010-10-28 13:01:54
+3
A:
I'd temper af's answer by saying that it would probably be the case that "index first, insert after" would be slower than "insert first, index after" where you are inserting records into a table with a clustered index, but not inserting records in the natural order of that index. The reason being that for each insert, the data rows themselves would be have to be ordered on disk.
As an example, consider a table with a clustered primary key on a uniqueidentifier field. The (nearly) random nature of a guid would mean that it is possible for one row to be added at the top of the data, causing all data in the current page to be shuffled along (and maybe data in lower pages too), but the next row added at the bottom. If the clustering was on, say, a datetime column, and you happened to be adding rows in date order, then the records would naturally be inserted in the correct order on disk and expensive data sorting/shuffling operations would not be needed.
I'd back up Winston Smith's answer of "it depends", but suggest that your clustered index may be a significant factor in determining which strategy is faster for your current circumstances. You could even try not having a clustered index at all, and see what happens. Let me know?
Neil Moss
2010-10-28 13:13:30