Why does your OData integration slow down at a few thousand records an hour?

Why does your OData integration slow down at a few thousand records an hour?

Because that is roughly where Microsoft's own published sizing examples put it. The unit in Microsoft's integration guidance is records per hour, not per minute, and behind that unit is a validation chain that runs in full on every row you send.

Before you go looking for a misconfiguration, check the pattern against the sizing Microsoft published for it.

Microsoft publishes the sizing, and it is per hour

Integration between finance and operations apps and third-party services walks six worked scenarios, each carrying a decision table with a Peak data volume line. Collected, those lines answer the question in the title.

OData (Open Data Protocol) and the batch data API are both on the page's own pattern list — long-standing [GA] platform APIs that Microsoft recommends by scenario on that page.

• Create and update product information — Pattern Microsoft recommends: OData service endpoints · Peak data volume Microsoft states: 1,000 records per hour
• Read the status of customer orders — Pattern Microsoft recommends: OData service endpoints · Peak data volume Microsoft states: 5,000 records per hour
• Approve BOMs (bill of materials — the parts list for a product) — Pattern Microsoft recommends: An OData action · Peak data volume Microsoft states: 1,000 records per hour
• Look up on-hand inventory — Pattern Microsoft recommends: A custom service · Peak data volume Microsoft states: 1,000 records per hour
• Import large volumes of sales orders — Pattern Microsoft recommends: Batch data APIs · Peak data volume Microsoft states: 200,000 records per hour
• Export large volumes of purchase orders — Pattern Microsoft recommends: Batch data APIs · Peak data volume Microsoft states: 300,000 records per hour

Every figure there is Microsoft's, from the same page. The highest volume it attaches to an OData scenario is the customer-order status read; the lowest it attaches to a batch data API scenario is forty times that. That gap is the design decision, visible on one page before anyone writes a line of code.

The caveat Microsoft attaches, and the threshold it publishes

Do not sharpen those numbers. Microsoft's own note, in full:

When providing guidance and discussing scenarios for choosing a pattern, data volume numbers are mentioned. Use these numbers only to gauge the pattern and don't consider them as hard system limits. The absolute numbers vary in real production environments because of different factors—configurations are only one aspect of this scenario.

They size an architecture. They do not certify one. The same page also publishes a threshold, stated as guidance rather than as a measurement:

Batch data APIs are designed to handle large-volume data imports and exports. It's difficult to define what exactly qualifies as a large volume. The answer depends on the entity, and on the amount of business logic that is run during import or export. However, here's a rule of thumb: If the volume is more than a few hundred thousand records, use the batch data API for integrations.

Note what the rule of thumb does not say. It does not say OData is safe up to a few hundred thousand records. It names the point past which the question is settled. Below it, the sizing table is what you have.

Why the number is what it is: every row runs the validate chain

OData is synchronous, and Microsoft states the consequence plainly: "Both OData and custom services are synchronous integration patterns, because when you call these APIs, business logic runs immediately."

Immediately is doing the work. The OData page publishes the methods "that the OData stack implicitly calls on the corresponding data entity", in order, per operation.

• Create — Methods Microsoft lists, in the order it lists them: Clear() · Initvalue() · PropertyInfo.SetValue() for all specified fields in the request · Validatefield() · Defaultrow · Validatewrite() · Write() · Steps: seven
• Update — Methods Microsoft lists, in the order it lists them: Forupdate() · Reread() · Clear() · Initvalue() · PropertyInfo.SetValue() for all specified fields in the request · Validatefield() · Defaultrow() · Validatewrite() · Write() · Steps: nine
• Delete — Methods Microsoft lists, in the order it lists them: Forupdate() · Reread() · checkRestrictedDeleteActions() · Validatedelete() · Delete() · Steps: five

An update takes an update lock and re-reads the record — Forupdate(), then Reread() — before a single field is set. Then it validates the field, defaults the row, and validates the write.

That is X++ (the Finance and Operations development language) business logic, per row, per call, and it is why Microsoft sizes these scenarios in an hour rather than a minute.

(Defaultrow appears without parentheses on Microsoft's Create row and with them on its Update row; both are reproduced as published.)

This is also why adding threads plateaus once the per-row operation stops being a quick one. You are not queueing behind network latency. You are queueing behind the ERP's own posting logic, which is the thing you wanted to run.

Four OData behaviours that make it slower, or quietly wrong

Each of these is on Microsoft's OData page, and each is cheap to fix before go-live and expensive after.

• Cross-company is off — What Microsoft's page says: "By default, OData returns only data that belongs to the user's default company. To see data from outside the user's default company, specify the ?cross-company=true query option." · What it costs you: A multi-entity manufacturer gets one company's data and a green test run
• $expand is shallow — What Microsoft's page says: The supported query options include "$expand (only first-level expansion is supported)" · What it costs you: Second-level reads become separate calls against the same per-hour scenario volume
• The client posts what you did not set (on create) — What Microsoft's page says: "When you create a new record by using an OData client… the client includes properties that you don't set in the body of the request and assigns default values to them" · What it costs you: A newly created record carries defaults in every field the client did not set
• Paging has a ceiling — What Microsoft's page says: "The OData service supports serving driven paging with a maximum page size of 10,000" · What it costs you: Page-size tuning stops helping at a published number

The fix for the third is the highest-value line here: SaveChangesOptions.PostOnlySetProperties on SaveChanges(), with before-and-after code on the same page. And to filter to one company rather than opening all of them, Microsoft's syntax is ?$filter=dataAreaId eq 'usrt'&cross-company=true — the filter alone will not do it.

Business Central publishes a read-replica intent. These four Finance and Operations pages do not.

If your write path is Business Central rather than Finance and Operations, there is a documented way to move read traffic off the primary database.

Read Scale-Out [GA] is marked "INTRODUCED IN: Business Central 2020 release wave 1", and the DataAccessIntent property [GA] that drives it is "Available or changed with runtime version 5.0".

It applies to Page, Report and Query objects, and, when calling an API page or API query, a caller overrides it per request with the HTTP request header Data-Access-Intent.

Then read the hedges, because they are the engineering story. "In the Business Central Online service, almost all OData GET requests use read-only intent for data access" — almost all.

And, on both pages: "Setting the DataAccessIntent property to ReadOnly doesn't guarantee that your data access will be running on the secondary replica." It is a hint, not a route.

"Sandbox environments can't be enabled with read scale-out", so what you measure in a sandbox is the primary database either way.

On the four Finance and Operations pages cited here — the integration overview, the OData page, the service protection API limits page and Maximize API throughput — none publishes an equivalent read-replica intent option for OData. That side publishes the validate chain instead.

What Microsoft says to do about throughput

Maximize API throughput is the page to argue from, and four of its six strategies are directly actionable. The fifth and sixth — distributing across multiple service principals, and connection tuning, for which Microsoft publishes sample code — are design changes rather than settings.

• Let the server set the pace. "Don't try to calculate the number of requests that you should send at a time. Each environment can differ." Ramp until you hit limits, then follow the Retry-After interval.
• Avoid large batches. "Most scenarios are fastest if you send single requests and use a high degree of parallelism." The ceiling is stated: "In finance and operations apps, batch size is limited to 5,000 operations."
• Remove the affinity cookie. "If you remove the cookie, you can route each request to any eligible server. Throughput increases because limits apply per server." Microsoft's Note restricts this to applications that must optimize throughput — interactive clients lose cached data.
• Use multiple threads — with Microsoft's condition attached, which is the same condition the validate chain above imposes: "If your individual operations are relatively quick, your application can use the higher limit on the number of concurrent threads to significantly improve performance."

The fifth — distributing work across multiple service principals — is written on that page around user-based limits, and the status of those limits changed. Read it next to the companion piece on which service protection limits are actually enforced.

Where we draw the line

We do not put bulk movement on the synchronous path, and we do not ask an ERP to be a feature store. Dynamics is the system of record; the optimization is the system of intelligence, and the two want different doors — which is why our write path uses both.

Demand & Inventory Optimizer reads history through the batch data path and writes the decision — the reorder point, the safety-stock level — back through OData, because a decision is a handful of rows and belongs where validation runs and errors return in the response. Pricing & Discount Optimizer does the same with the price on the line.

What we refuse to build: a nightly full-catalogue refresh over OData, or a model-training extract that walks the entity endpoint page by page. Those are batch data API jobs. We do not touch your ERP core — we surround it with intelligence.

What to do this week

1. Open the six scenarios on Microsoft's integration overview and write your own peak data volume beside the closest one. If yours is an order of magnitude higher, the pattern is the finding, not the code.
2. Grep the integration for cross-company=true. If it is absent and you run more than one legal entity, you have a correctness problem before a throughput problem.
3. Search the client for PostOnlySetProperties. If it is absent on any path that creates master-data records, fix that before anything else here.
4. Count the $expand calls that need a second level. Each is already a separate request; decide whether it belongs on a batch data API export instead.

About Cognilium Cognilium is the AI optimization layer for Dynamics 365 — complementary apps that optimize the pricing, inventory, warehouse and planning decisions your ERP manages but can't optimize. Built on Power Platform, Dataverse and Azure. https://cognilium.ai · https://www.linkedin.com/company/37180269/

Take your peak hourly row count to the sizing table above and see which side of it you land on. If the answer surprises you, book a fifteen-minute call and we will walk your write path with you, live — no deck. https://cognilium.ai

Sources

• Integration between finance and operations apps and third-party services
• Open Data Protocol (OData)
• Maximize API throughput
• Service protection API limits
• Using Read Scale-Out for Better Performance
• DataAccessIntent property

Sources

• learn.microsoft.com — integration overview
• learn.microsoft.com — odata
• learn.microsoft.com — service protection maximizing api throughput
• learn.microsoft.com — service protection api limits
• learn.microsoft.com — database read scale out overview
• learn.microsoft.com — devenv dataaccessintent property