My Problem is I am In a Scenario Where I have an API GetAllCards For Exp. And Then I Am Doing Some Time Taking Things In Parallel.Foreach For Example 3 party Api Calling And Inter Db calls All Seems To be Working Fine But When It Comes To the Stored Procedure It Skips Its Execution For Some Reason And Returned Null even though my null check (?? 0) is Not Working In Parallel Loop There Can be Cards Grater then 100 Sometimes And The Stored Procedure Is Returning Total Monthly Spent Of The Card. What do I Want ?? I Don't Want To Skip Any Of The Code Block In a Parallel Loop And also I don't want to increase the response time of API. I have Already Try Some ways But either It Reduces The Speed Of Api or they Start Misbehaving. I can't Change The Third Party Api Calls And Sp Calls Because The Third Party Does Not Provide Any Other Way And Sps Are Already Fast Executing in Less Than a Sec. And I am using Entity Framework Db First Approach (Edmx).

What I Have Tried :

1. Replace Parallel ForEach With Custom ForeachAsync For Reference Link

2. Tried Async Parallelism For Refrence Link

3. Try Initializing ModelX (Database Context) outside The Loop

What My Api Look Likes :

//third party api call
Cards Response = GetCards(Request);

try
{
   Parallel.ForEach(Response.cards, item =>{
   Modelx treaddb = new Modelx();
   
   //internal db call for additional card info
   item.CardInfo = treaddb.CardsTable.Where(x=> x.Cardnumber == item.Cardnumber).FirstOrDefault();
   
    // this Sp Returns ObjectResult<decimel?> and a null check is also handled in sp level sp always return a value if null sp returns 0.00
   item.CardInfo.TotalSpent = treaddb.sp_GetTotalSpendLimit(item.Cardnumber).FirstOrDefault() ?? 0;        
   // third party call
   var cardstatus = GetCardStatus();
   // then use it in Dto To Send Response
 });
}
catch(Exception ex){

}

And That's Prety Much It

CodePudding user response：

For your internal DB calls, I don't think parallelizing the queries helps much.

Whether it is 1 thread or 100 threads, you are still making 100 DB queries to 'CardsTable', and 100 DB queries of 'sp_GetTotalSpendLimit'

I would try something like this to instead make 1 DB query that returns card info for all requested cards at once.

If this improves your loading time, I think the next step would be to add another alternate stored procedure for 'sp_GetTotalSpendLimit' that similarly returns details for all requested cards in one DB query.

As for the third party API calls, I think you are good trying to parallelize them.

//third party api call
Cards Response = GetCards(Request);

Modelx treaddb = new Modelx();
var allCardNumbers = Response.cards.Select(x => x.Cardnumber).ToList();

// Make one internal DB query for card info for all requested cards
var allCardInfo = treaddb.CardsTable.Where(x => allCardNumbers.Any(y => y == x.Cardnumber)).ToList();

try
{
   Parallel.ForEach(Response.cards, item =>{
   
   // Set additional card info from previous internal DB query
   item.CardInfo = allCardInfo.FirstOrDefault(x=> x.Cardnumber == item.Cardnumber);
   
    // this Sp Returns ObjectResult<decimel?> and a null check is also handled in sp level sp always return a value if null sp returns 0.00
   item.CardInfo.TotalSpent = treaddb.sp_GetTotalSpendLimit(item.Cardnumber).FirstOrDefault() ?? 0;        
   // third party call
   var cardstatus = GetCardStatus();
   // then use it in Dto To Send Response
 });
}
catch(Exception ex){

}

CodePudding user response：

I think you are looking for Parallel.ForEach(items, new ParallelOptions{MaxDegreeOfParallelism = n}, item => { /* do something */ }); where n is the maximum number of threads you want to run in parallel.