I am currently debugging the system becauae it seems to load all the data. My system has a 4200 records. Options such as serverSize and processing in Ajax are enabled but it is still not working. I want to make this faster.
This is the entity class:
public List<NonconformingServiceModel> GetAllNSIssue(int UserID)
{
using (SIRManagementSystemEntities _entities = new SIRManagementSystemEntities())
{
NonconformingServiceModel NSModel = new NonconformingServiceModel();
var date1 = new DateTime(2000, 1, 1, 1, 0, 0);
List<NonconformingServiceModel> result = new List<NonconformingServiceModel>();
var userData = (from _user in _entities.Administrators where _user.UserID == UserID select _user).FirstOrDefault();
if (userData != null)
{
if (userData.UserType == 1 || userData.UserType == 0 || userData.UserID == 38)
{
result = (from _NSDetails in _entities.NonconformingServices
join _creator in _entities.Administrators on _NSDetails.CreatedBy equals _creator.UserID
join _SIRDetails in _entities.SIR_InitialDetail on _NSDetails.NS_ID equals _SIRDetails.NSCCRefID into _sir
from _SIRDetails in _sir.DefaultIfEmpty()
select new NonconformingServiceModel
{
ID = _NSDetails.NS_ID,
Type = _NSDetails.Type,
Category = _NSDetails.Category,
Reason = _NSDetails.Reason,
Remarks = _NSDetails.Remarks,
ModeThru = _NSDetails.Mode,
DateDelivered = _NSDetails.DateDelivered,
LoadingDate = _NSDetails.LoadingDate,
DateIssue = _NSDetails.DateIssue,
Origin = _NSDetails.Origin,
Destination = _NSDetails.Destination,
CartonNumber = _NSDetails.CartonNumber,
ShipmentNumber = _NSDetails.ShipmentNumber,
ShipperName = _NSDetails.ShipperName,
ConsigneeName = _NSDetails.ConsigneeName,
Address = _NSDetails.Address,
NSGroup = _NSDetails.NSGroup,
NSCode = _NSDetails.NSCode,
OtherType = _NSDetails.OtherType,
CreatedByID = _NSDetails.CreatedBy,
CreatedByName = _creator.FirstName " " _creator.LastName,
SIRCode = _SIRDetails.Code == null ? "" : _SIRDetails.Code,
SIRDateIssue = _SIRDetails.IssueDate == null ? date1 : _SIRDetails.IssueDate,
SIRDueDate = _SIRDetails.DueDate == null ? date1 : _SIRDetails.DueDate,
Status = _SIRDetails.Code == null ? 0 : _SIRDetails.Status,
}).ToList();
}
else
{
result = (from _creator in _entities.Administrators
join _NSDetails in _entities.NonconformingServices on _creator.UserID equals _NSDetails.CreatedBy
join _mapping in _entities.CCNSDEPTMappings on _NSDetails.NSCode equals _mapping.NSCode
join _SIRDetails in _entities.SIR_InitialDetail on _NSDetails.NS_ID equals _SIRDetails.NSCCRefID into _sir
from _SIRDetails in _sir.DefaultIfEmpty()
where _creator.Department == userData.Department || _mapping.ConcernDeptID == userData.Department || _creator.Department == userData.SecondDept || _mapping.ConcernDeptID == userData.SecondDept
select new NonconformingServiceModel
{
ID = _NSDetails.NS_ID,
Type = _NSDetails.Type,
Category = _NSDetails.Category,
Reason = _NSDetails.Reason,
Remarks = _NSDetails.Remarks,
ModeThru = _NSDetails.Mode,
DateDelivered = _NSDetails.DateDelivered,
LoadingDate = _NSDetails.LoadingDate,
DateIssue = _NSDetails.DateIssue,
Origin = _NSDetails.Origin,
Destination = _NSDetails.Destination,
CartonNumber = _NSDetails.CartonNumber,
ShipmentNumber = _NSDetails.ShipmentNumber,
ShipperName = _NSDetails.ShipperName,
ConsigneeName = _NSDetails.ConsigneeName,
Address = _NSDetails.Address,
NSGroup = _NSDetails.NSGroup,
NSCode = _NSDetails.NSCode,
OtherType = _NSDetails.OtherType,
CreatedByID = _NSDetails.CreatedBy,
CreatedByName = _creator.FirstName " " _creator.LastName,
SIRCode = _SIRDetails.Code == null ? "" : _SIRDetails.Code,
SIRDateIssue = _SIRDetails.IssueDate == null ? date1 : _SIRDetails.IssueDate,
SIRDueDate = _SIRDetails.DueDate == null ? date1 : _SIRDetails.DueDate,
Status = _SIRDetails.Code == null ? 0 : _SIRDetails.Status,
}).ToList();
result = result.GroupBy(x => x.NSCode).Select(x => x.FirstOrDefault()).ToList();
}
This is the view model:
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SIMS.Model.Models
{
public class NonconformingServiceModel
{
public int ID { get; set; }
public byte Type { get; set; }
public string TypeName { get; set; }
public string OtherType { get; set; }
public byte Category { get; set; }
public string CategoryName { get; set; }
public string OtherCategory { get; set; }
public string Reason { get; set; }
public string Remarks { get; set; }
public string ModeThru { get; set; }
public DateTime? DateDelivered { get; set; }
public DateTime? LoadingDate { get; set; }
public DateTime DateIssue { get; set; }
public string ConcernedDeptString { get; set; }
public List<int> ConcernedDeptArray { get; set; }
public List<string> ConcernedDeptName { get; set; }
public string Origin { get; set; }
public string Destination { get; set; }
public string CartonNumber { get; set; }
public string ShipmentNumber { get; set; }
public string ShipperName { get; set; }
public string ConsigneeName { get; set; }
public string Address { get; set; }
public string NSGroup { get; set; }
public int CreatedByID { get; set; }
public string CreatedByName { get; set; }
public int CreatedByDept { get; set; }
public int UserType { get; set; }
public string NSCode { get; set; }
public byte? Status { get; set; }
public int? SIRID { get; set; }
public string SIRCode { get; set; }
public DateTime? SIRDateIssue { get; set; }
public DateTime? SIRDueDate { get; set; }
public DateTime? SubmissionDate { get; set; }
public DateTime? CEDueDate { get; set; }
public DateTime? DetectionDate { get; set; }
public string FormatIssueDate { get; set; }
public int UserID { get; set; }
public int? Company { get; set; }
}
public class FilterModel
{
public string Month { get; set; }
public string Year { get; set; }
public byte? Type { get; set; }
public byte? Category { get; set; }
public byte? Department { get; set; }
public byte? Status { get; set; }
}
}
This is the Controller class:
{
JsonResult _json = new JsonResult();
NonconformingServiceManager _NSManager = new NonconformingServiceManager();
_json.Data = _NSManager.GetAllNSIssue(UserID);
_json.MaxJsonLength = Int32.MaxValue;
return _json;
}
And this is the View:
<!-- Begin Page Content -->
<div >
<nav aria-label="breadcrumb">
<ol >
<li ><a href="/Home/Dashboard">Dashboard</a></li>
<li aria-current="page">Nonconforming Service</li>
</ol>
</nav>
<div >
<div >
<h6 >Nonconforming Services Log</h6>
<div >
<button type="button" onclick="window.location.reload();">Show All Data</button>
<button type="button" data-toggle="modal" data-target=".modal-filter-table" ><i ></i>Sort by</button>
<button type="button" onclick="CreateNewConcern()" ><i ></i> Create New NS</button>
</div>
</div>
<div >
<div >
<table id="NonconformingServiceLogGrid" style="width:100%">
<thead>
<tr>
<th>Date Created</th>
<th>NS Code</th>
<th>SIR Code</th>
<th>Date Issue</th>
<th>Due Date</th>
<th>Submission Date</th>
<th>Type</th>
<th>Category</th>
<th>Concerned Dept.</th>
<th>SIR Status</th>
<th>CE Due Date</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Date Created</th>
<th>NS Code</th>
<th>SIR Code</th>
<th>Date Issue</th>
<th>Due Date</th>
<th>Submission Date</th>
<th>Type</th>
<th>Category</th>
<th>Concerned Dept.</th>
<th>SIR Status</th>
<th>CE Due Date</th>
</tr>
</tfoot>
</table>
</div>
</div>
</div>
</div>
CodePudding user response：
When using Entity Framework you need to keep in mind that a call to ToList() and similar methods executes the query against the database. The two large queries contain several joins and only one of them contains a Where condition that reduces the amount of data that is transferred from the server to the client. Especially in the first branch, the complete result of the joins is transferred resulting in a severe performance hit.
In order to reduce this, you should remove the ToList statements in the branches of the if statements and only keep the one after the grouping. This also means that you have to change the type of the result variable from List<T> to IQueryable<T>:
IQueryable<NonconformingServiceModel> result = new List<NonconformingServiceModel>();
// ...
result = result.GroupBy(x => x.NSCode).Select(x => x.FirstOrDefault()).ToList().AsQueryable();
This will reduce the amount of data that is transferred to the result of the group. In addition, you can use Skip() and Take() to move the paging logic to the server and return only a single page:
result = result
.GroupBy(x => x.NSCode)
.Select(x => x.FirstOrDefault())
.Skip(offset)
.Take(pageSize)
.ToList()
.AsQueryable();
In the sample above, offset denotes the number of rows to skip, e.g. if you are on page 4 and you have a page size of 10, you'd skip (page - 1) * pageSize = 30 rows and take 10 rows.
If your method returns a List<T>, you can call ToList() again on the IQueryable<T>() so that the data that by then resides in memory is converted to a list.
CodePudding user response：
Just make the changes in your query as per below code. It will help you to fetch the number of records as per your requirement.
Make sure you are supply the pageNumber and pageSize from UI
public List<NonconformingServiceModel> GetAllNSIssue(int UserID,int pageNumber=1, int pageSize=1000)
{
using (SIRManagementSystemEntities _entities = new SIRManagementSystemEntities())
{
NonconformingServiceModel NSModel = new NonconformingServiceModel();
var date1 = new DateTime(2000, 1, 1, 1, 0, 0);
List<NonconformingServiceModel> result = new List<NonconformingServiceModel>();
var userData = (from _user in _entities.Administrators where _user.UserID == UserID select _user).FirstOrDefault();
if (userData != null)
{
if (userData.UserType == 1 || userData.UserType == 0 || userData.UserID == 38)
{
result = (from _NSDetails in _entities.NonconformingServices
join _creator in _entities.Administrators on _NSDetails.CreatedBy equals _creator.UserID
join _SIRDetails in _entities.SIR_InitialDetail on _NSDetails.NS_ID equals _SIRDetails.NSCCRefID into _sir
from _SIRDetails in _sir.DefaultIfEmpty()
select new NonconformingServiceModel
{
ID = _NSDetails.NS_ID,
Type = _NSDetails.Type,
Category = _NSDetails.Category,
Reason = _NSDetails.Reason,
Remarks = _NSDetails.Remarks,
ModeThru = _NSDetails.Mode,
DateDelivered = _NSDetails.DateDelivered,
LoadingDate = _NSDetails.LoadingDate,
DateIssue = _NSDetails.DateIssue,
Origin = _NSDetails.Origin,
Destination = _NSDetails.Destination,
CartonNumber = _NSDetails.CartonNumber,
ShipmentNumber = _NSDetails.ShipmentNumber,
ShipperName = _NSDetails.ShipperName,
ConsigneeName = _NSDetails.ConsigneeName,
Address = _NSDetails.Address,
NSGroup = _NSDetails.NSGroup,
NSCode = _NSDetails.NSCode,
OtherType = _NSDetails.OtherType,
CreatedByID = _NSDetails.CreatedBy,
CreatedByName = _creator.FirstName " " _creator.LastName,
SIRCode = _SIRDetails.Code == null ? "" : _SIRDetails.Code,
SIRDateIssue = _SIRDetails.IssueDate == null ? date1 : _SIRDetails.IssueDate,
SIRDueDate = _SIRDetails.DueDate == null ? date1 : _SIRDetails.DueDate,
Status = _SIRDetails.Code == null ? 0 : _SIRDetails.Status,
}).Skip((pageSize*(pageNumber-1))).Take(pageSize).ToList();
}
else
{
result = (from _creator in _entities.Administrators
join _NSDetails in _entities.NonconformingServices on _creator.UserID equals _NSDetails.CreatedBy
join _mapping in _entities.CCNSDEPTMappings on _NSDetails.NSCode equals _mapping.NSCode
join _SIRDetails in _entities.SIR_InitialDetail on _NSDetails.NS_ID equals _SIRDetails.NSCCRefID into _sir
from _SIRDetails in _sir.DefaultIfEmpty()
where _creator.Department == userData.Department || _mapping.ConcernDeptID == userData.Department || _creator.Department == userData.SecondDept || _mapping.ConcernDeptID == userData.SecondDept
select new NonconformingServiceModel
{
ID = _NSDetails.NS_ID,
Type = _NSDetails.Type,
Category = _NSDetails.Category,
Reason = _NSDetails.Reason,
Remarks = _NSDetails.Remarks,
ModeThru = _NSDetails.Mode,
DateDelivered = _NSDetails.DateDelivered,
LoadingDate = _NSDetails.LoadingDate,
DateIssue = _NSDetails.DateIssue,
Origin = _NSDetails.Origin,
Destination = _NSDetails.Destination,
CartonNumber = _NSDetails.CartonNumber,
ShipmentNumber = _NSDetails.ShipmentNumber,
ShipperName = _NSDetails.ShipperName,
ConsigneeName = _NSDetails.ConsigneeName,
Address = _NSDetails.Address,
NSGroup = _NSDetails.NSGroup,
NSCode = _NSDetails.NSCode,
OtherType = _NSDetails.OtherType,
CreatedByID = _NSDetails.CreatedBy,
CreatedByName = _creator.FirstName " " _creator.LastName,
SIRCode = _SIRDetails.Code == null ? "" : _SIRDetails.Code,
SIRDateIssue = _SIRDetails.IssueDate == null ? date1 : _SIRDetails.IssueDate,
SIRDueDate = _SIRDetails.DueDate == null ? date1 : _SIRDetails.DueDate,
Status = _SIRDetails.Code == null ? 0 : _SIRDetails.Status,
}).ToList();
result = result.GroupBy(x => x.NSCode).Select(x => x.FirstOrDefault()).Skip((pageSize*(pageNumber-1))).Take(pageSize).ToList();
}