How to improve slow query - Hardware perspective part 2 (forget the hardware)

[GPO]

On 27 June I posted asking for advice on why a query would be running very
slowly and yet at the same time, not troubling the CPU greatly. I still
don't know the answer to this question, but I have done some experimenting
and have achieved some dramatic results.

Method 1:
Use SQL INSERT INTO to append (in this instance) 1.6 million rows and 26
columns of data from a text file (as a linked table) to a highly indexed
table (24 indexes).
Results:
Time taken : 493 mins and 57 secs

Method 2:
Same as method 1 but with all the indexes removed from the table.
Time taken: 88 seconds!
Time taken to reload the indexes using ADOX Indexes.Append: 336 seconds.
Total: 7 mins 4 secs.

Clearly there are times when it is not appropriate to append to an indexed
table, but better to apply the indexes afterwards. I would be grateful for
others experience.

GPO

 

[John Nurick]

I've always assumed that this would be the case, so it's good to see
confirmation. One thing to test: try wrapping the entire
import-to-indexed-table in a transaction. This may allow the database
engine to defer updating the indexes until all the data has been
imported.

 

[John Vinson]

Clearly there are times when it is not appropriate to append to an indexed
table, but better to apply the indexes afterwards. I would be grateful for
others experience.

Absolutely correct analysis!

I once worked on a large (for its day <g>) Oracle application; when we
did batch updates, we'd drop the indexes, update the table, and
recreate the indexes.

I believe that the logic is straightforward: when you're Appending
data to an indexed table, Access (or whatever engine) must add the
record to the Table, and then update each of the indexes. An index is
a complex tree structure, not just a sequential file; sometimes adding
a single value to an index requires that thousands of records be
rewritten. If this happens repeatedly during a bulk addition - as it
will - then reorganizing the indexes will end up taking lots of time.

Dropping and rebuilding the indexes puts all the effort of
reorganizing the indexes in one place, and it need only be done once.

John W. Vinson[MVP]