Hi all,
Need help to understand if I can use multiple SQL Query tags in a gateway and whether it will affect the system performance .
My configuration is under the UDT I have created 1 SQL query tag with data type dataset. And it will return a single row with 10 columns. Again under UDT 10 more expression tags which are point/getting the value from the dataset.
This query tag will be executed every 5 seconds and there are 200 Instance for this UDT, so 200 query firing every 5 seconds(single query takes fraction of seconds to retrieve data).
Is this approach correct or will this hamper the performance of the gateway?
Thank you.
So, forty queries per second. Not terrible, but no, not well-optimized. Consider pulling all 200 rows into a single dataset with a single query. Then use an expression to pull the correct row into the UDT’s dataset. (I would use my view() expression function, fwiw.) Leave the other expressions as-is.
1 Like
I really need to convince my place to use your simulation aids. I’m having the same exact issue. Being able to reduce query load to a single query instead of 1 per UDT and just view()ing the right rows per UDT would really help the gateway load.
You might be able to use the lookup expression as well if the query is nicely formed. We do something similar for a status bit for 1000s of UDTs reading from a database. A single tag reads the database and creates a dataset, and then a bit in the UDT uses lookup to get the correct data from that dataset.
2 Likes
I previously used the same approach but that particular query takes minimum 13-15secs to execute which ultimately gives a warning in log.
Hence trying individual query for each instance.
Will there be any other way to this?
That suggests pathological complications in your DB (poor choice of indexes, etc). Please share your queries if you can.
1 Like
Lookup can only grab one column value. My view() function can extract multiple rows and/or columns.