Can someone help me optimize this code especially the LINQ query. It takes 8-12 seconds just to return a response when I'm calling the endpoint. Its super slow, and my whole endpoint call is taking about 30 seconds since I have more functions to call after this. It is a big database that returns many rows and maybe someone can help me to optimize it or give me an advice on how to make it efficient and not take 8-12 seconds to complete. Thank you very much!
This is a function that I call from one of my endpoint.
public static void PrintSlip(IConverter _converter, AcommerceApiContext _context)
{
Helper ch = new Helper(_context);
SODataProvider so = new SODataProvider();
var lstOrder = ch.fillDataHeader();
This is the LINQ query that I've converted from SQL query to LINQ. I basically have 4 tables that I have joined so that I can access their properties. The part where I take just 1 in statuses is the part where I just need 1 recent row (based on eDate) from multiple rows with the same LineID but with different statuses.
public DataTable fillDataHeader()
{
var query = (from h in _context.orderHeaders
join l in _context.orderLines on h.HeaderId equals l.HeaderId
join u in _context.endUsers on h.ShipToId equals u.AddressId
join s in _context.orderStatuses on l.LineId equals s.LineId into statuses
from status in statuses
.Where(x => x.LineId == l.LineId)
.OrderByDescending(x => x.Edate)
.Take(1).DefaultIfEmpty()
where !l.IsDigitalGoods == true && h.SlipFlag == false && h.Attribute2 == null
&& (!h.OrderStatus.Contains("cancel") && (status.StatusName == "pending" || status.StatusName == "ready_to_ship" || status.StatusName == "packed"))
&& l.IsActive == true
orderby h.HeaderId descending
select new
{
//Add Order Date Here
HeaderID = h.HeaderId,
OrderNumber = h.OrderNumber,
StoreName = l.StoreName,
OrderDate = h.CreationDate,
SLATimestamp = l.Slatimestamp,
Marketplace = h.Attribute1,
Address = u.Address1 + (u.Address2 != "" ? ", " + u.Address2 : "") + (u.SubDistrict != "" ? ", " + u.SubDistrict : "") + (u.City != "" ? ", " + u.City : "") + (u.Province != "" ? ", " + u.Province : "") + (u.PostalCode != "" ? ", " + u.PostalCode : ""),
OrderStatus = h.OrderStatus,
SlipFlag = h.SlipFlag,
Addressee = u.Addressee
}).Distinct();
DataTable lstOrder = new DataTable();
// Add columns to the DataTable based on the properties in the query result
lstOrder.Columns.Add("HeaderID", typeof(int));
lstOrder.Columns.Add("OrderNumber", typeof(string));
lstOrder.Columns.Add("StoreName", typeof(string));
lstOrder.Columns.Add("OrderDate", typeof(string));
lstOrder.Columns.Add("SLATimestamp", typeof(string));
lstOrder.Columns.Add("Marketplace", typeof(string));
lstOrder.Columns.Add("Address", typeof(string));
lstOrder.Columns.Add("OrderStatus", typeof(string));
lstOrder.Columns.Add("SlipFlag", typeof(bool));
lstOrder.Columns.Add("Addressee", typeof(string));
foreach (var item in query)
{
lstOrder.Rows.Add(
item.HeaderID,
item.OrderNumber,
item.StoreName,
item.OrderDate,
item.SLATimestamp,
item.Marketplace,
item.Address,
item.OrderStatus,
item.SlipFlag,
item.Addressee
);
}
return lstOrder;
}
This is the Raw SQL query that I've converted to LINQ query above
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY h.HeaderID
ORDER BY h.HeaderID DESC) AA, h.*, h.Attribute1 Marketplace,
CONVERT(VARCHAR(10), CreationDate, 101) + ' ' + CONVERT(VARCHAR(8), CreationDate, 108) [OrderDate], u.Address1 + CASE WHEN u.Address2 != ''
THEN ', ' + u.Address2 ELSE '' END + CASE WHEN u.SubDistrict != '' THEN ', ' + u.SubDistrict ELSE '' END + CASE WHEN u.City != ''
THEN ', ' + u.City ELSE '' END + CASE WHEN u.Province != '' THEN ', ' + u.Province ELSE '' END + CASE WHEN u.PostalCode != ''
THEN ', ' + u.PostalCode ELSE '' END [Address],
COALESCE(l.SLATimestamp, NULL) SLATimestamp,
l.StoreName,
u.Addressee
FROM [OrderHeader] h
INNER JOIN [OrderLine] l ON l.HeaderID = h.HeaderID
LEFT JOIN (SELECT * FROM (SELECT ROW_NUMBER() OVER(Partition by LineID ORDER BY EDate DESC) AS AA, *
FROM OrderStatus) tbl
WHERE AA = 1) s ON s.LineID = l.LineID
INNER JOIN [EndUser] u ON h.ShipToID = u.AddressID WHERE l.[IsDigitalGoods] = 0 AND Slip_Flag = 0 AND h.ATTRIBUTE2 IS NULL
AND ( OrderStatus NOT LIKE '%cancel%' AND s.StatusName IN ('pending','ready_to_ship')) AND l.IsActive = 1) tbl
WHERE AA = 1
ORDER BY HeaderID desc
I have tried many ways to modify the LINQ query but it doesn't have the same result with the result from the SQL query. The LINQ query above is the only one that returns the same result.
