Floemdata
Data & ETL

Extracting Data Without Slowing Down the ERP

How to extract from a running ERP without slowing it: off-hours first load, reading in batches, pauses, timeouts and a read-only user. Floemdata's documented rules.

Floemdata · · 5 min read
A wide pipe full of dots narrows at a bow-tie valve with an amber handle into a thin pipe with just two dots

At nine in the morning the sales team is issuing invoices, and a data extraction started for the warehouse slows the ERP down. Screens freeze, users call, and the data project is shut down in its first week. The most important rule of extraction is not to disturb the work of a running system. The methods below are general practice; the numbers are for illustration only.

Five habits that reduce load

  • Do the first big load outside business hours. The first load that pulls the whole history is done at night or on a weekend; later incremental pulls can also run during business hours.
  • Read in batches. Instead of asking for a million rows in one query, pull fixed-size batches (for example 5,000 rows); each batch is short and the system gets a breather in between.
  • Wait between batches. A short pause leaves room for the ERP to answer its users.
  • Put a time limit on queries. Cancel a query that exceeds it and retry with a smaller batch size.
  • Pull only what changed. Store the last successful point and ask only for what came after it next time; make loading repeatable so that pulling again does no harm.

Do not put heavy work on the source

  • Do expensive work such as totalling, grouping and joining in your own warehouse, not in the source system. Take raw rows from the source as they are.
  • Limit the fields and tables you read to what you really need.
  • Do not leave load to assumption: measure the ERP's response time while the extraction runs.

Permission is a separate matter

Read permission and load management are not the same thing. A read-only user cannot write to the system but can still run a heavy query and slow it down. Think about the two separately.

Where Floemdata stands

Floemdata's SAP Business One extraction document sets out these rules: the agent connects with a read-only database user; extraction queries contain no totalling or grouping, the calculation happens in the warehouse; the default batch size is 5,000 rows and should be adjustable per customer; the first full load happens outside business hours, and incremental pulls leave a short wait between batches; the query timeout is 60 seconds, and a query that exceeds it is retried with the batch size halved; the last successful point of each extraction is stored locally. These are documented design rules. We cannot say that we measured the effect on the ERP's response time in a real customer SAP environment; no such measurement exists in the documents.

To start, pick the largest table in your ERP, decide on an off-hours slot and a batch size, and watch the ERP's response time alongside during the first trial. Questions are welcome at info@floemdata.com or via Floemdata.

← All guides