I have a linq query that is pulling over a paged list of items that has pulls back 73,000 paged rows with a row count of 10. So 7,300 pages. This all works just fine until I get over 500 pages or so. And if I click on the last page (page 7,300) the linq query times out. I am not the best at writing linq queries and having a hard time figuring how I can rewrite it to fix the issue. Any help would be greatly appreciated. Below is the query.
query = from pc in _paidClaimsRepository.GetAll()
join pcl in _paidClaimLinesRepository.GetAll() on pc.Id equals pcl.ClaimId
join cp in _claimpaymentRepository.GetAll() on pc.PaymentId equals cp.Id
where cp.Id == input.Id
select new BillingClaimListReportDto
{
Id = cp.Id,
PayeeId = cp.PayeeId,
InvoiceNum = pc.ClaimId,
PersonId = pc.PersontId,
Last = pc.PersonLast,
First = pc.PersonFirst,
Hcpc = pcl.ProcedureCode,
ChargedAmount = pcl.ChargedAmount,
PaidAmount = pcl.PaidAmount,
UnitsPaid = pcl.UnitsPaid,
ServiceDate = pcl.ServiceDate,
PayersClaimId = pc.PayersClaimId
var remitsCount = await query.CountAsync();
var billingRemitsList = await query
.OrderBy(input.Sorting)
.PageBy(input)
.ToListAsync();
5 Answer(s)
-
0
the OrderBy is for sure what is creating the issue. I take that out of there and it is perfectly fine. But then I can't do sorting.
-
0
Hi,
Actually, this question is not related with the aspnet-zero. In this question, there are many arguments to know to improve your query perfomance. May be you should ask this question in a common software QA site like <a class="postlink" href="http://stackoverflow.com/">http://stackoverflow.com/</a>
-
0
<cite>alirizaadiyahsi: </cite> Hi,
Actually, this question is not related with the aspnet-zero. In this question, there are many arguments to know to improve your query performance. May be you should ask this question in a common software QA site like <a class="postlink" href="http://stackoverflow.com/">http://stackoverflow.com/</a>
I disagree with you because I am using the ASP.NET Zero repository to query and the PagedResultDto which is also a class built into ABP. I believe this is a relevant question.
-
0
Hi @joe704la,
Can you check executing SQL query using SQL profiler ? If your entity is a soft delete entity, ABP adds IsDeleted = 0 to your query as you know. Also If your entity is a MayHaveTenant or MustHaveTenant entity, TenantId filter is added as well.
Probably you need to add an index to your table. You can decide the index columns according to executed SQL query.
Thanks.
-
0
Thank you @ismcagdas, I will check those things out.
