Optimizing data import in SQL Server 2005 with C #
I have a table that has about 1/2 million records.
Every month we get about 1/2 million records to import. They are currently being carried over to another table in the DB, but will eventually be loaded directly from the txt file. For each of these new entries, I have to determine if we have this entry, and if not, then it needs to be inserted. However, if we have a record, it needs updating. The logic for these updates contains C # code.
The C # command line program handles the import of this new data, and so there are 1/2 million select statements right now - one for each entry. Then a batch (again about 1/2 million) of insert and update statements for the database is created and run.
This takes about 6 hours to run on my workstation. Do you have any ideas on how to speed it up? I need to run about 60 of these large imports to bring the database up to the current month and then load new data once a month.
I think one area that could be improved is 1/2 million selected operators. Perhaps I could issue a single select statement to get all the rows, store them in memory, and search. Can I use the List for this, or is there a better class? I will have to search based on two properties (or DB fields).
source to share
Yes, move the logic into one saved process which will do Bulk Insert on temp table (no logging and then process all records in temp table in two separate statements ... One update for all records that exist in desti table and One Insert for all those who are not
Update DestTable Set
ColName = T.ColName,
[repeat for all cols]
From TmpTable T Join DestTable D On D.Pk = T.Pk
Insert DestTable(ColList)
Select [ColList]
From TmpTable T
Where Not Exists (Select * From DestTable
Where pk = T.Pk)
If this creates too large transactions for your transaction log, split it into smaller chunks
source to share