This query uses a lot of CPU and takes about 30 seconds to execute. What can I do in order to improve it?
The reason it uses joins is because there weren't proper relationships. I just added ones and started to replace all repositories since this is using EF Core and EF Core is already based on repositories and Unit of work.
var employeeLocations = await _dbContext.Set<LocationEmployee>()
.Include(x => x.Employee)
.Where(x => x.Employee.Email == _userEmail && x.IsActive && (x.CanBuy || x.CanSell))
.ToListAsync(cancellationToken);
What else? Ideas?
I have a feeling that the Union is what's causing the slowness.
public class GetAllReviewAndReleaseQuery : IRequest<ListDto<ReviewAndReleaseItemDto>>
{
public ReviewAndReleaseFilterDto Filter { get; set; }
}
public sealed class GetAllReviewAndReleaseQueryHandler : IRequestHandler<GetAllReviewAndReleaseQuery, ListDto<ReviewAndReleaseItemDto>>
{
readonly MarketTransactionRepository _marketTransaction;
readonly ProductRepository _productRepository;
readonly CommodityRepository _commodityRepository;
readonly ILogger<GetAllReviewAndReleaseQueryHandler> _logger;
readonly OfferMonitoringRepository _offerMonitoringRepository;
readonly OfferRepository _offerRepository;
readonly EmployeeRepository _employeeRepository;
readonly HedgeAccountRepository _hedgeAccountRepository;
readonly string _userEmail;
readonly EmployeeLocationRepository _employeeLocationRepository;
readonly ContractRepository _contractRepository;
readonly CustomerRepository _customerRepository;
public GetAllReviewAndReleaseQueryHandler(MarketTransactionRepository marketTransaction,
ProductRepository productRepository,
CommodityRepository commodityRepository,
OfferMonitoringRepository offerMonitoringRepository,
OfferRepository offerRepository,
EmployeeRepository employeeRepository,
HedgeAccountRepository hedgeAccountRepository,
ILogger<GetAllReviewAndReleaseQueryHandler> logger,
CurrentUser userEmail, EmployeeLocationRepository employeeLocationRepository, ContractRepository contractRepository, CustomerRepository customerRepository)
{
_marketTransaction = marketTransaction;
_logger = logger;
_productRepository = productRepository;
_commodityRepository = commodityRepository;
_offerMonitoringRepository = offerMonitoringRepository;
_offerRepository = offerRepository;
_employeeRepository = employeeRepository;
_hedgeAccountRepository = hedgeAccountRepository;
_userEmail = userEmail?.Email ?? CurrentUser.InternalUser;
_employeeLocationRepository = employeeLocationRepository;
_contractRepository = contractRepository;
_customerRepository = customerRepository;
}
public async Task<ListDto<ReviewAndReleaseItemDto>> Handle(GetAllReviewAndReleaseQuery request, CancellationToken cancellationToken)
{
if (!request.Filter.ValidatePagingParametersAreGreaterThanZero())
{
throw new PaginationNotValidException("Pagination parameters are not valid, please review your parameters");
}
var employeeLocations = from el in _employeeLocationRepository.GetDbSet()
join emp in _employeeRepository.GetDbSet() on el.EmployeeId equals emp.Id
where emp.Email == _userEmail && el.IsActive && (el.CanBuy || el.CanSell)
select el;
var dataSet1 = from f in _marketTransaction.GetDbSet()
join c in _commodityRepository.GetDbSet() on f.CommodityId equals c.Id
join p in _productRepository.GetDbSet() on c.ProductId equals p.Id
join h in _hedgeAccountRepository.GetDbSet() on f.MarketAccount equals h.Account into gha
from account in gha.DefaultIfEmpty()
join con in _contractRepository.GetDbSet() on f.ContractId equals con.Id into contract
from ct in contract.DefaultIfEmpty()
join off in _offerRepository.GetDbSet() on f.OfferId equals off.Id into offer
from of in offer.DefaultIfEmpty()
join cusForCon in _customerRepository.GetDbSet() on ct.CustomerId equals cusForCon.Id into customerForContract
from customerForCon in customerForContract.DefaultIfEmpty()
join cusForOff in _customerRepository.GetDbSet() on of.CustomerId equals cusForOff.Id into customerForOffer
from customerForOff in customerForOffer.DefaultIfEmpty()
where f.IsActive == true && (f.Source == EMarketTransactionSource.ExternalFill || employeeLocations.Any(em => em.LocationId == ct.DeliveryLocationId || em.LocationId == of.DeliveryLocationId))
select new ReviewAndReleaseDto
{
Id = f.Id,
CustomerFirstName = customerForCon == null ? (customerForCon.FirstName ?? customerForOff.FirstName) : string.Empty,
CustomerLastName = customerForCon == null ? (customerForCon.LastName ?? customerForOff.LastName) : string.Empty,
CustomerNumber = customerForCon == null ? (customerForCon.Number ?? customerForOff.Number) : string.Empty,
CustomerId = customerForCon == null ? customerForOff.Id : customerForCon.Id,
CreationDate = f.CreatedOn,
UpdatedOn = f.UpdatedOn,
IsReal = true,
FuturesPrice = f.FuturesPrice,
IsSell = f.IsSell,
MarketAccount = f.MarketAccount,
WasAcknowledge = f.WasAcknowledge,
PassFill = f.PassFill,
IsGtc = f.IsGtc,
Expiration = f.Expiration,
Lots = f.Lots,
WorkingLots = f.WorkingLots,
ProductId = c.ProductId,
InstrumentCode = p.Code,
MarketPrice = f.MarketPrice,
FuturesMonth = f.FuturesMonth,
MarketId = Convert.ToString(f.MarketId),
AccountName = Convert.ToString(account.Name),
InternalCode = Convert.ToString(f.InternalCode),
Source = f.Source == EMarketTransactionSource.Contract ? EMarketTransactionSource.Contract :
f.Source == EMarketTransactionSource.Accumulation ? EMarketTransactionSource.Accumulation :
f.Source == EMarketTransactionSource.Offer ? EMarketTransactionSource.Offer : EMarketTransactionSource.ExternalFill,
Type = f.Type == EMarketTransactionType.Market ? EMarketTransactionType.Market : EMarketTransactionType.Limit,
Event = f.Event == ETransactionEvent.Creation ? ETransactionEvent.Creation :
f.Event == ETransactionEvent.Edition ? ETransactionEvent.Edition :
f.Event == ETransactionEvent.Cancelation ? ETransactionEvent.Cancelation :
f.Event == ETransactionEvent.Roll ? ETransactionEvent.Roll :
f.Event == ETransactionEvent.Book ? ETransactionEvent.Book :
f.Event == ETransactionEvent.RollBack ? ETransactionEvent.RollBack :
f.Event == ETransactionEvent.PartiallyFilled ? ETransactionEvent.PartiallyFilled :
f.Event == ETransactionEvent.Filled ? ETransactionEvent.Filled : ETransactionEvent.Completed,
State = f.State.Value == EMarketTransactionState.Ready ? EMarketTransactionState.Ready :
f.State.Value == EMarketTransactionState.Denied ? EMarketTransactionState.Denied :
f.State.Value == EMarketTransactionState.Pending ? EMarketTransactionState.Pending :
f.State.Value == EMarketTransactionState.Working ? EMarketTransactionState.Working :
f.State.Value == EMarketTransactionState.PartiallyFilled ? EMarketTransactionState.PartiallyFilled :
f.State.Value == EMarketTransactionState.Filled ? EMarketTransactionState.Filled :
f.State.Value == EMarketTransactionState.Canceled ? EMarketTransactionState.Canceled :
f.State.Value == EMarketTransactionState.Rejected ? EMarketTransactionState.Rejected :
EMarketTransactionState.Expired
};
var dataSet2 = from f in _offerMonitoringRepository.GetDbSet()
join o in _offerRepository.GetDbSet() on f.OfferId equals o.Id
join c in _commodityRepository.GetDbSet() on o.CommodityId equals c.Id
join p in _productRepository.GetDbSet() on c.ProductId equals p.Id
join cusForOf in _customerRepository.GetDbSet() on o.CustomerId equals cusForOf.Id
where f.Action.Value == EOfferMonitorAction.Add && employeeLocations.Any(el => o.LocationId == el.LocationId)
select new ReviewAndReleaseDto
{
Id = f.Id,
CustomerFirstName = cusForOf.FirstName,
CustomerLastName = cusForOf.LastName,
CustomerNumber = cusForOf.Number,
CustomerId = cusForOf.Id,
CreationDate = f.CreatedOn,
UpdatedOn = f.UpdatedOn,
IsReal = false,
FuturesPrice = o.FuturesPrice ?? 0,
IsSell = !o.IsSell,
MarketAccount = 0,
WasAcknowledge = false,
PassFill = false,
IsGtc = o.Gtc,
Expiration = o.Expiration,
Lots = (int)(o.Quantity / c.LotFactor),
WorkingLots = 0,
ProductId = c.ProductId,
InstrumentCode = p.Code,
MarketPrice = o.FreightPrice,
FuturesMonth = o.FuturesMonth,
MarketId = Convert.ToString(null),
AccountName = Convert.ToString(null),
InternalCode = Convert.ToString(o.InternalCode),
Source = EMarketTransactionSource.Offer,
Type = EMarketTransactionType.Limit,
Event = f.Action.Value == EOfferMonitorAction.Cancel ? ETransactionEvent.Cancelation :
f.Action.Value == EOfferMonitorAction.Add ? ETransactionEvent.Creation : ETransactionEvent.Edition,
State = EMarketTransactionState.Pending
};
var query = dataSet1.Union(dataSet2);
query = ApplyFilters(request, query);
var tag50CurrentUser = await (from e in await _employeeRepository.GetAllEntities() where e.Email == _userEmail select e.Tag50).FirstOrDefaultAsync(cancellationToken: cancellationToken);
var data = new ListDto<ReviewAndReleaseItemDto> { Total = await query.CountAsync(cancellationToken: cancellationToken) };
if (data.Total > 0)
{
query = query.OrderByDescending(x => x.UpdatedOn ?? x.CreationDate);
query = query.GetPagedQuery(request.Filter.Start.Value, request.Filter.Limit.Value);
foreach (var reviewAndReleaseDto in
from item in query.ToList()
let customerFullName = $"{item.CustomerFirstName} {item.CustomerLastName}"
select new ReviewAndReleaseItemDto
{
Id = item.Id,
OrderNumber = item.MarketId,
Type = Helper.MapType(item.Type),
TypeName = item.Type.ToString(),
CreationDate = item.CreationDate,
UpdateDate = item.UpdatedOn,
Price = item.Type == EMarketTransactionType.Market ? null : item.FuturesPrice,
IsSell = item.IsSell,
Side = Helper.MapSide(item.IsSell),
Quantity = item.Lots,
WorkingQuantity = item.WorkingLots,
Status = Helper.MapStatusGridToString(item.State, item.Event),
AccountNumber = item.MarketAccount,
AccountName = item.AccountName,
AvgFillPrice = item.IsReal ? item.MarketPrice : 0,
WasAcknowledge = item.WasAcknowledge,
PassFill = item.PassFill,
GTC = new()
{
IsGTC = item.IsGtc,
Expiration = item.Expiration
},
FuturesMonth = item.FuturesMonth,
InternalCode = item.InternalCode,
Customer = new()
{
Id = item.CustomerId,
Name = $"{item.CustomerFirstName} {item.CustomerLastName}".Trim(),
Number = item.CustomerNumber,
},
Source = Helper.MapSource(item.Source),
Symbol = new() { Code = item.InstrumentCode },
Restrictions = new()
{
CanBeApproved = item.IsReal && item.State == EMarketTransactionState.Ready && tag50CurrentUser,
CanBeDenied = item.IsReal && item.Type == EMarketTransactionType.Market && tag50CurrentUser &&
(item.State == EMarketTransactionState.Ready || (item.State == EMarketTransactionState.Pending && item.Event == ETransactionEvent.Creation && !item.WasAcknowledge)),
CanBeResent = item.IsReal && item.State == EMarketTransactionState.Rejected && item.Type == EMarketTransactionType.Market && tag50CurrentUser,
CanBeAcknowledged = item.IsReal && item.Type == EMarketTransactionType.Market && item.State == EMarketTransactionState.Rejected && !item.WasAcknowledge && tag50CurrentUser,
CanBeEdited = item.Source == EMarketTransactionSource.ExternalFill && item.State != EMarketTransactionState.Canceled && tag50CurrentUser,
CanBeCanceled = item.Source == EMarketTransactionSource.ExternalFill && item.State != EMarketTransactionState.Canceled && tag50CurrentUser
}
})
{
data.List.Add(reviewAndReleaseDto);
}
}
_logger.ReviewAndReleaseAll();
return data;
}
private static IQueryable<ReviewAndReleaseDto> ApplyFilters(GetAllReviewAndReleaseQuery request, IQueryable<ReviewAndReleaseDto> query)
{
var endDate = request.Filter.EndDate ?? DateTime.Now.Date;
query = request.Filter.StartDate != null
? query.Where(x => x.UpdatedOn == null ? x.CreationDate.Date >= request.Filter.StartDate && x.CreationDate.Date <= endDate : x.UpdatedOn.Value.Date >= request.Filter.StartDate && x.UpdatedOn.Value.Date <= endDate)
: query.Where(x => x.UpdatedOn == null ? x.CreationDate.Date <= endDate : x.UpdatedOn.Value.Date <= endDate);
query = query.WhereIf(!string.IsNullOrEmpty(request.Filter.Number), x => x.MarketId.ToUpper().Contains(request.Filter.Number.ToUpper())
|| x.InternalCode.ToUpper().Contains(request.Filter.Number.ToUpper()));
query = query.WhereIf(!string.IsNullOrEmpty(request.Filter.AccountName), x => x.AccountName.ToUpper().Contains(request.Filter.AccountName.ToUpper()));
query = query.WhereIf(request.Filter.FuturesMonth is { Count: > 0 }, x => request.Filter.FuturesMonth.Contains(x.FuturesMonth));
query = query.WhereIf(request.Filter.Symbol is { Count: > 0 }, x => request.Filter.Symbol.Contains(x.ProductId));
query = query.WhereIf(request.Filter.CustomerId is { Count: > 0 }, x => request.Filter.CustomerId.Contains(x.CustomerId));
query = query.WhereIf(!string.IsNullOrEmpty(request.Filter.ContractOrCustomer), x => x.InternalCode.Contains(request.Filter.ContractOrCustomer) ||
x.CustomerFirstName.Contains(request.Filter.ContractOrCustomer) ||
x.CustomerLastName.Contains(request.Filter.ContractOrCustomer) ||
x.CustomerNumber.Contains(request.Filter.ContractOrCustomer));
if (request.Filter.Status is { Count: > 0 })
{
var listStatusFilter = request.Filter.Status.Select(Helper.MapStatusToEnum).ToList();
query = query.Where(x => listStatusFilter.Contains(x.State));
}
return query;
}
}
public class MarketTransactionConfiguration : IEntityTypeConfiguration<MarketTransaction>
{
public void Configure(EntityTypeBuilder<MarketTransaction> builder)
{
builder.ConfigureBase();
builder.ConfigureAuditable();
builder.ToTable("MarketTransaction", SchemaName.Transaction);
builder.OwnsOne(o => o.State)
.Property(p => p.Value)
.HasColumnName("State")
.HasConversion(
v => v.ToString(),
v => (EMarketTransactionState)Enum.Parse(typeof(EMarketTransactionState), v)
).HasMaxLength(15).IsConcurrencyToken();
builder.Property(x => x.Type).HasMaxLength(6).HasConversion(
v => v.ToString(),
v => (EMarketTransactionType)Enum.Parse(typeof(EMarketTransactionType), v)
);
builder.Property(x => x.Event).HasMaxLength(11).HasConversion(
v => v.ToString(),
v => (ETransactionEvent)Enum.Parse(typeof(ETransactionEvent), v)
);
builder.Property(x => x.Source).HasMaxLength(18).HasConversion(
v => v.ToString(),
v => (EMarketTransactionSource)Enum.Parse(typeof(EMarketTransactionSource), v)
);
builder.Property(x => x.MarketAccount).HasMaxLength(500);
builder.Property(x => x.MarketId).HasMaxLength(500);
builder.Property(x => x.MarketInformation);
builder.Property(x => x.MarketPrice).HasColumnType(ColumnType.Decimal4).IsRequired();
builder.Property(x => x.ContractPrice).HasColumnType(ColumnType.Decimal4).IsRequired();
builder.Property(x => x.FuturesPrice).HasColumnType(ColumnType.Decimal4).IsRequired();
builder.Property(x => x.OldFuturesPrice).HasColumnType(ColumnType.Decimal4).IsRequired();
builder.Property(x => x.IsSell).IsRequired();
builder.Property(x => x.Quantity).IsRequired().HasColumnType(ColumnType.Decimal2);
builder.Property(x => x.OldQuantity).IsRequired().HasColumnType(ColumnType.Decimal2);
builder.Property(x => x.Lots).IsRequired();
builder.Property(x => x.WorkingLots).IsRequired();
builder.Property(x => x.ThresholdUsed).IsRequired();
builder.Property(x => x.CommodityId).IsRequired();
builder.Property(x => x.CommodityName).IsRequired();
builder.Property(x => x.Comments);
builder.Property(x => x.Instrument).IsRequired();
builder.Property(x => x.ContractId);
builder.Property(x => x.OfferId);
builder.Property(x => x.FuturesMonth).HasMaxLength(24).IsRequired();
builder.Property(x => x.ClientNumber).HasMaxLength(30).IsRequired();
builder.Property(x => x.InternalCode).HasMaxLength(22);
builder.Property(x => x.InternalNumber).HasMaxLength(30).IsRequired();
builder.Property(x => x.IsGtc).IsRequired();
builder.Property(x => x.CropYear).IsRequired();
builder.Property(x => x.WasAcknowledge).IsRequired();
builder.Property(x => x.WasAlreadySent).IsRequired();
builder.Property(x => x.Expiration);
builder.Property(x => x.PassFill);
builder.HasOne(x => x.Commodity)
.WithMany()
.HasForeignKey(x => x.CommodityId);
builder.HasOne(x => x.Contract)
.WithMany()
.HasForeignKey(x => x.ContractId)
.OnDelete(DeleteBehavior.SetNull);
builder.HasOne(x => x.Offer)
.WithMany()
.HasForeignKey(x => x.OfferId)
.OnDelete(DeleteBehavior.SetNull);
builder.HasMany(x => x.OrderFills)
.WithOne()
.IsRequired()
.OnDelete(DeleteBehavior.Cascade);
}
}
I suggest you to try the following query:
var employeeLocations = await _dbContext.Set<LocationEmployee>()
.Where(x => x.Employee.Email == _userEmail &&
x.IsActive && (x.CanBuy || x.CanSell))
.Select(x => new
{
LocationEmployee = x,
Employee = x.Employee
})
.ToListAsync(cancellationToken);
The above query does not try not to load all the LocationEmployee tables data. Instead it pass a sub set of data via the Select.
Furthermore, make sure that you have indexes added to the properties Employee.Email,IsActive,CanBuy and CanSell columns or attributes on the database side.