|
The external
sets that an import task uses may have one or more import task set
virtuals. These are fields you define in the external set, which do
not exist in the file to be imported. You can use them for storing
intermediate values during the import, for example, for computing
values on the basis of other fields in the external set. Import
task set virtuals can only be used within external sets that are
not table-based.
The virtual element receives
its value from the import SQL statement defined for the particular
import task. For each virtual element that you want to use, you
must specify the name, data type and length.
NOTE:
The performance of USoft Batch can be much improved if you use
virtuals rather than constraints to compute values on the basis of
external set elements during import tasks.
To take an example, you can
use import task set virtuals to improve the performance of foreign
key generation for imported child data. Suppose you have an import
task in which the import SQL statement needs to join with the
database table PERSON to retrieve the newly generated primary key
value for the parent record.
If there are many children for
each parent, you can improve performance by retrieving the primary
key value once for the entire set of children and storing it in a
virtual column of the parent external set.
NOTE:
In fact, this method is sometimes the best way to handle tables
with automatically generated primary keys, as follows.
Suppose you want to import
parent records with related child records, and the child records
are in a separate file, and related to the parent records through
an ID number. If the table has automatically generated primary
keys, then USoft Batch will write new primary keys for each
imported record, thereby destroying the links between the parent
and child records.
If you retrieve the newly
generated primary key value for each parent record, and store it in
a virtual column, you can then use it for all the child
inserts.
You could also use an
alternate key (assuming that there is some other column in the
child record that uniquely identifies the parent), and fetch the
new key for each parent. But the disadvantage is that you need to
first put the parent in the database, and then query the database
for the key for each child record, and this will not perform as
efficiently as the solution using a virtual column.
|
