Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Tim 26 posts 140 karma points
    Aug 12, 2020 @ 20:02
    Tim
    0

    Timeout Downloading Orders

    Hi,

    I have an issue trying to download the orders from the API between certain dates. I'm using the example code that Matt sent over to do this:

    IEnumerable<OrderEntity> orderEntities = UmbracoContext.Current.Application.DatabaseContext.Database.Fetch<OrderEntity>($"SELECT * FROM TeaCommerce_Order WHERE DateFinalized is not NULL AND [Id] IN ('{string.Join("','", GetSelectedOrderIds())}')");
    IEnumerable<Order> orders = OrderService.Instance.Get(storeId, orderEntities.Select(entity => entity.Id));
    
    string csv = orders.OrderByDescending(order => order.DateFinalized).ToList().ToChipCSV(storeId);
    

    The problem we've hit is that if the date range returns 1000s of orders, the script times out. Is there a more efficient way of getting the orders out of the system using the API? Or would I be better writing some custom SQL to do it?

    I'm going to do some testing in the morning to see if I can see where the bottleneck is.

  • Matt Brailsford 2768 posts 14049 karma points MVP 7x c-trib
    Aug 13, 2020 @ 08:59
    Matt Brailsford
    0

    Hey Tim,

    I don't think there is going to be a more optimal TC API for this, so if you do need to make this more performant, I think it's going to have to involve some custom SQL.

    I guess it depends what properties you need from the order as loading orders into memory will cause them to run through the calculation process, so to have that occur for every order, could be pretty time consuming. If on the other hand, all the data you need is present in the DB tables, you might be better off loading the raw values and formatting them yourself, saving the construction of the order entity.

    /Matt

  • Tim 26 posts 140 karma points
    Aug 13, 2020 @ 15:28
    Tim
    0

    I think I've identified some performance issues in the ToCsv() method in your code example around how it's building the strings. I'm going to refactor it and do some tests, if I get it working faster, I'll post the updated code here for you!

  • Tim 26 posts 140 karma points
    Aug 20, 2020 @ 13:12
    Tim
    100

    The best way ended up being to use SQL. For my report, I don't care about the prices (as everything is free currently), and I need to concatenate the order lines together. This is the SQL I used, and it's BLISTERINGLY quick (<1s compared to about 40s previously). You could probably make it faster if you were willing to use stored procedures and table variables.

    IEnumerable<OrderReport> orders = UmbracoContext.Current.Application.DatabaseContext.Database.Fetch<OrderReport>(@";WITH OrderLines_CTE (OrderId, OrderItems)  
                    AS  
                    (  
                        SELECT a.OrderId, STRING_AGG(CONCAT(a.Sku, ': ', a.Name, ' - ', b.Value, ' ', c.Value), CHAR(10)) AS OrderItems
                        FROM TeaCommerce_OrderLine a
                        INNER JOIN TeaCommerce_CustomOrderLineProperty b ON a.Id = b.OrderLineId AND b.Alias = 'customProp1'
                        INNER JOIN TeaCommerce_CustomOrderLineProperty c ON a.Id = c.OrderLineId AND c.Alias = 'customProp2'
                        GROUP BY a.OrderId
                    ),
                    Orders_CTE (Id, OrderNumber, DateFinalized, Fullname, Email, AddressLine1, City, Postcode, Country, Comments, Notes, OrderStatusId, StoreId)
                    AS
                    (
                        SELECT a.Id, a.OrderNumber, a.DateFinalized, CONCAT(a.FirstName, ' ', a.LastName) AS FullName, a.Email, b.Value AS AddressLine1, c.Value AS City, d.Value AS Postcode, 
                        g.Name AS Country, COALESCE(e.Value, '') AS Comments, COALESCE(f.Value, '') AS Notes, a.OrderStatusId, a.StoreId
                        FROM TeaCommerce_Order a
                        INNER JOIN TeaCommerce_CustomOrderProperty b ON a.Id = b.OrderId AND b.Alias = 'shipping_streetAddress'
                        INNER JOIN TeaCommerce_CustomOrderProperty c ON a.Id = c.OrderId AND c.Alias = 'shipping_city'
                        INNER JOIN TeaCommerce_CustomOrderProperty d ON a.Id = d.OrderId AND d.Alias = 'shipping_zipCode'
                        LEFT OUTER JOIN TeaCommerce_CustomOrderProperty e ON a.Id = e.OrderId AND e.Alias = 'comments'
                        LEFT OUTER JOIN TeaCommerce_CustomOrderProperty f ON a.Id = f.OrderId AND f.Alias = 'OrderNotes'
                        INNER JOIN TeaCommerce_Region g ON a.ShippingCountryId = g.Id
                        WHERE a.DateFinalized IS NOT NULL 
    
                    )
                    SELECT a.*, b.OrderItems
                    FROM Orders_CTE a
                    INNER JOIN OrderLines_CTE b ON a.Id = b.OrderId
                    WHERE a.DateFinalized >= @0 AND 
                        a.DateFinalized <= @1 AND
                        a.StoreId = @2 AND 
                        a.OrderStatusId = @3
                    ORDER BY a.DateFinalized DESC", fromDate, toDate, storeId, orderStatusId);
    
  • Matt Brailsford 2768 posts 14049 karma points MVP 7x c-trib
    Aug 20, 2020 @ 13:24
    Matt Brailsford
    0

    Thanks for sharing Tim.

    Glad you were able to get it working to your needs 👍

Please Sign in or register to post replies

Write your reply to:

Draft