PK / FK problem in data map 1

[ducksoup]

Hey all --

I need to map an old database into a redesigned database. Right now, I've got an n^3 loop that'll do it -- but with the number of records I have, that loop will take a while (>20 hours by my last test!).

The problem is with the auto_increment id on the new tables and their PK/FK relationships.

Here's the current loop to keep the constraints intact, in pseudocode:

For each Client do
For each Invoice for Client do
For each Item in Invoice do
Insert Item
Get Item.PK
Loop
Insert Invoice with FK -> Item.PK
Get Invoice.PK
Loop
Insert Client with FK -> Invoice.PK
Loop

[This is simplified, the actual transfer involves about ten tables, but this is a similar problem]

Is there a better way to bulk-map the data and still keep the FK->PK constraints intact? Would a trigger be more efficient? From my experience, it wouldn't seem so, especially with a lot of data (>170,000 rows of Invoices for >25,000 Clients).

Note that this is a REDESIGN, column names have changed from the original.

Thanks for any advice! ~Andrew __________________________________
You don't see with your eyes, you perceive with your mind.

 

[chiph]

If you're 100% sure the data will meet the pk/fk constraints, you can drop the relationships between the tables, do your inserts, then re-establish the relationships. This is almost always many times faster, but if there's a chance of bad data, don't do it, as you'll be searching through the tables for the rows that violate the key constraints (yuck).

Chip H.

 

[ducksoup]

Yeah, the old data _does_ have keys but they aren't guaranteed to be unique -- although they theoretically should be (actually I know there are a couple of dupes, but I can massage).

I wasn't sure if turning off the PK and then inserting if the PK would complain about the auto-increment since there's data in there when I turned the PK back on (although I suppose I can turn up the starting value)?

Thanks! ~Andrew __________________________________
You don't see with your eyes, you perceive with your mind.