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.

Implicit Transations and Deleted Rows

I have a problem where records a being deleted via implicit transactions (I
can see this from the SQL log). I am not sure I understand implicit
transactions fully but no where in our database or application do we ever
delete these records. Why are they being deleted via implicit transactions?Martin,
What do you mean, you can see this from the SQL Log? What is the exact
message in the sql error log?
Can you run a profiler trace on your SQL Server to see what/who is actually
sending these commands?
Do you have replication set up on this server (there is a thing known as
compensating deletes that sometimes surprises people)?
Set up a profiler trace, and make sure to capture sp:starting/completed,
stmt starting/completed, batch starting/completed, rpc starting/completed.
Make sure to include the columns loginname, hostname, etc. Of course you
will want text data and all the regular columns. If you've never set this up
before and you still have questions, post a message here and we can go
through this together.
Donna
"Martin" wrote:
> I have a problem where records a being deleted via implicit transactions (I
> can see this from the SQL log). I am not sure I understand implicit
> transactions fully but no where in our database or application do we ever
> delete these records. Why are they being deleted via implicit transactions?

Implicit Transations and Deleted Rows

I have a problem where records a being deleted via implicit transactions (I
can see this from the SQL log). I am not sure I understand implicit
transactions fully but no where in our database or application do we ever
delete these records. Why are they being deleted via implicit transactions?Martin,
What do you mean, you can see this from the SQL Log? What is the exact
message in the sql error log?
Can you run a profiler trace on your SQL Server to see what/who is actually
sending these commands?
Do you have replication set up on this server (there is a thing known as
compensating deletes that sometimes surprises people)?
Set up a profiler trace, and make sure to capture sp:starting/completed,
stmt starting/completed, batch starting/completed, rpc starting/completed.
Make sure to include the columns loginname, hostname, etc. Of course you
will want text data and all the regular columns. If you've never set this u
p
before and you still have questions, post a message here and we can go
through this together.
Donna
"Martin" wrote:
[vbcol=seagreen]
> I have a problem where records a being deleted via implicit transactions (
I
> can see this from the SQL log). I am not sure I understand implicit
> transactions fully but no where in our database or application do we ever
> delete these records. Why are they being deleted via implicit transactions?[/vbcol
]

Implicit Transations and Deleted Rows

I have a problem where records a being deleted via implicit transactions (I
can see this from the SQL log). I am not sure I understand implicit
transactions fully but no where in our database or application do we ever
delete these records. Why are they being deleted via implicit transactions?
Martin,
What do you mean, you can see this from the SQL Log? What is the exact
message in the sql error log?
Can you run a profiler trace on your SQL Server to see what/who is actually
sending these commands?
Do you have replication set up on this server (there is a thing known as
compensating deletes that sometimes surprises people)?
Set up a profiler trace, and make sure to capture sp:starting/completed,
stmt starting/completed, batch starting/completed, rpc starting/completed.
Make sure to include the columns loginname, hostname, etc. Of course you
will want text data and all the regular columns. If you've never set this up
before and you still have questions, post a message here and we can go
through this together.
Donna
"Martin" wrote:

> I have a problem where records a being deleted via implicit transactions (I
> can see this from the SQL log). I am not sure I understand implicit
> transactions fully but no where in our database or application do we ever
> delete these records. Why are they being deleted via implicit transactions?

Implicit transaction

Can Implicit Transactions be set for the Data Base and not for every query in
SQL Server? For a Data Base, how do we set that and how do we verify the
existing setting .
Ven
There is not databse option for this, but you do not have to set it for every
statement, you set it for the connection and it remains in effect until the
connection executes a SET IMPLICIT_TRANSACTIONS OFF statement.
AMB
"Ven" wrote:

> Can Implicit Transactions be set for the Data Base and not for every query in
> SQL Server? For a Data Base, how do we set that and how do we verify the
> existing setting .
> --
> Ven
|||Be wary of using this option, your program will remain in transaction state
almost all of the time..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Ven" <Ven@.discussions.microsoft.com> wrote in message
news:7B4AB9B4-8003-4122-AD69-0695985785A7@.microsoft.com...
> Can Implicit Transactions be set for the Data Base and not for every query
in
> SQL Server? For a Data Base, how do we set that and how do we verify the
> existing setting .
> --
> Ven

Implicit transaction

Can Implicit Transactions be set for the Data Base and not for every query i
n
SQL Server? For a Data Base, how do we set that and how do we verify the
existing setting .
--
VenThere is not databse option for this, but you do not have to set it for ever
y
statement, you set it for the connection and it remains in effect until the
connection executes a SET IMPLICIT_TRANSACTIONS OFF statement.
AMB
"Ven" wrote:

> Can Implicit Transactions be set for the Data Base and not for every query
in
> SQL Server? For a Data Base, how do we set that and how do we verify the
> existing setting .
> --
> Ven|||Be wary of using this option, your program will remain in transaction state
almost all of the time..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Ven" <Ven@.discussions.microsoft.com> wrote in message
news:7B4AB9B4-8003-4122-AD69-0695985785A7@.microsoft.com...
> Can Implicit Transactions be set for the Data Base and not for every query
in
> SQL Server? For a Data Base, how do we set that and how do we verify the
> existing setting .
> --
> Ven