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.
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.
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!
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);
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:
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.
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
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!
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.
Thanks for sharing Tim.
Glad you were able to get it working to your needs 👍
is working on a reply...