Question Why it gets too long to update?

[raysefo]

Hello,

I am trying to update my database table as follows in the service method:

//Query GameBank database
var gameBankResult = await _unitOfWork.GameBankRepository.GetGamesAsync(g =>
                                                                        g.productCode == requestDto.productCode && g.referenceId == Guid.Empty);

if (gameBankResult.Count() >= requestDto.quantity)
{
    var k = requestDto.quantity - 1;
    for (var i = k; i >= 0; --i)
    {
        gameBankResult[i].referenceId = gameRequest.referenceId;
        gameBankResult[i].requestDateTime = DateTime.Now;
        gameBankResult[i].responseDateTime = DateTime.Now;
    }

    //Update GameBank
    _unitOfWork.GameBankRepository.Update(gameBankResult[k]);

Here is the generic repo update and GetGamesAsync:

public virtual void Update(TEntity entityToUpdate)
{
    dbSet.Attach(entityToUpdate);
    context.Entry(entityToUpdate).State = EntityState.Modified;
}

public virtual async Task<List<TEntity>> GetGamesAsync(
    Expression<Func<TEntity, bool>> where)
{
    return await dbSet.Where(where).ToListAsync();
}

If there are a couple of records in the query (gameBankResult ) there is no problem, the update is very fast. But if there are let's say 2000 records it takes 2 minutes. By the way, there is always 1 result in gameBankResult, let's say a business rule. What can be the problem?


Best Regards.

 

[jmcilhinney]

I've fixed the formatting of your code. Please don't trim the leading whitespace off the first line and then leave it on the rest. You're just making it hard to read. If you depress the Alt key while selecting a block of code then you can omit the whitespace off the whole block.

 

[NoUserHere]

Do you remember that really long post I wrote on your last topic? Now you know why I wrote it.

If there are a couple of records in the query (gameBankResult ) there is no problem, the update is very fast. But if there are let's say 2000 records it takes 2 minutes.

Welcome to working with EF.

I'd guess its your SQL data types in your database, and the fact that you are probably using the wrong types for specific columns. Some columns in your database are likely not using the appropriate data types, but EF is likely assuming your datatypes are something else compared to what they actually are. When a transaction is taking place, I believe it goes through a conversion process and this is likely what is taking so much of your time to execute. I would advise running your queries outside of EF until you can see what is wrong. If it executes fine, then you know that the problem resides within EF, and is something more sinister. If it doesn't, then I'd be looking towards your system administrator for answers. Keep in mind, I'm no expert on this stuff, but that is something I would check first. Maybe someone else will have more insight or experience with this issue. EF is not great when it comes to many queries, especially if the above is true.

 

[raysefo]

I'd guess its your SQL data types in your database, and the fact that you are probably using the wrong types for specific columns. Some columns in your database are likely not using the appropriate data types, but EF is likely assuming your datatypes are something else compared to what they actually are.

Why would be the column types wrong? They seem OK to me.

Here is the table:

CREATE TABLE [dbo].[GameBanks](
    [GameBankID] [int] IDENTITY(1,1) NOT NULL,
    [referenceId] [uniqueidentifier] NOT NULL,
    [productCode] [nvarchar](max) NULL,
    [quantity] [int] NOT NULL,
    [version] [nvarchar](max) NULL,
    [requestDateTime] [datetime] NULL,
    [customerID] [int] NULL,
    [password] [nvarchar](max) NULL,
    [responseDateTime] [datetime] NULL,
    [initiationResultCode] [nvarchar](max) NULL,
    [companyToken] [nvarchar](max) NULL,
    [used] [int] NOT NULL,
    [productDescription] [nvarchar](max) NULL,
    [currency] [nvarchar](max) NULL,
    [unitPrice] [float] NOT NULL,
    [totalPrice] [float] NOT NULL,
    [ApplicationCode] [nvarchar](max) NULL,
    [estimateUnitPrice] [float] NOT NULL,
    [validatedToken] [nvarchar](max) NULL,
    [signature] [nvarchar](max) NULL,
    [status] [int] NOT NULL,
 CONSTRAINT [PK_dbo.GameBanks] PRIMARY KEY CLUSTERED
(
    [GameBankID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

[NoUserHere]

Post back your structure for these declarations which would be inserted into these fields.

 

[raysefo]

Here is entity:

public class GameBank
    {
        public int GameBankID { get; set; }
        public Guid referenceId { get; set; }
        public string productCode { get; set; }
        public int quantity { get; set; }
        public string version { get; set; }
        public DateTime? requestDateTime { get; set; } = DateTime.Now;
        public int? customerID { get; set; }
        public string password { get; set; }
        public DateTime? responseDateTime { get; set; } = DateTime.Now;
        public string initiationResultCode { get; set; }
        public string companyToken { get; set; }
        public int used { get; set; }
        public string productDescription { get; set; }
        public string currency { get; set; }
        public double unitPrice { get; set; }
        public double totalPrice { get; set; }
        public string ApplicationCode { get; set; }
        public double estimateUnitPrice { get; set; }
        public string validatedToken { get; set; }
        public string signature { get; set; }
        public int status { get; set; }
        public virtual List<GameBankPin> coupons { get; set; }
    }

 

[raysefo]

By the way, can I somehow narrow the linq query? Is it possible to add Take() ?

 

[Skydiver]

Have you run with a profiler? Where is most of the time spent? In the call to Update() or in the call to GetGameAsync()?

As a quick aside, going through a list from the backend going forward is not very CPU cache friendly, but that is an optimization you can tackle later if your profiler is showing that it is the for loop which is taking a lot of time.

 

[raysefo]

I ran the profiler, there is no performance issue with the queries. The problem is Automapper:

//Query GameBank database
var gameBankResult = await _unitOfWork.GameBankRepository.GetGamesAsync(g =>
g.productCode == requestDto.productCode && g.referenceId == Guid.Empty);

if (gameBankResult.Count() >= requestDto.quantity)
{
     var k = requestDto.quantity - 1;
     for (var i = k; i >= 0; --i)
      {
            gameBankResult[i].referenceId = gameRequest.referenceId;
            gameBankResult[i].requestDateTime = DateTime.Now;
            gameBankResult[i].responseDateTime = DateTime.Now;
      }

//Update GameBank
_unitOfWork.GameBankRepository.Update(gameBankResult[k]);

var gameBankConfirmResponse = iMapper.Map<IList<GameBank>, IList<GameConfirmResponse>>(gameBankResult);

 

[raysefo]

I changed the code as follows now gameBankResult becomes 2 or 3 item list at most according to the quantity. Now there is no mapping latency.

//Query GameBank database
var gameBankResult = await _unitOfWork.GameBankRepository.GetGamesAsync(g =>
g.productCode == requestDto.productCode && g.referenceId == Guid.Empty);

gameBankResult = gameBankResult.Take(requestDto.quantity).ToList();

 

[jmcilhinney]

I see that you simply ignored post #2.

 

[raysefo]

sorry