Does TC correctly support retries in it's data layer, when using Azure SQL? (Umbraco core does, via the modified PetaPoco)
We're seeing a number of SQL timeout exceptions (all logged in Application Insights); general performance isn't a problem (the db is in an elastic pool, so has plenty of DTUs available).
I'm also seeing in the Azure SQL Performance recommendations blade a 'high impact' suggestion to create an index in TeaCommerce_Order for [OrderStatusId], [StoreId], [DateFinalized] - which seems relevant to the backoffice timeouts we're seeing.
Looking at the database, there are no indexes (other thaan PKs) - is there a definitive schema I can compare against?
Tea Commerce use PetaPoco to support MSSQL, SQLCE, MySQL, Azure etc - just like Umbraco - but it use its own PetaPoco class and not the one from Umbraco. That is because the core of Tea Commerce needs to work without Umbraco. In Tea Commerce we only have PK indexes out of the box. You can easily create new indexes if needed for optimazation.
We don't have retries implemented in Tea Commerce. Do you know which lines of code in Umbraco PetaPoco that does the retry. We could just copy it :)
I would purge old orders/carts if I had so many :) Also look into if a new cart is created for every visit to the website. Would make sense to use autoCreate = false for e.g. the mini cart.
Thanks Anders; I've since found a few places that weren't using autoCreate=false, which will help stop the problem growing, and have enabled indexes as suggested by the Azure tuning recommendations.
Azure SQL Timeouts
Does TC correctly support retries in it's data layer, when using Azure SQL? (Umbraco core does, via the modified PetaPoco)
We're seeing a number of SQL timeout exceptions (all logged in Application Insights); general performance isn't a problem (the db is in an elastic pool, so has plenty of DTUs available).
It seems, from looking at the code, that it's using a bare SqlClient, with no retry strategy as per https://docs.microsoft.com/en-us/azure/architecture/best-practices/retry-service-specific#sql-database-using-adonet-retry-guidelines
I'm also seeing in the Azure SQL Performance recommendations blade a 'high impact' suggestion to create an index in
TeaCommerce_Orderfor[OrderStatusId], [StoreId], [DateFinalized]- which seems relevant to the backoffice timeouts we're seeing.Looking at the database, there are no indexes (other thaan PKs) - is there a definitive schema I can compare against?
With >2.6million rows in that table (mostly old carts), should I be running anything to purge them periodically?
Any 'official' response to this?
Hi Phil
Tea Commerce use PetaPoco to support MSSQL, SQLCE, MySQL, Azure etc - just like Umbraco - but it use its own PetaPoco class and not the one from Umbraco. That is because the core of Tea Commerce needs to work without Umbraco. In Tea Commerce we only have PK indexes out of the box. You can easily create new indexes if needed for optimazation.
We don't have retries implemented in Tea Commerce. Do you know which lines of code in Umbraco PetaPoco that does the retry. We could just copy it :)
I would purge old orders/carts if I had so many :) Also look into if a new cart is created for every visit to the website. Would make sense to use autoCreate = false for e.g. the mini cart.
https://docs.teacommerce.net/v3.0.0/reference#getcurrentorder
Kind regards
Anders
Thanks Anders; I've since found a few places that weren't using
autoCreate=false, which will help stop the problem growing, and have enabled indexes as suggested by the Azure tuning recommendations.Umbraco core uses a modified PetaPoco to implement connection and command retries, via some extension methods, eg https://github.com/umbraco/Umbraco-CMS/blob/5397f2c53acbdeb0805e1fe39fda938f571d295a/src/Umbraco.Core/Persistence/PetaPocoCommandExtensions.cs - I'm not sure this is copyable as-is, but there certainly should be some retry logic to work properly in SQL Azure.
is working on a reply...