Bulk insert c# datatable to sql server - Efficient check for duplicate

Question

I wish to import many files into a database (with custom business logic preventing simple SSIS package use).

High level description of problem:

Pull existing sql data into DataTable (~1M rows)
Read excel file into 2d array in one chunk
validate fields row by row (custom logic)
Check for duplicate in existing DataTable
Insert into DataRow
Bulk insert DataTable into SQL table

Problem with my approach: Each row must be checked for duplicates, I thought a call to remote server to leverage SQL would be too slow, so I opted for LINQ. The query was simple, but the size of the dataset causes it to crawl (90% execution time spent in this query checking the fields).

var existingRows = from row in recordDataTable.AsEnumerable()
                                 where row.Field<int>("Entry") == entry
                                 &&    row.Field<string>("Device") == dev
                                 select row;
bool update = existingRows.Count() > 0;

What other ways might there be to more efficiently check for duplicates?

My first thought is that you should probably be drop Count() in favor of Any(). Then I realize you might be looping this over your entire dataset, in which case you're probably better off with Distinct() or Union(). — rutter

Daniel Daniel · Accepted Answer · 2014-02-19T00:47:57

Using linq it will basically do a for loop over your ~1M records every time you check for a duplicate.

You would be better off putting the data into a dictionary so your lookups are against an in memory index.

Bulk insert c# datatable to sql server - Efficient check for duplicate

1 Answers