The initial load of 400 million rows into a new database is running far slower than planned. Every target table already has all of its secondary indexes built and its constraints enabled. Which approach is most appropriate for the load?
Select an answer to reveal the explanation.
Short Explanation
Every index is another notebook the database must update for each row it writes. For a big one-time load, you set the extra notebooks aside, load the data, then rebuild them in one pass. Just don't forget to check the data afterward, since you relaxed the checks on the way in.
Full Explanation
During a bulk import, each enabled secondary index must be maintained for every inserted row, multiplying the I/O and page splits per row. The common practice is to disable or drop nonessential indexes, and sometimes to defer constraint checking, load the data in large batches, then rebuild the indexes in a single sorted operation that is far cheaper than incremental maintenance. Because constraints were relaxed during the load, the data must then be validated, for example by re-enabling constraints with checking, verifying row counts and checking referential integrity, before the system goes live. Adding more indexes increases the per-row maintenance cost and makes the load slower, not faster. Loading row by row in separate transactions adds commit overhead and round trips for every row; bulk loads are faster with set-based batches sized to balance log usage and restart cost. Permanently disabling logging, where an engine allows it at all, removes the ability to recover committed work after a crash and would undermine durability for production use. Exam caveat: the primary key or clustered index is often kept, and data loaded in its order avoids a full re-sort. Operational check: time a sample load with and without secondary indexes, and verify row counts and constraint checks after rebuilding.