Import Design Question

I have at least three tables into which the user can import data.
The three tables are 1) Portfolio 2) Account 3) Transactions. The user
will provide us three files - one for each entity (in the file) related
by a userdefined NUMBER field.
These three tables are linked by foreign keys but the key columns in
the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
TRANSACTIONID).
The files can be really big so the import has to be quick. Right now I
first insert the rows into PORTFOLIO, get the generated IDs , loop
through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
ID and then import the TRANSACTIONS. This process of retreiving and
setting the IDs is consuming a long time and making the import very
slow. I am wondering if using IDENTITY is the right design for the
above mentioned tables. Is it better to generate the key values myself
using a SEED table?
Is there a more elegant solution to the problem?
Thanks.Looping? In SQL?
Don't you have any natural keys in the source files? If there is a natural
key (or at least one or more candidate columns) use that to achieve a
set-based solution.
Whether you use identity or any other kind of key generation is IMHO
irrelevant, since there really should be a more natural way to uniquely
identify each row of data (i.e. a natural relationship between the sets).
ML
http://milambda.blogspot.com/|||S Chapman wrote:
> I have at least three tables into which the user can import data.
> The three tables are 1) Portfolio 2) Account 3) Transactions. The user
> will provide us three files - one for each entity (in the file) related
> by a userdefined NUMBER field.
> These three tables are linked by foreign keys but the key columns in
> the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
> TRANSACTIONID).
> The files can be really big so the import has to be quick. Right now I
> first insert the rows into PORTFOLIO, get the generated IDs , loop
> through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
> ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
> ID and then import the TRANSACTIONS. This process of retreiving and
> setting the IDs is consuming a long time and making the import very
> slow. I am wondering if using IDENTITY is the right design for the
> above mentioned tables. Is it better to generate the key values myself
> using a SEED table?
> Is there a more elegant solution to the problem?
> Thanks.
>
Sounds like your relationships are like this:
Portfolio -> AccountID -> TransactionID
meaning a single portfolio links to multiple Account ID's, a single
account links to multiple Transaction ID's.
You might consider changing this. Look for natural keys to link on
instead of manufacturing one (using an ID value). For instance, a
transaction should link to an account via ACCOUNT NUMBER. A portfolio
should contain multiple ACCOUNT NUMBERS, not account ID's.
What this will allow you to do is to bulk import all three tables
without having to mess with finding ID's, updating ID's, etc. The data
is already naturally linked together.|||Unfortunately there are no natural keys. The Portfolio Number and
Account Number can be gauranteed to be unique only within a batch.
Also, I am NOT looping through in Sql, looping through rows in the
dataset inside the program.
Tracy McKibben wrote:
> S Chapman wrote:
> Sounds like your relationships are like this:
> Portfolio -> AccountID -> TransactionID
> meaning a single portfolio links to multiple Account ID's, a single
> account links to multiple Transaction ID's.
> You might consider changing this. Look for natural keys to link on
> instead of manufacturing one (using an ID value). For instance, a
> transaction should link to an account via ACCOUNT NUMBER. A portfolio
> should contain multiple ACCOUNT NUMBERS, not account ID's.
> What this will allow you to do is to bulk import all three tables
> without having to mess with finding ID's, updating ID's, etc. The data
> is already naturally linked together.