Comparing Largish DataSets C# or SQL for OFAC

I need to find a way to compare 2 large datasets quickly, we are comparing a list of names (approximately 31,000 entries) against OFAC's SDN List (approximately 30,000 entries, a combination of the main SDN list and the alternate names SDN list). If I'm doing my math correctly, this combination would result in a datatable with over 900,000,000 entries. Based on my extrapolations it would take about 10 days for this to finish on my pc, using Parallel.ForEach loops, if it takes 30 seconds to compare 1 name against the OFAC SDN information. I'm using a C# console app for my testing. The data is stored in MS SQL database. I'm open to using either source for the processing, locally or on the SQL Server.

Please forgive me if my estimations are wrong, but the main crux is, this is going to take a long time.

This is for the initial process. In subsequent comparison runs, I'll check the names against a results table to see if the comparison has already been done and skip it if it's already been done.

How do I speed this up so the initial comparison process does not take so long??? Thanks for your help and tips!!

Here's my console app code.

List<CUSTOMER> CUSTOMERList = conn.CUSTOMERs.Take(1).ToList();

List<OFAC_Main> ofacMainList = conn.OFAC_Main.Include("OFAC_Alternate").Include("OFAC_Address").Include("Ofac_RemarkComment").ToList();

int minimumCharacterCount = Convert.ToInt32(Setting.SelectByName("MinimumCharacters").Value);

//loop through the CUSTOMER list
Parallel.ForEach(CUSTOMERList, CUSTOMER =>
{
	//find the values in the possible name fields in each CUSTOMER record
	List<string> CUSTOMERNames = GetNames(CUSTOMER);

	//loop through the names found in the CUSTOMER record
	Parallel.ForEach(CUSTOMERNames, CUSTOMERName =>
	{
		//if the CUSTOMER name is longer than the minimum character count setting, process it
		if (CUSTOMERName.Length > minimumCharacterCount)
		{
			//loop through the SDN list and process the name
			Parallel.ForEach(ofacMainList, ofacName =>
			{
				//convert List to json object
				//string ofacJson = JsonConvert.SerializeObject(ofacName);

				//if the SDN name is longer than the minimum character count setting, process it
				if (ofacName.SDN_Name.Length > minimumCharacterCount)
				{
					Compare(CUSTOMERName.ToString(), ofacName.SDN_Name.ToString());
				}

				//if the SDN Name has Alternate names too, need to run the comparison against it too
				if (ofacName.OFAC_Alternate.Count > 0)
				{
					foreach (OFAC_Alternate alternateName in ofacName.OFAC_Alternate)
					{
						//if the SDN Alternate name is longer than the minimum character count setting, process it
						if (alternateName.Alt_Name.Length > minimumCharacterCount)
						{
							Compare(CUSTOMERName.ToString(), alternateName.Alt_Name.ToString());
						}
					}
				}
			});
		}
	});
});

I agree ! You need to chop this down into small pieces. One idea would be to do the joining and filtering in SQL, then, when you have a table with all the potential comparisons, start looping through doing your Compare(). The join you need is called a cartesian join, and it's what you get if you don't specify any join criteria. That way, you can store the result of Compare() in the Comparisons table, and throw out any comparisons that are not interesting. so something like this...

CREATE table Comparisons (...)
INSERT into Comparisons(...) (
SELECT C.Name, O.OFAC_Alternate, OFAC_Address, OFAC_RemarkComment
FROM Customers C, OFAC_Main O
WHERE len(C.Name) > @minimumCharacterCount
and( 
   len(O.OFAC_Name) > @minimumCharacterCount
   or len(O.OFAC_Alternate > @minimumCharacterCount)
)
-- you can set some arbitrary filter here to chop this mega table into smaller pieces.
and C.id < 1000
)

This part won't take long. It's the Compare() function that will kill you. I have no idea how you're going to speed that up.