Copied to clipboard

Flag this post as spam?

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


  • Phil Dye 149 posts 325 karma points
    Aug 09, 2017 @ 08:42
    Phil Dye
    0

    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

  • Phil Dye 149 posts 325 karma points
    Aug 09, 2017 @ 08:59
    Phil Dye
    0

    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?

  • Phil Dye 149 posts 325 karma points
    Aug 09, 2017 @ 09:06
    Phil Dye
    0

    With >2.6million rows in that table (mostly old carts), should I be running anything to purge them periodically?

  • Phil Dye 149 posts 325 karma points
    Aug 15, 2017 @ 08:03
    Phil Dye
    0

    Any 'official' response to this?

  • Anders Burla 2560 posts 8256 karma points
    Sep 07, 2017 @ 08:27
    Anders Burla
    0

    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

  • Phil Dye 149 posts 325 karma points
    Sep 07, 2017 @ 09:49
    Phil Dye
    0

    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.

Please Sign in or register to post replies

Write your reply to:

Draft