1
\$\begingroup\$

I have an .NET Core EF application, setup as Database first, that is supposed to take in a payload containing items to be persisted to my database. They payloads can be huge containing multiple "Securities" and each "Security" has multiple "Positions/Transactions" to be persisted to the database. This application can be called multiple times in parallel from a commandline tool. Sometimes these payloads can take up to 20 minutes to deserialize to their components and persist. The ultimate goal here is to reduce the amount of time taken to run through a payload and persist. The SaveChanges() function call is not the issue here (afaik). I think the issue is the nested looping through the securities and all of their positions on the payload and the corresponding LINQ queries. These operations are happening in HandlePostions/HandleTransactions/TryFindSecId functions. Any help pointing out any obvious code smells, any performance improvements, or any help optimizing the queries would be welcomed. Here is my calling function:

    private async Task<AccPackBody> Persist(AccPackBody pack)
    {
        bool hasModifications = false, hasErrors = false;

        using (var dbContext = new newportMasterContext(Configs.MasterDbConnString))
        {
            dbContext.Database.SetCommandTimeout(300);

            foreach(var mappedSecurity in pack.Internal)
            {
                mappedSecurity.FirmId = pack.FirmId;

                SEC_IDENTIFIER existingSecurity = null;
                try
                {
                    existingSecurity = await TryFindSecId(mappedSecurity, pack.FirmId.Value, dbContext);
                }
                catch(Exception ex)
                {
                    await Logger.Send(LogType.Error, "Something failed while trying to clone security");
                }

                if(existingSecurity == null)
                {
                    await Logger.Send(pack, LogType.Error, $"Missing security for: Identifier: {mappedSecurity.IdentifierName} Value: {mappedSecurity.IdentifierValue}, Firm{pack.FirmId}, Currency:{mappedSecurity.Currency}");
                    throw new LoggedException();
                }
                else
                {
                    mappedSecurity.SecurityGuid = existingSecurity.Security_UID;

                    POSITION[] updatedPositions = null;
                    if(mappedSecurity.Postitions != null && mappedSecurity.Postitions.Length > 0)
                    {
                        updatedPositions = HandlePositions(pack,dbContext, pack.State, pack.AccountUID.Value, mappedSecurity, pack.StatementDate.Value);


                        mappedSecurity.Postitions = updatedPositions != null  && updatedPositions.Count() > 0
                            ? updatedPositions
                            : new POSITION[0];

                        if(updatedPositions != null && updatedPositions.Count() > 0)
                            hasModifications = true;
                    }

                    //EditTransaction
                    TRANSACTION[] updatedTransactions = null;
                    if(mappedSecurity.Transactions != null && mappedSecurity.Transactions.Length > 0)
                    {
                        updatedTransactions = HandleTransctions(dbContext, pack, mappedSecurity);
                        mappedSecurity.Transactions = updatedTransactions != null && updatedTransactions.Count() > 0
                            ? updatedTransactions
                            : new TRANSACTION[0];

                        if(updatedTransactions != null && updatedTransactions.Count() > 0)
                            hasModifications = true;
                    } 
                }
            }

            if(!hasErrors && dbContext.ChangeTracker.HasChanges())
            {
                try
                {
                    await dbContext.SaveChangesAsync();
                }
                catch(Exception ex)
                {
                    await Logger.Send(pack,LogType.Warning, "Error persisting changes to db, rolling back: " + ex.ToString());
                    throw new LoggedException();
                }
            }
        }

        return pack;
    } 

Here are my three functions that the above method calls - these are where a lot of the slowdowns are occuring:

    private async Task<SEC_IDENTIFIER> TryFindSecId(AccMappedSecurity mappedSecurity, long firmId, newportMasterContext dbContext, Guid? packUid = null)
    {
        var existingSecurities = dbContext.SEC_IDENTIFIER
            .Where(x => x.IdentifierName == mappedSecurity.IdentifierName 
                && mappedSecurity.IdentifierValue == x.Identifier
                && x.FirmID == firmId 
                && (string.IsNullOrEmpty(mappedSecurity.Currency) || x.SECURITY.Currency == mappedSecurity.Currency) && x.SECURITY.ChangeType != "D");

        var existingSecurity = existingSecurities.FirstOrDefault();

        if(existingSecurity == null)
        {
            var cloneResult = await ClonePostition.Clone(
                new ClonePositionRequest
                {
                    IdentifierName = mappedSecurity.IdentifierName,
                    IdentifierValue = mappedSecurity.IdentifierValue,
                    NewCurrency = mappedSecurity.Currency,
                    FirmId = firmId
                },
                Logger.LocalLog
            );

            if(cloneResult.Code != HttpStatusCode.InternalServerError)
            {
                existingSecurity = dbContext.SEC_IDENTIFIER
                .FirstOrDefault(x => x.IdentifierName == mappedSecurity.IdentifierName 
                    && mappedSecurity.IdentifierValue == x.Identifier
                    && x.FirmID == firmId 
                    && (string.IsNullOrEmpty(mappedSecurity.Currency) || x.SECURITY.Currency == mappedSecurity.Currency));
            }
            else
            {
                await Logger.Send(LogType.Error, "Internal server error on clone function - clone failed" + cloneResult.Code);
            }
        }
        else
        {

        }

        return existingSecurity;
    }





    private POSITION[] HandlePositions(AccPackBody reqPayload, newportMasterContext dbContext, PackState state, Guid accountGuid, AccMappedSecurity mappedSecurity, DateTime statementDate, Guid? packUid = null)
    {
        if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, "Hit handle positions");

        var handledPositions = new List<POSITION>();
        int posCnt = mappedSecurity.Postitions.Length;
        if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, "Deserializing mapped positions");
        List<POSITION> deserializedPositions = mappedSecurity.Postitions
            .Select(x => JsonConvert.DeserializeObject<POSITION>(x.ToString(), Configs.JsonCircularRefFixConfig))
            .OrderBy(x => x.DateStart)
            .ToList();

        var updatedPosDetails = new List<POSITION_DETAIL>();
        POSITION prevailingPosition = null;

        if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, "Checking for prevailing position.");

        prevailingPosition = dbContext.POSITION
            .Include(x => x.POSITION_DETAIL)
            .Include(x => x.POSITION_DETAIL).ThenInclude(x => x.POSITION_BOND)
            .Include(x => x.POSITION_DETAIL).ThenInclude(x => x.POSITION_FX)
            .Include(x => x.POSITION_DETAIL).ThenInclude(x => x.POSITION_OPTION)
            .Include(x => x.POSITION_DETAIL).ThenInclude(x => x.POSITION_PRICE)
            .Where(x => x.Account_UID == accountGuid 
                && x.Security_UID == mappedSecurity.SecurityGuid 
                && x.SecurityFirmID == mappedSecurity.FirmId 
                && (x.DateStart.Date <= statementDate) && (!x.DateEnd.HasValue || x.DateEnd.Value.Date < statementDate) 
                && x.ChangeType != "D" 
                && x.POSITION_DETAIL.Any(pd => pd.Genesis == "FEED" &&
                                            pd.ChangeType != "D"))
            .OrderByDescending(x => x.DateStart)
            .ToList().FirstOrDefault();

        if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, "Looping incoming positions.");
        foreach(var incomingPosition in deserializedPositions)
        {
            if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, string.Format("Hit new incoming position:{0}", incomingPosition.Position_UID.ToString()));
            incomingPosition.Account_UID = accountGuid;
            incomingPosition.Security_UID = mappedSecurity.SecurityGuid.Value;
            incomingPosition.SecurityFirmID = mappedSecurity.FirmId.Value;
            incomingPosition.Position_UID = Guid.NewGuid();
            if (prevailingPosition == null)
            {
                EntityEntry<POSITION> newPosition = null;
                dbContext.POSITION.Add(incomingPosition);
                newPosition = dbContext.Entry(incomingPosition);
                newPosition.CurrentValues.SetValues(new { ChangeType = "I" });
                foreach(var posDetail in incomingPosition.POSITION_DETAIL.Where(x => x.ChangeType.ToUpper() != "D"))
                {
                    EntityEntry<POSITION_DETAIL> newPositionDetail = null;
                    posDetail.Detail_UID = Guid.NewGuid();
                    dbContext.POSITION_DETAIL.Add(posDetail);
                    newPositionDetail = dbContext.Entry(posDetail);
                    newPositionDetail.CurrentValues.SetValues(new { ChangeType = "I"});
                    updatedPosDetails.Add(posDetail);
                }

                handledPositions.Add(incomingPosition);

                if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, "Inserted: " + incomingPosition.Position_UID.ToString());
            }
            else
            {
                if(prevailingPosition.DateStart.Date == incomingPosition.DateStart.Date)
                {
                    foreach(var posDetail in prevailingPosition.POSITION_DETAIL.Where(x => x.ChangeType.ToUpper() != "D"))
                    {
                        EntityEntry<POSITION_DETAIL> positionDetailToRemove = dbContext.Entry(posDetail);
                        positionDetailToRemove.CurrentValues.SetValues(new
                        {
                            ChangeDate = DateTime.Now,
                            ChangeType = "D"
                        });
                    }
                    EntityEntry<POSITION> positionToRemove = dbContext.Entry(prevailingPosition);
                    positionToRemove.CurrentValues.SetValues(new 
                    {
                        ChangeDate = DateTime.Now,
                        ChangeType = "D"
                    });

                    EntityEntry<POSITION> newPosition = null;
                    dbContext.POSITION.Add(incomingPosition);
                    newPosition = dbContext.Entry(incomingPosition);
                    newPosition.CurrentValues.SetValues(new { ChangeType = "I" });
                    handledPositions.Add(incomingPosition);

                    if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, "inserted: " + incomingPosition.Position_UID.ToString());

                    foreach(var posDetail in incomingPosition.POSITION_DETAIL.Where(x => x.ChangeType.ToUpper() != "D"))
                    {
                        EntityEntry<POSITION_DETAIL> newPositionDetail = null;
                        posDetail.Detail_UID = Guid.NewGuid();
                        dbContext.POSITION_DETAIL.Add(posDetail);
                        newPositionDetail = dbContext.Entry(posDetail);
                        newPositionDetail.CurrentValues.SetValues(new { ChangeType = "I"});
                        updatedPosDetails.Add(posDetail);
                    }
                }
                else if(prevailingPosition.DateStart.Date < incomingPosition.DateStart.Date 
                    && PositionHasChange(prevailingPosition, incomingPosition, packUid))
                {
                    EntityEntry<POSITION> newPosition = null;
                    dbContext.POSITION.Add(incomingPosition);
                    newPosition = dbContext.Entry(incomingPosition);
                    newPosition.CurrentValues.SetValues(new { ChangeType = "I" });
                    handledPositions.Add(incomingPosition);

                    if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, "inserted: " + incomingPosition.Position_UID.ToString());

                    foreach(var posDetail in incomingPosition.POSITION_DETAIL.Where(x => x.ChangeType.ToUpper() != "D"))
                    {
                        EntityEntry<POSITION_DETAIL> newPositionDetail = null;
                        posDetail.Detail_UID = Guid.NewGuid();
                        dbContext.POSITION_DETAIL.Add(posDetail);
                        newPositionDetail = dbContext.Entry(posDetail);
                        newPositionDetail.CurrentValues.SetValues(new { ChangeType = "I"});
                        updatedPosDetails.Add(posDetail);
                    }
                }
                else if(prevailingPosition.DateStart.Date < incomingPosition.DateStart.Date 
                    && !PositionHasChange(prevailingPosition, incomingPosition, packUid))
                {
                }
            }
        }

        return handledPositions.ToArray();
    }





 private TRANSACTION[] HandleTransctions(newportMasterContext dbContext, AccPackBody pack, AccMappedSecurity mappedSecurity, Guid? packUid = null)
    {
        object[] transactionsToProcess = new object[0]; 
        var handledTransactions = new List<TRANSACTION>();

        foreach(var trans in mappedSecurity.Transactions)
        {
            TRANSACTION handledTransaction = null, 
                mappedTransaction = null;

            mappedTransaction =  JsonConvert.DeserializeObject<TRANSACTION>(trans.ToString(), Configs.JsonCircularRefFixConfig);
            mappedTransaction.Security_UID = mappedSecurity.SecurityGuid.Value;

            //HACK
            if(string.IsNullOrWhiteSpace(mappedTransaction.BaseCurrency))
            {
                mappedTransaction.BaseCurrency = "USD";
            }

            //missing transId - drop out
            if(mappedTransaction.Reversal && string.IsNullOrWhiteSpace(mappedTransaction.TranID))
            {
                Logger.Send(pack, LogType.Warning, "Reversal Transaction is missing a transacation Id");
                throw new LoggedException();
            }
            else
            {
                //if(mappedTransaction.Reversal && !string.IsNullOrWhiteSpace(mappedTransaction.TranID))
                if(mappedTransaction.Reversal)
                {
                    List<TRANSACTION> transactionsInDb = dbContext.TRANSACTION
                        .Where(x => x.Account_UID == pack.AccountUID
                            && x.Security_UID == mappedSecurity.SecurityGuid
                            && x.SecurityFirmID == mappedSecurity.FirmId
                            && x.TradeDate.Date == mappedTransaction.TradeDate.Date
                            && x.TranID == mappedTransaction.TranID
                            && !x.Reversal
                            && x.ChangeType != "D")
                        .ToList();

                    if(transactionsInDb.Count == 1)
                    {
                        mappedTransaction.Visible = false;
                        TRANSACTION transactionInDb = transactionsInDb.FirstOrDefault();
                        transactionInDb.Visible = false;
                    }
                }

                mappedTransaction.SecurityFirmID = mappedSecurity.FirmId.Value;
                mappedTransaction.Account_UID = pack.AccountUID.Value;

                if(mappedTransaction?.Comment?.Length >= 100)
                {
                    mappedTransaction.Comment = mappedTransaction.Comment.Substring(0,96) + "...";
                }

                mappedTransaction.Tran_UID = Guid.NewGuid();

                var comparer = new TransactionComparer();
                EntityEntry<TRANSACTION> transactionToInsert = null;
                dbContext.TRANSACTION.Add(mappedTransaction);
                transactionToInsert = dbContext.Entry(mappedTransaction);
                transactionToInsert.CurrentValues.SetValues(new { ChangeType = "I" });

                if(useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog(_logText, packUid.Value, "inserted: " + mappedTransaction.Tran_UID.ToString());

                if(transactionToInsert != null)
                {
                    transactionToInsert.CurrentValues.SetValues(new { ChangeDate = DateTime.Now });
                }

                handledTransaction = transactionToInsert?.Entity;

                if(handledTransaction != null)
                {
                    handledTransactions.Add(handledTransaction);
                }
             //   }
            }



        }

        return handledTransactions.ToArray();
    }

I have refactored the HandlePositions method to multiple functions for readability and code clarity. I am working some of the other code as well right now.

   private POSITION[] HandlePositions(AccPackBody reqPayload, newportMasterContext dbContext, PackState state, Guid accountGuid, AccMappedSecurity mappedSecurity, DateTime statementDate, Guid? packUid = null)
    {
        var handledPositions = new List<POSITION>();

        //deserialize positions sent in through mappedSecurity
        List<POSITION> deserializedPositions = mappedSecurity.Postitions
            .Select(x => JsonConvert.DeserializeObject<POSITION>(x.ToString(), Configs.JsonCircularRefFixConfig))
            .OrderBy(x => x.DateStart)
            .ToList();

        //get the prevailing position if it exists
        POSITION prevailingPosition = GetPrevailingPosition(accountGuid, statementDate, mappedSecurity, dbContext);

        //loop the incoming positions to persist as necessary
        foreach(var incomingPosition in deserializedPositions)
        {
            incomingPosition.Account_UID = accountGuid;
            incomingPosition.Security_UID = mappedSecurity.SecurityGuid.Value;
            incomingPosition.SecurityFirmID = mappedSecurity.FirmId.Value;
            incomingPosition.Position_UID = Guid.NewGuid();
            if (prevailingPosition == null)
            {
                //persist a position
                POSITION persistedPosition = AddPostionToContext(incomingPosition, dbContext);

                if(persistedPosition!=null)
                    handledPositions.Add(incomingPosition);
            }
            else
            {
                if(prevailingPosition.DateStart.Date == incomingPosition.DateStart.Date)
                {
                    //soft delete a position/details
                    POSITION positionToRemove = RemovePostionFromContext(prevailingPosition, dbContext);

                    //persist a position
                    POSITION persistedPosition = AddPostionToContext(incomingPosition, dbContext);
                    if(persistedPosition != null)
                        handledPositions.Add(persistedPosition);
                }
                else if(prevailingPosition.DateStart.Date < incomingPosition.DateStart.Date 
                    && PositionHasChange(prevailingPosition, incomingPosition, packUid))
                {
                    //persist a position
                    POSITION persistedPosition = AddPostionToContext(incomingPosition, dbContext);
                    if(persistedPosition != null)
                        handledPositions.Add(persistedPosition);
                }
            }
        }

        return handledPositions.ToArray();
    }

New functions to support the above code:

        private static POSITION AddPostionToContext(POSITION position, newportMasterContext context)
        {
            EntityEntry<POSITION> newPosition = null;
            context.POSITION.Add(position);
            newPosition = context.Entry(position);
            newPosition.CurrentValues.SetValues(new { ChangeType = "I" });
            foreach(var posDetail in position.POSITION_DETAIL.Where(x => x.ChangeType.ToUpper() != "D"))
            {
                EntityEntry<POSITION_DETAIL> newPositionDetail = null;
                posDetail.Detail_UID = Guid.NewGuid();
                context.POSITION_DETAIL.Add(posDetail);
                newPositionDetail = context.Entry(posDetail);
                newPositionDetail.CurrentValues.SetValues(new { ChangeType = "I"});
            }
            return position;
        }

        private static POSITION RemovePostionFromContext(POSITION position, newportMasterContext context)
        {
            foreach(var posDetail in position.POSITION_DETAIL.Where(x => x.ChangeType.ToUpper() != "D"))
            {
                EntityEntry<POSITION_DETAIL> positionDetailToRemove = context.Entry(posDetail);
                positionDetailToRemove.CurrentValues.SetValues(new
                {
                    ChangeDate = DateTime.Now,
                    ChangeType = "D"
                });
            }
            EntityEntry<POSITION> positionToRemove = context.Entry(position);
            positionToRemove.CurrentValues.SetValues(new 
            {
                ChangeDate = DateTime.Now,
                ChangeType = "D"
            });
            return position;
        }

        private static POSITION GetPrevailingPosition(Guid accountId, DateTime statementDate, AccMappedSecurity mappedSecurity, newportMasterContext context)
        {
            return (context.POSITION
                .Include(x => x.POSITION_DETAIL)
                .Include(x => x.POSITION_DETAIL).ThenInclude(x => x.POSITION_BOND)
                .Include(x => x.POSITION_DETAIL).ThenInclude(x => x.POSITION_FX)
                .Include(x => x.POSITION_DETAIL).ThenInclude(x => x.POSITION_OPTION)
                .Include(x => x.POSITION_DETAIL).ThenInclude(x => x.POSITION_PRICE)
                .OrderByDescending(x => x.DateStart)
                .FirstOrDefault(x => x.Account_UID == accountId 
                    && x.Security_UID == mappedSecurity.SecurityGuid 
                    && x.SecurityFirmID == mappedSecurity.FirmId 
                    && (x.DateStart.Date <= statementDate) && (!x.DateEnd.HasValue || x.DateEnd.Value.Date < statementDate) 
                    && x.ChangeType != "D" 
                    && x.POSITION_DETAIL.Any(pd => pd.Genesis == "FEED" &&
                                                pd.ChangeType != "D")));
        }
\$\endgroup\$
  • 1
    \$\begingroup\$ .ToList().FirstOrDefault(); doesn't make any sense since both of those methods will materialize the query. You could safely remove the .ToList(). \$\endgroup\$ – Brad M Apr 19 at 13:10
  • 2
    \$\begingroup\$ A lot of this code doesn't make any sense. For example, secGuidsWithPositions is a List<Guid> that just gets added to, but nothing is ever done with it. Also, the formatting is really off. secGuidsWithPositions is Hungarian notation. List<Guid> should almost always be a HashSet<Guid> instead to prevent duplicates. \$\endgroup\$ – Brad M Apr 19 at 13:13
  • \$\begingroup\$ @BradM - secGuidsWithPositions isn't being used you are correct. I am going to pull that out. It was being used at one point. \$\endgroup\$ – VinnyGuitara Apr 19 at 13:22
  • 1
    \$\begingroup\$ Why do you do a Where only to later use a FirstOrDefault? I only see items being added to updatedPosDetails -- what's the point? \$\endgroup\$ – BCdotWEB Apr 19 at 13:40
  • 2
    \$\begingroup\$ You can replace the Where with the FirstOrDefault, that way the db will only return the TOP 1 result. My main problem with your code is that there is for instance a 100+ line method in there which does all kinds of things. Understanding this code requires a lot of business knowledge and keeping track of such things is hard is a 100+ lines method with various loops and if blocks etc. You should split that up into properly named smaller methods. HandlePositions should IMHO be a class of its own (PositionsHandler?) with a number of methods. \$\endgroup\$ – BCdotWEB Apr 19 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.