You can
remove non-essential indexes before a load and recreate those indexes
after the load. In some situations, this procedure improves performance.
As a general rule, consider removing and recreating indexes if more
than 10 percent of the data in the table requires reloading.
You might also want to temporarily remove key constraints in order to improve performance.
If you remove constraints from the
target before the load, then you remove the overhead of maintaining those constraints. If
you are loading a significant number of transactions with data that conforms to the
constraints, then removing the constraints should improve your performance.
To control
the timing of index and constraint removal, use the options that are
available on the Load Technique tab of the
Table Loader transformation. The following settings are provided to
enable you to specify the desired conditions for the constraints and
indexes before and after the load:
-
the Before Load field in the Constraint Condition group
box
-
the After Load field in the Constraint Condition group
box
-
the Before Load field in the Index Condition group box
-
the After Load field in the Index Condition group box
The options
that are available depend on the load technique that you choose. The
choices translate to four different tasks: put on, take off, leave
as is, or recreate as is. When you select Off for the Before Load options, the generated
code checks for and removes any indexes (or constraints) that are
found. Then, it loads the table. If an index is required for an update,
that index is added or not removed as needed. Select On for the After Load options to have indexes
added after the load.
In some
situations, you might select Leave Off in
the After Load field to leave the indexes
off during and after the table loading for performance reasons. One
scenario is when the table is updated multiple times in a series of
load steps. Indexes are defined on the table only to improve performance
of a query and reporting application that runs after the nightly load.
None of the load steps need the indexes, and leaving the indexes on
impedes the performance of the load. In this scenario, the indexes
can be taken off before the first update and left off until after
the final update.