Optimize stored procedure loop

Question

I have a c# application where I call a stored procedure in C#, I pass a list object to the procedure and it runs a validation on the records. This is however very slow to process with a lot of records, is there perhaps a better way to achieve this. Please see my code below

using (SqlConnection conn = new SqlConnection(sqlConnection))
{
    try
    {
        foreach (var claim in supplierClaimsData)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 60;
            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;
            cmd.Connection = conn;

            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                claim.ST_Key = reader.GetString(reader.GetOrdinal("ST_Key"));
                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"));
                string warningCleanInclusion = "Warning";

                if (claim.ST_Key != null && string.IsNullOrEmpty(claim.Warning) && string.IsNullOrEmpty(claim.Error_1) && string.IsNullOrEmpty(claim.Error_2))
                {
                    var existingClaimCount = db.GPClaimsReadyToImports.Count(a => a.ST_Key == claim.ST_Key && a.CleanSupplierClaimSessionID == claim.CleanSupplierClaimsUploadSessionID);
                    if (existingClaimCount == 0)

                        db.GPClaimsReadyToImports.Add(new GPClaimsReadyToImport
                        {
                            Id = claim.Id,
                            ST_Key = claim.ST_Key,
                            Warning = claim.Warning,
                            Action = claim.Action,
                            Claim_Reference = claim.ClaimReference,
                            Currency = claim.Currency,
                            Error_1 = claim.Error_1,
                            Error_2 = claim.Error_2,
                            Line_Number = claim.Line_Number,
                            Total_Claim = claim.Total_Claim,
                            Domain_Username = domainNameOfficial.ToString(),//claim.Domain_Username,
                            DateCreated = DateTime.Now,
                            ImportFlag = true,
                            ReadyForImport = true,
                            CleanSupplierClaimSessionID = sessionIdentifier

                        });
                    db.SaveChanges();
                }
            }

            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();
            }

            conn.Close();
        }
    }
}

I wonder if there is a way I can exclude calling the proc in the loop, but don't know exactly how to optimize this code, any help will be appreciated.

Because you are in a using section you don't have to explicite close your connection. — Mr_Thorynque, Jul 20 '16 at 07:02
An optimization could be multi-threading of stored procedure calls using a thread pool or a simple Parallel.ForEach — Mr_Thorynque, Jul 20 '16 at 07:06
easier, and better performance win to get code of storedproc and make another version of it that reads directly from source table, etc. — DaFi4, Jul 20 '16 at 12:00

score 1 · Answer 1 · edited May 23 '17 at 10:27

1

I see that the Stored procedure is called multiple times for each record, which causes the time delay. Thus recommend to pass the "supplierClaimsData" List/Array to the procedure as a whole via XML or Table type variable and have the procedure return a table for subsequent input records. Then do your while loop for each result table, that will be faster...

One of the example to pass the table to procedure is defined in the link. How to pass User Defined Table Type as Stored Procedured parameter in C#

edited May 23 '17 at 10:27

Community

1
1

answered Jul 20 '16 at 06:55

Prithvi Raja

11
2

I have no access to modify the proc as it was built by the BI team, they built it to allow the proc to take two parameters, claim invoice and amount , my list consists of items with invoice and amount. Is there a way I can achieve this without modifying the procedure? – Papi Jul 20 '16 at 07:16
1

In that case, generalise SQLCommand and procedure declaration before foreach loop and do not close the SQLconnection. Close it after the foreach loop. Collect the returned result of each iteration in a datatable and restructure your while loop logic to accommodate the datatable. – Prithvi Raja Jul 20 '16 at 08:46
@papi get the code for the procedure and make a new procedure (best). or wrap the procedure in another procedure (not as good) – DaFi4 Jul 20 '16 at 12:12

Optimize stored procedure loop

1 Answers1