Reading data from a CSV and pushing it to a Salesforce application I'm trying to write code that reads data from a CSV and pushes it to a Salesforce application using the API. My code processes the data in a for loop, but it takes a long time (3 hours) to run the function. What can I do to optimize my code to run faster?
Here's an example of my code which reads Patient Diagnosis data from a flatfile which us more than 200k records. Inside the for loop, I query the patient list which has 100k+ records, transform the object then add it to a list for bulk processing. My code looks like this:
Iterating over ptdiag which contains flatfile data
for (int i = 0; i &lt; ptdiags.Count; i += BATCH_SIZE)
{
  var batchContents = SFToBTMapping.Bulk_PtDiag_Content(ptdiags.Skip(i).Take(BATCH_SIZE).ToList(),sfPatients);
  var batch = BulkUpsert(job.Id, batchContents);
}

Function that transforms the object. Here I query sfpatients to link a patientid to the diagnosis object
    public static string Bulk_PtDiag_Content(List&lt;Ptdiag&gt; ptdiags, List&lt;SfPatient__c&gt; sfpatients)
    {
        string res = &quot;Patient__c,DiagKey__c,NickName__c&quot; +
            &quot;,Sequence__c,ShortDescr__c,PTDiagKey__c&quot; + Environment.NewLine;

        foreach (var d in ptdiags)
        {
            var sfd = Map_BTSQL_Patientdiag_To_SF_Patientdiag(d);
            sfd.Patient__c = sfpatients.FirstOrDefault(c =&gt; c.PatientKey__c == d.Ptkey.ToString())?.Id;

            res += string.Join(&quot;,&quot;, sfd.Patient__c, sfd.DiagKey__c, sfd.NickName__c
                                , sfd.Sequence__c, sfd.ShortDescr__c.Replace(&quot;,&quot;,&quot;&quot;), sfd.PTDiagKey__c);
            if (ptdiags.Last() != d)
                res += Environment.NewLine;
        }

        return res;
    }

Method that creates a mapping for Ptdiag
    public static SfPatientDiag__c Map_BTSQL_Patientdiag_To_SF_Patientdiag(Ptdiag d)
    {
        return new SfPatientDiag__c
        {
            DiagKey__c = d.Diagkey.ToString(),
            Diagnosis__r = new SfDiagnosis__c { Diagnosis_Key__c = d.Diagkey.ToString() },
            NickName__c = d.Nickname,
            Patient__r = new SfPatient__c { PatientKey__c = d.Ptkey.ToString() },
            Sequence__c = d.Sequence != null ? Convert.ToDouble(d.Sequence) : 0,
            ShortDescr__c = d.Shortdescr,
            PTDiagKey__c = d.Ptdiagkey.ToString()
        };

    }
In addition to the post by @aepot, I made the following changes which reduced the completion time significantly.
I used a dictionary instead of a list to query patient Id. Querying a large list of objects in for loop is what really slowed down the processing. Here's what the code looks like now:
for (int i = 0; i &lt; ptdiags.Count; i += BATCH_SIZE)
{
  var batchContents = SFToBTMapping.Bulk_PtDiag_Content(ptdiags.Skip(i).Take(BATCH_SIZE).ToList(), sfPatients.ToDictionary(p =&gt; p.PatientKey__c));
  var batch = BulkUpsert(job.Id, batchContents);
}

public static string Bulk_PtDiag_Content(List&lt;Ptdiag&gt; ptdiags, Dictionary&lt;string, SfPatient__c&gt; sfpatients)
        {
            var sb = new StringBuilder();
            sb.AppendLine(&quot;Patient__c,DiagKey__c,NickName__c,Sequence__c,ShortDescr__c,PTDiagKey__c&quot;);

            foreach (Ptdiag d in ptdiags)
            {
                SfPatientDiag__c sfd = Map_BTSQL_Patientdiag_To_SF_Patientdiag(d);

                sfd.Patient__c = sfpatients.GetValueOrDefault(d.Ptkey.ToString()) != null ? sfpatients[d.Ptkey.ToString()].Id : &quot;&quot;;

                sb.Append(string.Join(&quot;,&quot;, sfd.Patient__c, sfd.DiagKey__c, sfd.NickName__c
                                    , sfd.Sequence__c, sfd.ShortDescr__c.Replace(&quot;,&quot;, &quot;&quot;), sfd.PTDiagKey__c));
                if (ptdiags.Last() != d)
                    sb.AppendLine();
            }

            return sb.ToString();
        }