Best Database Architecture for Large-Scale OEE System (400+ Machines) – Named Queries vs Stored Procedures

Ignition
, ,
1

Hi Team,

We are developing a large-scale Industry 4.0 OEE application in Ignition and would like suggestions regarding the best database interaction architecture for long-term scalability and performance.

Our current environment:

  • Ignition 8.1.42
  • MSSQL database
  • AWS infrastructure
  • 400+ CNC machines connected through Ewon gateways
  • Unlimited clients
  • Heavy real-time tag event scripts
  • Continuous OEE, downtime, and quality logging
  • Perspective dashboards with aggregation queries

Current workflow:

  • Machine tag changes trigger Ignition scripts
  • Scripts perform calculations and database inserts/updates
  • OEE data is stored in large historical tables
  • Dashboards query aggregated OEE metrics (Availability, Performance, Quality, OEE)

We are evaluating whether to:

  1. Continue using Named Queries extensively
  2. Move most database operations into SQL Stored Procedures
  3. Use a hybrid approach (Stored Procedures for heavy operations and Named Queries for simple UI queries)

We would appreciate guidance from the community regarding best practices for a system of this scale.

Specific questions:

  1. For high-frequency insert/update operations, is using SQL Stored Procedures more efficient than Ignition Named Queries?

  2. Are there any known performance bottlenecks on the Ignition Gateway side when many Named Queries are executed continuously from tag events?

  3. In large OEE systems, is it recommended to move:

    • calculations,
    • aggregation logic,
    • downtime classification,
    • KPI generation
      into SQL Server procedures instead of Ignition Project Library?

  4. What architecture is generally recommended for:

    • 400+ machines,
    • continuous inserts,
    • large historical datasets,
    • real-time dashboards?

  5. Are there any limitations or concerns with using Stored Procedures extensively in Ignition?

  6. For dashboard performance, is it better to:

    • query raw OEE tables directly,
    • or maintain hourly/shift/day summary tables?

  7. Are there any recommended practices for:

    • partitioning large OEE tables,
    • indexing strategies,
    • handling concurrent inserts,
    • avoiding gateway/database bottlenecks?

  8. Has anyone implemented similar-scale manufacturing/OEE systems in Ignition, and what architecture worked best long term?

We are mainly trying to ensure:

  • long-term scalability,
  • stable gateway performance,
  • optimized database performance,
  • and maintainable architecture as data volume grows.

Any suggestions or real-world experiences would be greatly appreciated.

Thank you.

2

You're not getting much of a response here because what your asking for is essentially what consultants charge money to consult on.

A couple general tips

  1. Queue up data in ignition and send to SQL in batches

  2. Use your hybrid approach for named queries

I will say this, just having the knowledge to ask these questions probably means you have the capability to test multiple solutions and determine for yourself which is the best way to go for your application.

Best of luck.

3

Unless all of your queries are run through the SF dB query functions which run instantly but pass query execution off to the store and forward system...
Yes, and there is a chance you are dropping these events as well. Tag events are not the place to be executing long-running code (think milliseconds, some say <10ms, but it really depends on your project size and activity level). You get up to 5 events that can be queued per tag, and there are 3 threads that process these queues for all tags. It's not hard for long running scripts to start stalling this and start dropping new events.

You should look into Phil's Integration Toolkit module, specifically the bulk action tag actor which is where this would be executed from instead. Either that, or run this from a gateway tag change script instead