Tip #68 | Using Read-Scale Out in Business Central to improve Performance

Another quick tip for something I’ve used this week to help out a QBS partner with performance issues on Business Central.

Since the last release it’s possible to issue read-only commands on a real-time copy of your Business Central database by using the DataAccessIntent property.

This allows API Pages, reports and queries to be executed outside of your production database which is ideal for Power Apps, Power BI and websites that for example only show status information on outstanding orders.

Then I remembered, “off course” we can also use that with the ForNAV report pack for financial reports that run longer, like the Inventory to G/L Reconciliation. (Which already runs 10 times faster than the out of the box version).

So here is a screenshot:

DataAccessIntent = ReadOnly

And the documentation

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/performance/performance-developer#using-read-scale-out

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/properties/devenv-dataaccessintent-property

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/database-read-scale-out-overview

IMPORTANT NOTE!!

You cannot use this for document reports! Document Reports in Business Central write to the database for logging purposes and VAT/Sales Tax calculation.

Document Reports are not good candidates for this feature since they are more or less WYSIWYG. They print litteraly what is in their source tables.

Also they leverage the Service Tier caching so they probably don’t even take a roundtrip to the database for most of the data they print.

6 thoughts on “Tip #68 | Using Read-Scale Out in Business Central to improve Performance

  1. Carl

    This is really great! I have a customer that i could apply this to right away that is experiencing locking issues.

    I do however have 1 primary question after reading on the following link:
    https://docs.microsoft.com/en-us/dynamics365/business-central/devitpro/developer/properties/devenv-dataaccessintent-property

    It says “When a workload is executed against the replica, insert/delete/modify operations aren’t possible. If any of these operations are executed against the replica, an exception is thrown at runtime.”

    Does this mean if someone is reading sales orders from the replica for a long time my customers user will get an error messages if they try to create a new sales order? Sounds a bit strange if that is the case but that is how i interpret it.

    Thank you for your clarification regarding this if possible.

    Liked by 1 person

    Reply
    1. Mark Brummel Post author

      I have to test that and I can do that later this week.

      Please note that Business Central is normally using READUNCOMMITTED isolation level on Azure SQL for reading so this will not solve any locking unless the locking happens because the tenant is slow because of the heavy reads. Did you setup Application Insights to look at this issue? Don’t hesitate to reach out so I can help you set this up in a few moments.

      Like

      Reply
      1. Carl Ohlsson

        Thank you!

        A little clarification, lock might not have been the correct word to use. I have hooked up application insights to the SaaS tenant and it works perfectly. The users are getting a message something like “Please wait working on it” so its not the old “Record is locked by another user” issue. So my thinking was like you mentioned its slow because of some reason. I searched the logs in application insights and have been able to match the times described by users with incoming read’s to a custom API page that is only used for exporting price data.

        Looking forward to seeing what you can find out regarding this.

        Liked by 1 person

  2. Pingback: Tip #68 | Using Read-Scale Out in Business Central to improve Performance - 365 Community

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.