Seeking a less costly solution for matching Ids of two tables

Question

The application I am building allows a user to upload a .csv file containing multiple rows and columns of data. Each row contains a unique varchar Id. This will ultimately fill in fields of an existing SQL table where there is a matching Id.

Step 1: I am using LinqToCsv and a foreach loop to import the .csv fully into a temporary table.

Step 2: Then I have another foreach loop where I am trying to loop the rows from the temporary table into an existing table only where the Ids match.

Controller Action to complete this process:

[HttpPost]
public ActionResult UploadValidationTable(HttpPostedFileBase csvFile)
{
    var inputFileDescription = new CsvFileDescription
    {
        SeparatorChar = ',',
        FirstLineHasColumnNames = true
    };
    var cc = new CsvContext();
    var filePath = uploadFile(csvFile.InputStream);
    var model = cc.Read<Credit>(filePath, inputFileDescription);

    try
    {
        var entity = new TestEntities();
        var tc = new TemporaryCsvUpload();
        foreach (var item in model)
        {

            tc.Id = item.Id;
            tc.CreditInvoiceAmount = item.CreditInvoiceAmount;
            tc.CreditInvoiceDate = item.CreditInvoiceDate;
            tc.CreditInvoiceNumber = item.CreditInvoiceNumber;
            tc.CreditDeniedDate = item.CreditDeniedDate;
            tc.CreditDeniedReasonId = item.CreditDeniedReasonId;
            tc.CreditDeniedNotes = item.CreditDeniedNotes;
            entity.TemporaryCsvUploads.Add(tc);
        }

        var idMatches = entity.PreexistingTable.Where(x => x.Id == tc.Id);

        foreach (var number in idMatches)
        {
            number.CreditInvoiceDate = tc.CreditInvoiceDate;
            number.CreditInvoiceNumber = tc.CreditInvoiceNumber;
            number.CreditInvoiceAmount = tc.CreditInvoiceAmount;
            number.CreditDeniedDate = tc.CreditDeniedDate;
            number.CreditDeniedReasonId = tc.CreditDeniedReasonId;
            number.CreditDeniedNotes = tc.CreditDeniedNotes;
        }
        entity.SaveChanges();
        entity.Database.ExecuteSqlCommand("TRUNCATE TABLE TemporaryCsvUpload");

        TempData["Success"] = "Updated Successfully";

    }
    catch (LINQtoCSVException)
    {
        TempData["Error"] = "Upload Error: Ensure you have the correct header fields and that the file is of .csv format.";
    }

    return View("Upload");
}

The issue in the above code is that tc is inside the first loop, but the matches are defined after the loop with var idMatches = entity.PreexistingTable.Where(x => x.Id == tc.Id);, so I am only getting the last item of the first loop.

If I nest the second loop then it is way to slow (stopped it after 10 minutes) because there are roughly 1000 rows in the .csv and 7000 in the preexisting table.

Finding a better way to do this is plaguing me. Pretend that the temporary table didn't even come from a .csv and just think about the most efficient way to fill in rows in table 2 from table 1 where the id of that row matches. Thanks for your help!

Answer 1

As your code is written now, much of the work is being done by the application that could much more efficiently be done by SQL Server. You are making hundreds of unnecessary roundtrip calls to the database. When you are mass importing data you want a solution like this:

1. Bulk import the data. See this answer for helpful guidance on bulk import efficiency with EF.
2. Join and update destination table.

Processing the import should only require a single mass update query:

update PT set
   CreditInvoiceDate = CSV.CreditInvoiceDate
  ,CreditInvoiceNumber = CSV.CreditInvoiceNumber
  ,CreditInvoiceAmount = CSV.CreditInvoiceAmount
  ,CreditDeniedDate = CSV.CreditDeniedDate
  ,CreditDeniedReasonId = CSV.CreditDeniedReasonId
  ,CreditDeniedNotes = CSV.CreditDeniedNotes
from PreexistingTable PT
join TemporaryCsvUploads CSV on PT.Id = CSV.Id

This query would replace your entire nested loop and apply the same update in a single database call. As long as your table is indexed properly this should run very fast.

Answer 2

After saving CSV record into second table which have same fileds as your primary table, execute following procedure in sqlserver

create proc [dbo].[excel_updation]
  as
set xact_abort on

begin transaction
-- First update records
update first_table
   set [ExamDate]      = source.[ExamDate],
       [marks]      = source.[marks],
       [result]      = source.[result],
       [dob] = source.[dob],
       [spdate]      = source.[spdate],
       [agentName]      = source.[agentName],
       [companycode]      = source.[companycode],
       [dp]      = source.[dp],
       [state]      = source.[state],
       [district]      = source.[district],
       [phone]      = source.[phone],
       [examcentre]      = source.[examcentre],
       [examtime]      = source.[examtime],
       [dateGiven]      = source.[dateGiven],
       [smName]      = source.[smName],
       [smNo]      = source.[smNo],
       [bmName]      = source.[bmName],
       [bmNo]      = source.[bmNo]
  from tbUser
 inner join second_table source
    on tbUser.[UserId]     = source.[UserId]

-- And then insert

insert into first_table (exprdate, marks, result, dob, spdate, agentName, companycode, dp, state, district, phone, examcentre, examtime, dateGiven, smName, smNo, bmName, bmNo)
select   [ExamDate], [marks], [result], [dob], [spdate], [agentName], [companycode], [dp], [state], [district], [phone], [examcentre], [examtime], [dateGiven], [smName], [smNo], [bmName], [bmNo]
  from second_table source
 where not exists
       (
          select *
            from first_table
           where first_table.[UserId]     = source.[UserId]
       )

commit transaction

delete from second_table

The condition of this code is only that both table must have same id matching data. Which id match in both table, data of that particular row will be updated in first table.

Answer 3

As long as the probability of the match is high you should simply attempt update with every row from your CSV, with a condition that the id matches,

UPDATE table SET ... WHERE id = @id