I have a c# application(mvc) that allows a user to upload an excel sheet containing an account number key and amount value to be passed as parameters to a proc that then validates those records, the proc then return an error or warning if any are found. My issue is that the BI team implemeted a proc which I cannot modify to make it accept a table type as explained in this post Table Value The issue is that I cannot modify the proc to accept an table type, so I need to optimize the stored procedure call in c# code. The performance is acceptable if there are about 30 to 50 lines the proc needs to validate, but once there are more record e.g a 100 or more the application is slow, is there a way in c# that I can improve the performance of this code, please see my implementation below.using (SqlConnection conn = new SqlConnection(sqlConnection))    {        try        {            foreach (var claim in supplierClaimsData)            {                SqlCommand cmd = new SqlCommand();                cmd.CommandTimeout = 60;                SqlDataReader reader;                cmd.Connection = conn;                cmd.CommandText = CRM.Supplier_Claim_Upload;                cmd.CommandType = CommandType.StoredProcedure;                cmd.Parameters.Add(@Invoice, SqlDbType.NVarChar).Value = claim.Line_Number;                cmd.Parameters.Add(@Amount, SqlDbType.Decimal).Value = claim.Total_Claim;                conn.Open();                reader = cmd.ExecuteReader();                while (reader.Read())                {                    claim.Error_1 = reader.GetString(reader.GetOrdinal(Error1));                    string lineNumberDoesNotExist = Error: Invoice line number does not exist;                    if (claim.Error_1.StartsWith(lineNumberDoesNotExist))                    {                        continue;                    }                    claim.Warning = reader.GetString(reader.GetOrdinal(Warning));                    claim.Error_2 = reader.GetString(reader.GetOrdinal(Error2));                }                conn.Close();            }            foreach (CleanSupplierClaim saveToDBClaim in supplierClaimsData)            {                db.CleanSupplierClaims.Attach(saveToDBClaim);                var entry = db.Entry(saveToDBClaim);                entry.Property(aa => aa.Line_Number).IsModified = true;                entry.Property(aa => aa.Total_Claim).IsModified = true;                entry.Property(aa => aa.Currency).IsModified = true;                entry.Property(aa => aa.ClaimReference).IsModified = true;                entry.Property(aa => aa.Action).IsModified = true;                entry.Property(aa => aa.Domain_Username).IsModified = true;                entry.Property(aa => aa.Error_1).IsModified = true;                entry.Property(aa => aa.Error_2).IsModified = true;                entry.Property(aa => aa.Warning).IsModified = true;                entry.Property(aa => aa.ImportFlag).IsModified = true;                entry.Property(aa => aa.ReadyForImport).IsModified = true;                db.Entry(saveToDBClaim).State = System.Data.Entity.EntityState.Modified;                db.SaveChanges();            }        }I would really appreciate your input in this regard.Here is the procedure I got from the QA sql, it basically validates claims with Invoice number and Amount as parameters.    ALTER PROCEDURE [CRM].[Supplier_Claim_Upload] (@Invoice        varchar(20), @Amount         decimal(18,3) )asset nocount ondeclare         @ST_Key varchar(20)     = @Invoicedeclare         @Claim decimal(18,3)    = @Amountselect * into #Tempfrom (        select st.ST_Key,                 case when sm.Supplier_Claim_Original > 0 then 'Warning: You Are going to Overwrite a Claim Value: ' + convert(varchar(50), round(sm.Supplier_Claim_Original, 0)) else '' end as 'Warning',                case when st.Orig_Inv is not null then 'Error: You are attempting to claim against a returned invoice: ' + st.Orig_Inv else '' end as 'Error1',                case when  @Claim > sm.System_Cost then 'Error: Claim value is higher than system cost: ' + convert(varchar(50), sm.System_Cost) else '' end as 'Error2'        from Embrace.fact.Sales st        left join Embrace.fact.Sales_Margin sm on sm.ST_Key = st.ST_Key and sm.Tarsus_Country = st.Tarsus_Country        where st.ST_Key = @ST_Key) xif not exists (select top 1 * from #Temp)begin    select @ST_Key as 'ST_Key', 'Error: Invoice line number does not exist' as 'Error1'endelsebegin    select * from #Temp enddrop table #Temp--Action    --Line No   --Total Claim   --Currency  --Claim Referenceset nocount off
Optimize store proc performance being called in c# on array loop
c#;asp.net mvc;stored procedure