which way would be better

[Glowworm27]

Hello all,

Another one of those which way would you do it questions.

I have 2 relational text files InvoiceHeader and InvoiceDetail

These two files are imported into temp tables on the database.

I would import the InvoiceHeader file into the Invoices table, and the InvoiceDetail get broken out into 3 other tables, InvoiceDetail, InvoiceSalesTax, and InvoiceComments.

Originaly the first programmer had these files importing line by line using stored proces. This took way to long, but was the only way he knew how to import data.

I have taken over the import and have created a DTS package that uses Data pumps and SQL tasks to import the files.

My question is this. originaly the InvoiceHeader data was inserted into the Invoices table, and then the user updated the Temp Invoice Header and InvoiceDetail tables with the newly inserted Invoice Ids.

Then go and insert the InvoiceDetail into the cooresponding 3 tables.

I though about skipping the update of the invoicedetail table with the newly inserted Invoice ID's and just do a join on the InvoiceHeader table (because it already has the new id's) and insert the data taking the new invoice id from the Invoiceheader table and the rest of the invoicedetail table Into the 3 cooresponding tables.

The invoice header can contain around 50,000 records, where the invoicedetail table can contain 500,000 records.
I would be removing a step to update the 500,000 records with the new invoice id's but I would be trading that for the slower inner join with the invoice header table. to do my inserts.

What would you do ?

Update the 50,000 header records,then update the 500,000 detail records, and then insert the detail into the 3 tables. or

Update the 50,000 header records, and then update the 3 tables using an inner join between the header, and detail tables?

Thanks in advance


or omit

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[JohnDTampaBay]

I would test both methods and see which one performed better. My guess would be the INNER JOIN method (#2). Especially if you can index the join columns on the tables. Many things impact performance. Only testing will show which is better for your scenario. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)