IMD SolutionComputing LibraryShop main store

Explainer

Historian data retrieval speed: what actually decides it

Edge computing and IIoT Published 9 min read

Short answer

An indexed historian returns a year-old sample about as fast as yesterday's, so data age is the wrong variable to optimize. Retrieval speed is set by the requested window, returned point count, retrieval mode, archive storage tier and client rendering. Benchmark candidates with identical query semantics before choosing one.

On this page
  1. How fast should a historian return a value recorded a year ago?
  2. How much data does one tag at one second produce in a year?
  3. What is the difference between recorded, interpolated and summary retrieval?
  4. Why is a full-year trend pull slow when a ten-minute window is fast?
  5. Why can yesterday's data be quick and last year's still be slow?
  6. Why does my historical trend come back empty while the live value is fine?
  7. How do I benchmark two historians so the comparison is fair?
  8. How much does the historian host hardware move retrieval speed?
  9. Questions asked

Retrieval speed on an indexed historian is not a race between brands; it is arithmetic you control. A value recorded a year ago is addressed through the same index as yesterday's, so age itself costs almost nothing. What costs is the window you ask for, the number of points the query returns, the retrieval mode you select, the storage tier the archive sits on and how many points the client has to draw. One tag at one second produces about 31.5 million samples a year; a ten-minute event is a few hundred of them. Query the event window and any competent historian feels instant; pull the whole year at one-second resolution and transfer and rendering dominate. The sections below give the number behind each of those terms and the decision that follows from it.

How fast should a historian return a value recorded a year ago? #

On an indexed archive, a one-second sample from twelve months back comes back in about the same time as one from yesterday, because the index addresses the tag's time range directly instead of reading the year that came before it; age is not the variable that sets retrieval speed.

A historian indexes each tag's samples as it stores them: the collector writes tag identity, timestamp, value and quality, and the archive keeps an index over tag and time. A query carries a tag and a start and end time, so the engine seeks straight to the range and never reads the months in between. Everything that costs time — reading records, moving bytes, drawing pixels — happens after the seek and scales with the number of points returned, not with how long ago they were written.

The consequence for anyone shopping on speed is that how fast a historian trends a year-old tag does not separate products. On ordinary server hardware, a short window on a handful of tags completes faster than an operator can notice on any competent historian. If the year-old trend is the slow one while yesterday's is quick, the cause is almost always where the old archive physically sits, which is the storage tier question below, not age handling.

How much data does one tag at one second produce in a year? #

One tag recorded continuously at one second produces 31,536,000 samples in a 365-day year, and that number, not the age of the data, is what every query, transfer and drawing cost downstream multiplies by.

The arithmetic: 365 days × 24 hours × 60 minutes × 60 seconds = 31,536,000 samples for one tag across a 365-day year, assuming recording that never stops. Ten tags give 315,360,000; a hundred tags give 3,153,600,000. Every point count and transfer figure in this article comes from that one number.

Compression changes the stored count, not the recorded rate. With a deadband on the tag, the collector stores a new sample only when the value moves further than the deadband from the last stored value, so flat stretches cost almost nothing and the stored total falls well below 31.5 million. How far it falls depends on how much your signal actually moves; the setting lives in the tag's recording configuration in the collector, and it is the first place to look when a year of history is smaller on disk than the arithmetic predicts.

What is the difference between recorded, interpolated and summary retrieval? #

Recorded retrieval returns the stored samples with their original timestamps, interpolated retrieval returns calculated values at whatever interval you ask for, and summary retrieval returns per-interval statistics, so the mode decides the returned point count before the archive does any work.

Retrieval mode What comes back Points for the year-long, one-second tag
Recorded Stored samples with their original timestamps Up to 31,536,000
Interpolated Values calculated at the requested output interval 31,536,000 at a 1 s interval
Summary Per-interval statistics such as average, minimum and maximum 8,760 at a 1 h interval

Use recorded retrieval when you need to know what the archive actually captured; it is the only mode that proves what was stored. Use interpolated retrieval when two signals must line up on evenly spaced timestamps, and treat the output as calculated, because interpolation fills the gaps between stored samples whether anything was measured there or not. Use summary retrieval for overviews, and take the interval minimum and maximum alongside the average whenever a short excursion matters, because an average alone hides it.

Verify that the mode took effect instead of trusting the request. Query a known flat stretch of a slow signal: interpolated output should draw a flat line between stored points, and recorded output shows the collector's stored steps. A query set to interpolated output that still comes back as raw steps means the interval setting never reached the query, and any comparison built on it is comparing two different answers.

Why is a full-year trend pull slow when a ten-minute window is fast? #

The index finds the start of any window in about the same time, but the work after the seek scales with returned points: a ten-minute window is 600 samples, while a full year at one-second resolution is 31,536,000 points to read, move and draw.

The full set of counts: 10 minutes × 60 seconds = 600 samples for the event window, 24 × 60 × 60 = 86,400 for a day, and 365 × 24 × 60 × 60 = 31,536,000 for the year. The index reaches each of those windows in about the same time; what changes by five orders of magnitude is what must be read, transferred and rendered afterwards.

Transfer is easy to estimate. Assume 30 bytes per serialized point on the wire — an illustrative figure that varies with protocol and compression, not a specification. 31,536,000 × 30 B = 946,080,000 B, about 0.95 GB for one tag. On a 100 Mbit/s link running flat out, 100 ÷ 8 = 12.5 MB/s, so 946,080,000 B ÷ 12.5 MB/s ≈ 76 s of pure transfer before server read time and client drawing add anything. Rendering is what the operator feels: no trend control draws millions of points usefully, so the client decimates or stalls while the screen stays busy.

Decision: trend event windows at native resolution, open months and years as summaries, and reserve full-resolution year-long pulls for engineering exports that run as batch jobs instead of as a click in a shift meeting.

Why can yesterday's data be quick and last year's still be slow? #

When older records sit on a cheaper storage tier, the first query pays an access penalty the hot tier never charges: Microsoft's Azure Storage documentation prices its cold tier with lower storage cost and higher access cost and expects data to dwell there at least 90 days.

Tiers are a cost trade, not a defect. The coldest archival tiers go further and can require a rehydration step before anything can be read at all, which takes hours, not milliseconds. A plant file server reproduces the same effect in miniature when closed archive files have been moved to slower disk or nearline storage: the first trend into last year's closed archives pays the penalty, and the second does not.

Caching adds a second asymmetry: the first request pays for loading archive segments into memory, and the repeat finds them already there. Time the first request of the morning separately from the tenth before quoting any figure.

Decision: keep the windows operators actually open on the active tier, push long-retention data onto the cheap tier and accept the one-off delay. A cold-tier first read is the storage design working as priced, not the historian being slow.

Why does my historical trend come back empty while the live value is fine? #

An empty or short historical trace is almost always a query-side problem — a time-zone offset, a missing or closed archive, a wrong tag mapping, or a stretch when collection was stopped — not a speed problem, and the live path never touches any of it.

The two paths share almost nothing. Live runs display binding, driver, network, controller. Historical runs display binding, historical query, archive, storage. A stale live trace therefore points at collection, while an empty or short historical trace points at the request itself.

Run the same query through the historian's own retrieval interface, outside the trend screen, and time it. If the data comes back there, the archive and index are fine and the client's request or rendering is the suspect. Confirm both traces reference the same tag identity, and confirm the request range in the archive's own time base, before anything else. When the query returns nothing at all, work through the historian trend empty PLC tag collection troubleshooting sequence before touching the trend screen configuration.

Decision: fix the time base and the mapping first; only once the query returns correct data quickly outside the client does rendering become worth optimizing.

How do I benchmark two historians so the comparison is fair? #

Run the identical dataset through both systems with identical query semantics — the same window, the same output interval, the same retrieval mode, the same tag count — and time query completion, transfer and finished drawing separately, first request and repeat, with cache conditions recorded.

  1. Freeze the question: time window, tag list, output interval, retrieval mode and how quality values are handled, written down as the test script.
  2. Load the identical dataset into both candidates: same tags, same timestamps, same compression settings.
  3. Time query completion at each product's retrieval interface, outside any screen, first request and repeat.
  4. Time transfer and finished drawing separately, so a fast archive hiding behind a slow client shows up.
  5. Repeat under representative collection and user load; an idle-server result does not describe a Tuesday afternoon.

Identical query semantics is where vendor comparisons usually break: one product returning recorded values against another returning interpolated ones is not a speed difference, it is two different answers. Run the heavy test queries off-shift or against a copy; a full-year pull against the production archive while operators depend on it is self-inflicted downtime. The candidate that wins on your window, your tags and your load is your historian, and the plant historian selection comparison by scale and support needs covers the criteria beyond speed.

How much does the historian host hardware move retrieval speed? #

The host decides the parts the query design cannot: active-tier disk latency sets the read cost per archive segment, RAM cache absorbs repeat reads, CPU does the interpolation, and the network link to the client carries every returned point.

The parts are separable inside one machine. Disk latency on the active tier sets the per-segment read cost, RAM holds the hot window in cache, CPU does interpolation and summary math, and the network link carries every returned point once. A modest current box collects hundreds of tags at one second and serves trend queries at the same time; the honest sizing inputs are retention window, sample rate and tag count, not a processor brand. The sizing worked example for a self-hosted Node-RED, InfluxDB and Grafana historian turns those three inputs into concrete disk and memory figures.

When the historian host is being specified or replaced, a fanless industrial mini PC is the usual cabinet fit for a data-collection and small-historian host.

Decision: spend on active-tier disk and enough RAM to cache the hot window; past that, query design moves retrieval speed more than the processor does.

Questions asked

Can I use SQL Server as a historian instead of a dedicated product?

It works, and some plants run it that way, but you take on what a historian bundles: collection, tag identity, timestamp and quality storage, compression, archive rotation and the retrieval queries themselves. Retrieval speed then depends on your schema, indexes and query plans rather than on a product's archive engine. It suits teams that already operate the database and can maintain the acquisition layer they build on it.

How do I check how many points a tag actually stored last year?

Ask the archive rather than the configuration. Most historians report a per-tag sample or stored-point statistic, and a counted recorded query over the year gives the same answer. Compare it with the theoretical 31,536,000 samples for one-second recording across a 365-day year: a large shortfall means compression, a stopped collector or a tag added mid-year, while a close match means the archive holds everything the collector wrote.

How long should full-resolution one-second data stay on the active tier?

Keep it for the longest window anyone actually opens. If incident reviews never reach past 90 days, 90 days of second-level data is enough; beyond that, store hourly or shift summaries, which keep long-term context at a fraction of the point count. Keep raw samples older than that only where a regulation, contract or customer requirement asks for them.

Is a cloud-hosted historian slower to query than one in the plant?

Only in what the cloud adds. Every query crosses the WAN, which typically adds tens of milliseconds of round-trip latency, and archive segments in a low-cost tier pay that tier's higher access cost before any data moves. Inside the same region, the same variables decide the result as on-site: window length, returned point count, retrieval mode and client rendering.

Keep reading

  • Troubleshooting

    Historian tag scaling for an unscaled PLC integer value

    Scale the tenths-encoded integer on the historian tag itself: linear scaling with gain 0.1 and offset 0 turns raw 144 into 14.4 while keeping the direct PLC acquisition. Check the tag's existing scaling first so a second divide-by-ten does not log 1.44, and repoint the tag at a SCADA-supplied value only if logging that stops with that runtime is acceptable.

    Edge computing and IIoT

  • Troubleshooting

    Historian trend empty: PLC tag collection troubleshooting

    A live PLC value proves nothing about what the historian stored. Work one known, changing tag through four boundaries — controller to data server, server to collector, collector to point, point to chart — and with a 2 s update rate, a 1% deadband on a 200 in span and a 24 in/h fill, a 20 min window must show about 4 stored events. Zero events means the collector; four events with a flat chart means the display.

    Edge computing and IIoT

  • Explainer

    Do you need a historian or just an OPC tag server?

    No. An OPC tag server (OPC DA) supplies the current value, quality and timestamp of each item and stores nothing, so monthly figures need a historian or a SQL logger collecting from that same server. Keep a reporting client like ActiveFactory only if you also run the historian it expects.

    Edge computing and IIoT

OLAP versus plain historian storage in SCADA databases