How to optimize and speed up an asynchronous method with database calls

Question

Hello everyone and thanks for helping me in advance. The following question might sound stupid and incorrect but I'm a beginner about it.

I have a method that gets some information from my database and sends it to an external database using a post call and a patch call in case the information has changed. I use EF Framework. In that db table there are at least 165k rows.

My question is the following: There is a way to optimize and speed up all the process? Maybe using multi threading, parallelism? I'm a beginner about it and I hope some of you help me understand.

The method is the following:

public async Task<List<dynamic>> SyncOrdersTaskAsync(int PageSize)
{
    int PageIndex = 0;

    if (PageSize <= 0) PageSize = 100;

    const string phrase = "The fields order, task_code must make a unique set";

    var sorting = new SortingCriteria {
        Properties = new string[] { "WkOpenDate ASC" } };

    List<dynamic> listTest = new List<dynamic>();

    using (var uow = this.Factory.BeginUnitOfWork())
    {
        var repo = uow.GetRepository<IWorkOrderRepository>();

        var count = await repo.CountAllAsync();

        count = 150;

        for (PageIndex = 0; PageIndex <= count / PageSize; PageIndex++)
        {
            var paging = new PagingCriteria
            {
                PageIndex = PageIndex,
                PageSize = PageSize
            };

            var rows = await repo.GetByCriteriaAsync(
                "new {WkID, CompanyID, JobNo, JobTaskNo ,WkNumber, WkYear," +
                "WkYard,WkCustomerID,CuName,WkDivisionID,DvName,BusinessUnit," +
                "BusinessUnitManagerID,BusinessUnitManager,WkWorkTypeID,WtName," +
                "WkActivityID,WkActivityDescription,NoteDescrLavoro,WkWOManagerID," +
                "ProjectManager,IDMaster,ProjectCoordinator,WkOpenDate," +
                "WkDataChiusa,Prov,CodiceSito,CodiceOffice,CodiceLavorazione," +
                "CodiceNodo,DescrizioneNodo,WkPrevisionalStartDate,WkRealStartDate," +
                "WkPrevisionalEndDate,WkRealEndDate,NumeroOrdine," +
                "WkPrevisionalLabourAmount,TotaleCosti,SumOvertimeHours," +
                "SumTravelHours,SumNormalHours,WkProgressPercentage,Stato,CUP,CIG," +
                "TotaleManodopera,TotalePrestazioni,TotaleNoli,TotaleMateriali," +
                "SumAuxiliaryHours,TipoCommessa,TotaleOrdine, WkPreventivoData," +
                "WkConsuntivoData,TotaleFatturato,AggregateTotaleFatturato," +
                "AggregateTotalePrestazioni,Contract,CustomerOrderNumber," +
                "XmeWBECode,LastUpdateDate,PreGestWkID,CommercialNotes,Mandant," +
                "GammaProjectName,WkInventoryDate,WkCloseFlag,WkNote," +
                "TotalRegisteredLabour,TotalRegisteredPerformances," +
                "TotalRegisteredLeasings,TotalRegisteredMaterials,FlagFinalBalance," +
                "FinalBalance,OrderDate,TotalOrderDivision,SearchDescription," +
                "TotaleBefToBeApproved,TotaleBefToBeApprovedLeasings," +
                "TotaleLabourToBeApproved,AggregateLevel, AggregateTotalLabour," +
                "AggregateTotalLeasings,AggregateTotalMaterials," +
                "AggregateTotalRegisteredLabour," +
                "AggregateTotalRegisteredPerformances," +
                "AggregateTotalRegisteredLeasings," +
                "AggregateTotalRegisteredMaterials," +
                "AggregateTotalCost,AggregateSumNormalHours," +
                "AggregateSumAuxiliaryHours,AggregateSumRainHours," +
                "AggregateSumTravelHours,AggregateSumOvertimeHours," +
                "AggregateWkPrevisionalLabourAmount,AggregateFinalBalance," +
                "AggregateTotalOrder,AggregateTotalOrderDivision," +
                "AggregateTotalBefToBeApproved," +
                "AggregateTotalBefToBeApprovedLeasings," +
                "AggregateTotalLabourToBeApproved,TotalProduction," +
                "AggregateTotalProduction,JobTaskDescription}", paging, sorting);

            String url = appSettings.Value.UrlV1 + "order_tasks/";

            using (var httpClient = new HttpClient())
            {
                httpClient.DefaultRequestHeaders.Add("Authorization", "Token " +
                    await this.GetApiKey(true));
                if (rows.Count() > 0)
                {
                    foreach (var row in rows)
                    {
                        var testWork = (Model.WorkOrderCompleteInfo)Mapper
                            .MapWkOrdersCompleteInfo(row);
                        var orderIdDiv = await this.GetOrderForSyncing(httpClient,
                            testWork.JobNo);
                        var jsonTest = new JObject();
                        jsonTest["task_code"] = testWork.JobTaskNo;
                        jsonTest["description"] = testWork.JobTaskDescription;
                        jsonTest["order"] = orderIdDivitel.Id;
                        jsonTest["order_date"] = testWork.OrderDate.HasValue
                            ? testWork.OrderDate.Value.ToString("yyyy-MM-dd")
                            : string.IsNullOrEmpty(testWork.OrderDate.ToString())
                                ? "1970-01-01"
                                : testWork.OrderDate.ToString().Substring(0, 10);
                        jsonTest["progress"] = testWork.WkProgressPercentage;

                        var content = new StringContent(jsonTest.ToString(),
                            Encoding.UTF8, "application/json");
                        var result = await httpClient.PostAsync(url, content);
                        if (result.Content != null)
                        {
                            var responseContent = await result.Content
                                .ReadAsStringAsync();
                            bool alreadyExists = phrase.All(responseContent.Contains);

                            if (alreadyExists)
                            {
                                var taskCase = await GetTaskForSyncing(httpClient,
                                    testWork.JobTaskNo, orderIdDiv.Id.ToString());
                                var idCase = taskCase.Id;
                                String urlPatch = appSettings.Value.UrlV1 +
                                    "order_tasks/" + idCase + "/";
                                bool isSame = taskCase.Equals(testWork
                                    .toSolOrderTask());
                                if (!isSame)
                                {
                                    var resultPatch = await httpClient.PatchAsync(
                                        urlPatch, content);
                                    if (resultPatch != null)
                                    {
                                        var responsePatchContent = await resultPatch
                                            .Content.ReadAsStringAsync();
                                        var jsonPatchContent = JsonConvert
                                            .DeserializeObject<dynamic>(
                                            responsePatchContent);
                                        listTest.Add(jsonPatchContent);
                                    }
                                }
                                else
                                {
                                    listTest.Add(taskCase.JobTaskNo_ +
                                        " is already updated!");
                                }
                            }
                            else
                            {
                                var jsonContent = JsonConvert
                                    .DeserializeObject<dynamic>(responseContent);
                                listTest.Add(jsonContent);
                            }
                        }
                    }
                }
            }
        }

        return listTest;
    }
}

Maybe I need to apply parallelism in the for loop?

Again, really thanks to everyone in advance and I hope I was clear :)

Sounds like you need to rethink how this API works. You need to be able to batch upload a lot of data at once, which would speed things up significantly. — Charlieface
– Charlieface, Commented Jul 7, 2022 at 10:32
As a side note, the HttpClient class is intended to be instantiated once, and reused throughout the life of an application. — Theodor Zoulias
– Theodor Zoulias, Commented Jul 7, 2022 at 11:06
Regarding the asp.net-mvc tag, is your intention to run this code as part of creating the response for a request in a web application? Also are you targeting the .NET 6 runtime, or an earlier .NET platform? — Theodor Zoulias
– Theodor Zoulias, Commented Jul 7, 2022 at 11:09
@TheodorZoulias not at the moment but in the future it is possible, so I don't exclude this possibility. And I'm using .net6 — Frank DG
– Frank DG, Commented Jul 7, 2022 at 12:45
@Charlieface I'm a beginner in the matter of parallelizing processes — Frank DG
– Frank DG, Commented Jul 7, 2022 at 12:45

Theodor Zoulias · Accepted Answer · 2022-07-07 13:36:19Z

1

The most handy tool that is currently available for parallelizing asynchronous work is the Parallel.ForEachAsync method. It was introduced in .NET 6. Your code is quite complex though, and deciding where to put this loop is not obvious.

Ideally you would like to call the Parallel.ForEachAsync only once, so that it parallelizes your work with a single configurable degree of parallelism from start to finish. Generally you don't want to put this method inside an outer for/foreach loop, because then the degree of parallelism will fluctuate during the whole operation. But since your code is complex, I would go the easy way and do just that. I would replace this code:

foreach (var row in rows)
{
    //...
}

...with this:

ParallelOptions options = new() { MaxDegreeOfParallelism = 2 };
await Parallel.ForEachAsync(rows, options, async (row, _) =>
{
    //...
});

You have to make one more change. The List<T> is not thread safe, and so it will get corrupted if you call Add from multiple threads without synchronization. You can either add a lock (listTest) before each listTest.Add, or replace it with a concurrent collection. My suggestion is to do the later:

ConcurrentQueue<dynamic> listTest = new();
//...
listTest.Enqueue(jsonContent);
//...
return listTest.ToList();

After doing these changes, hopefully your code will still work correctly, and it will be running a bit faster. Then you'll have to experiment with the MaxDegreeOfParallelism setting, until you find the one that yields the optimal performance. Don't go crazy with large values like 100 or 1000. In most cases overparallelizing is harmful, and might yield worse performance than not parallelizing at all.

answered Jul 7, 2022 at 13:36

Theodor Zoulias

46.1k8 gold badges112 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frank DG Over a year ago

It seems to work but sometimes appears this message: "detail: request was throttled. expected available in 1 second"

Theodor Zoulias Over a year ago

@FrankDG the target server might dislike being bombarded with too many requests. Do you have any documentation about how many requests are allowed every X seconds? If you don't, you could consider incorporating a rate-limiter in your code (like this one) and then try to find the server's rate-limiting policy through experimentation.

Frank DG Over a year ago

Honestly I don't have any documentation about how many requests are allowed. Anyway thank you for helping me :) If I encounter other problems I will add a comment here @TheodorZoulias

Collectives™ on Stack Overflow

How to optimize and speed up an asynchronous method with database calls

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related