Troubleshooting
Historian reads slow in Excel after adding tags
Short answer
Run one timed test before touching anything: read one old tag and one newly added tag over the same range in the historian's built-in client, then in the Excel macro. Fast in the client but slow in Excel means the macro's request pattern is the fault; the new tag slow in both means you run the historian's validate action and then confirm the SQL maintenance plan actually targets the historian database.
On this page
- Is the slow read in Excel or in the historian?
- What do the logs say while the slow read runs?
- What does the validate action fix after a tag change?
- Did the SQL maintenance plan actually target the historian database?
- How do I cut the macro's read time in Excel?
- How do I confirm the fix and when do I open a support case?
- Questions asked
A historian that answered Excel promptly before the tag change and crawls now has the fault in one of four layers: the Excel macro, the tag configuration, the historian data service, or the SQL database. One timed test, about ten minutes, tells you which — and it comes before any tuning and before trusting the maintenance schedule you already ran. Read one old tag and one newly added tag over the same range in the historian's built-in client, then in the Excel macro. Fast in the client and slow in Excel means the macro's request pattern is the fault. The new tag slow or empty in both means you run the historian's validate action, then confirm the maintenance plan actually targets the historian database.
Is the slow read in Excel or in the historian? #
Time one old tag and one newly added tag over the same range — a 24-hour window works — in the historian's built-in client and then in the Excel macro, and let the old tag's client time be the baseline, because it was fast before the change.
- Pick two tags: one that existed before the change and one of the newly added ones.
- Read the same range for both in the built-in client (the Web Client or the viewer your historian ships) and write down each time.
- Run the Excel macro for only those two tags over the same range and write down that time.
- Run the macro for the full sheet and note whether the time grows with tag count.
| Built-in client, same range | Excel macro | Where the fault is |
|---|---|---|
| Both tags fast | Slow | The macro's request pattern |
| New tag slow or empty | Slow | Configuration first, then the SQL checks |
Use the old tag's client time as the baseline, because it was fast before the change. Working rule: both tags back within a factor of two of each other, in single-digit seconds over a 24-hour range, clears the back end — go straight to the macro section. The new tag at more than twice the old tag's time, or returning nothing, is a configuration fault: go to the validate section, and if the new tag returns nothing at all, start with historian trend empty: PLC tag collection troubleshooting, because a tag that never collects cannot be fast. If the built-in client is slow for both tags, the constraint is the back end itself, and historian data retrieval speed: what actually decides it covers what sets that time.
Illustrative numbers, not a benchmark of your system. Built-in client, 24-hour range: old tag 1.6 s, new tag 1.9 s. The ratio is 1.9 ÷ 1.6 = 1.19, inside the factor-of-two rule, so the historian, data service and SQL layers are cleared. The Excel macro over the same two tags: 6.4 s. The full sheet of 80 tags, one request per tag: 62 s, which is 62 ÷ 80 = 0.775 s per request. Decision: configuration and database are healthy; the macro's request pattern is the fault.
What do the logs say while the slow read runs? #
Read the Windows Event Viewer and the historian data service log for the exact minutes of one slow macro run: repeating messages that name the tags you added, deleted or renamed mean a configuration fault, and SQL timeouts or login failures mean a database fault.
- Start the macro, note the clock time, and let it finish.
- On the historian server, open Event Viewer and read the Application and System logs for those minutes.
- Open the historian data service log for the same window and look for messages that repeat and name the tags you added, deleted or renamed.
- Note any SQL Server connection, timeout or login-failure messages.
Repeats naming your tags point to the validate section. SQL timeouts point to the maintenance section. A clean log behind a slow read points back to the request pattern: with no errors logged, the delay is load, not faults.
What does the validate action fix after a tag change? #
The validate action resolves the conflicts that added, deleted or renamed tags leave in the configuration, it is the step most often missed after a tag change, and it runs only after you have backed up the historian configuration and database.
Adding or removing tags without validating leaves configuration entries that no longer match the live tag set, and every read pays to resolve them. Where the action lives differs by product — AVEVA Historian manages tag definitions in its Configuration Editor, and Canary's historian tile carries a Validation field that names the DataSet whose files still need validating — so open your historian's help and follow the path it documents rather than a remembered menu.
- Back up the historian configuration and the database. This is the one step not to skip.
- Run the validate action as documented and let it complete.
- Restart the data service only if the documentation requires it, and only in a window where a logging gap is acceptable: the pause hits collection on every tag, not just the new ones.
- Re-read the data service log. The repeats naming your tags must be gone.
- Re-time the two test tags in the built-in client.
Gate: the new tag returns in about the old tag's time.
Did the SQL maintenance plan actually target the historian database? #
Demand two things from the maintenance plan: a job history success dated after the tag change, and plan steps that name the historian database and include index maintenance with an Update Statistics task — a backup-and-integrity-only plan reports success while reads stay slow.
A maintenance schedule that ran is not the same as one that worked on the historian database, and a job can report success while touching nothing that read speed depends on.
- In SQL Server Management Studio, open the maintenance plan's job history and compare the last success date with the date of the tag change.
- Open the plan's tasks: the historian database must be selected by name, with a Rebuild Index or Reorganize Index task and an Update Statistics task included, not only Back Up Database and Check Integrity.
- Check free space on the data and log volumes and the files' autogrowth settings.
- Run the plan manually once the validate action has completed, then repeat the timed read from the first section.
Gate: a success dated after the tag change for the historian database, and a timed read no slower than the baseline.
How do I cut the macro's read time in Excel? #
Batch the tags into fewer requests, shorten the requested range to what the sheet displays, and set calculation to manual with screen updating off around the read loop — three changes that took the illustrative 62-second, 80-tag sheet to 3.6 seconds.
- Count the tags the macro requests per run. One request per tag multiplies fixed overhead by the tag count; batch the whole list into one request where the add-in supports it.
- Shorten the requested range to what the sheet actually displays. A week of one-minute data behind a shift report is wasted read time.
- Formulas that recalculate as each value arrives turn one read into many recalculations; calculation set to manual for the read loop stops that.
- Restore both settings afterward, including on the error path — a sheet left in manual calculation confuses the next user.
Dim requestCount As Long
requestCount = 1 ' illustrative: one batched request for the whole tag list
Dim runStart As Double
runStart = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' read loop: a single call for all tags
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Debug.Print Timer - runStart ' the test: seconds for the batched read
Application.ScreenUpdating, Application.Calculation and the xlCalculation constants are the documented Excel object-model names; requestCount and runStart are illustrative variables, and the printed elapsed seconds is the test that proves the change.
Gate: the macro's time lands near the built-in client's time and grows in proportion to tag count instead of jumping.
How do I confirm the fix and when do I open a support case? #
Run the full macro twice over the normal range and check its values against the built-in client for the same timestamps: matching values plus a repeatable time near the baseline close the fault, and a fast first run with a slow second points at SQL job timing or memory pressure.
- Run the full macro over the normal range and record the total time.
- Read the same tags in the built-in client and confirm the values match for the same timestamps — speed means nothing if the numbers differ.
- Check Event Viewer and the data service log for new entries from that run.
- Run it a second time and compare.
- If the validate action ran, the job history shows the maintenance success, and the macro is still slow against a fast client, open a support case with your timed results and the log export.
Decision by case: fast client and slow macro — batch the requests and trim the range. New tag slow in both — validate the configuration, then verify the maintenance plan names the historian database. Slow across every client — the retrieval path itself is the constraint, and that is a capacity question, not a tag-change procedure.
Questions asked
Does adding tags require restarting the historian data service?
Only when your historian's documentation says so for that change. If it does, pick a window where a short logging gap is acceptable, because a restart pauses collection on every tag, not just the ones you added. After the service comes back, clear the old log entries, run the timed read, then read the log again before judging the speed.
Why do stale statistics slow reads only after tags are added?
New tags add rows and change how selective each query is. Statistics are the query planner's summary of the table's data distribution; if they still describe the old table, the planner keeps join orders and index choices that suited fewer tags, so each read touches more rows than it needs. An Update Statistics task after the tag change rebuilds that summary.
Should the built-in client test run on the Excel PC or on the server?
On the Excel PC, with the same user account the macro uses. Both query paths then cross the same network and the same permissions, so a fast client read beside a slow macro rules out network latency and server load in one step. If you can only open the client on the server, say so in your notes, because that comparison is weaker.
What do I record before opening a support case?
Four times over one range: the old tag in the built-in client, the new tag in the built-in client, the macro for those two tags, and the macro for the full sheet with its tag count. Add the date of the tag change, the maintenance job's last success date, and an export of the data service log covering one slow run. That hands support the layer split without a second visit.