0
\$\begingroup\$

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.

\$\endgroup\$
5
  • 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
    Jul 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
    Jul 7, 2023 at 4:14
  • \$\begingroup\$ Did you already look at Tips from Microsoft ? What have you tried? Especially split queries could help if the joined tables have many rows. \$\endgroup\$ Jul 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 to simply state the task accomplished by the code. Please see How do I ask a good question?. \$\endgroup\$
    – BCdotWEB
    Jul 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
    Jul 26, 2023 at 16:30

1 Answer 1

0
\$\begingroup\$

Many times the solution is to do a tunning of the database. For example, you can add indexes to table key columns or to the fields by which you want to sort.

\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.