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.