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.

• 1
  \$\begingroup\$use the RAW SQL, and check the execution plan from your DBMS, check the query overall performance, and optimize its required indexes if any.\$\endgroup\$

  iSR5

  – iSR5

  2023-07-07 04:07:08 +00:00

  CommentedJul 7, 2023 at 4:07
• 2
  \$\begingroup\$What motivated you to rewrite the query using linq? Do you have any idea what kind of sql query(s) are being executed when running the linq query? Btw for the sake of quality of your question, you should include details about your table structure as well as explaining what the query is trying to achieve.\$\endgroup\$

  slepic

  – slepic

  2023-07-07 04:14:56 +00:00

  CommentedJul 7, 2023 at 4:14
• \$\begingroup\$Did you already look atTips from Microsoft ? What have you tried? Especially split queries could help if the joined tables have many rows.\$\endgroup\$

  DasKrümelmonster

  – DasKrümelmonster

  2023-07-08 12:48:54 +00:00

  CommentedJul 8, 2023 at 12:48
• \$\begingroup\$The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title tosimply state the task accomplished by the code. Please seeHow do I ask a good question?.\$\endgroup\$

  BCdotWEB

  – BCdotWEB

  2023-07-26 16:26:29 +00:00

  CommentedJul 26, 2023 at 16:26
• \$\begingroup\$It's impossible to say anything about your code without knowing the structure of your tables. Based on what you provide here, nobody can replicate this locally. Also, why use a DataTable?\$\endgroup\$

  BCdotWEB

  – BCdotWEB

  2023-07-26 16:30:27 +00:00

  CommentedJul 26, 2023 at 16:30

1 Answer1

Start asking to get answers

Find the answer to your question by asking.

Explore related questions

See similar questions with these tags.