Dashboard with SQL queries

Ignition
1

I will develope a dashboard in IGNITION, it will be something like the image below, many boxes , each comes from an ignition TAG which get its value of a query to SQL or from PLC addresses, in some cases the sql query get just one value because the tables design and in other cases the sql query can get several columns , so the questions are:

  1. Is there a performance problem to do many sql queries (~3000 each 1-5 seconds) to populate values of ignition tags?
  2. For the case of a query that get several columns how can I split the returned values to different tags? or should I always do a query by tag?

thanks

image

2

  1. Yes, don’t do that. Fewer queries with larger results will perform best.

  2. Elements of query results can be extracted with expressions into their own tags. If very many, and there is a regular pattern, it can be more efficient to use a script.

3

thanks. but if we have the data in different tables/databases we can’t do fewer queries, can it be an issue for Ignition or when will become an issue? or how can I estimate/detect this as an issue?

4

There is a slow query log, which one indicator of when things are starting to go awry.

If you’re running 200 queries a minute but they’re all executing in under 50 ms or shorter, you’re in better shape than if you’re running 10 queries a minute but they all take 10 seconds each.

Proper indexing of table columns is your way to ensure that the query times remain reasonable. Index the columns or group of columns that are used in your where clauses.

5

Surely you aren’t going to display all items in the dashboard at once, so only grab the data when you need it, perhaps a lease scan class?

6

thanks

7

I have to display around 500-600 data at the same time in the dashboard, from different sources/queries. under a c# dashboard that I have it works fine, I will do a test with ignition, maybe slower scans if I have issues, and create views internally in SQL and then to queries to the view.

thank you all for your feedback.