I have a series of power tables that I use in my projects that read from named queries such as a sql query that’s reading from an inventory table. Is it a better design practice to bind that data to a tag and point all sources to that tag? Or to have named queries and to read that data? Maybe a better way to ask is if I have 30 users that are viewing this project, is it an easier load on the server to have all projects read that tag, or all projects polling the table separately?
I would say named queries are better for two reasons:
- Ease of maintenance. A year or five from now, you’re more likely to look for DB interactions in a place with ‘query’ in the name than in scattered tags. Sure, you can backtrack from the table, but named queries are built in a more modern way than query tags.
- Performance. Named queries support caching, which gets you the same benefit tags do (you only run the query “once” for a given span of time) but also give you easier control over that caching - you can set an expiration time and it will automatically be handled for you, rather than having to maintain that bookkeeping yourself on the tag. Also, holding a large dataset on a tag can be an ‘expensive’ operation that could cause other performance issues in your tags.
3 Likes
Awesome thanks. I was thinking along the same lines for a single table or a couple of users but didn’t know if the same held true for multiple users or if there was a sort of line where once it reaches X amount of users a tag would be better. Thank you!
Commenting to see if same is true with latest version of Igntion…thanks.
If you’re asking if you should still use Named Queries over over a memory tag of dataset type, then I would say the answer is still yes.
any differences or best practice of using named query vs the system.db functions?
Named query has caching built in so that is useful for large select queries that display on tables, less useful for update/insert queries.
If you are using a Vision application, then if you’re scripts require a system.db.functions in your scripts, you must enable Legacy Database Access which is a potential vector for SQL based security exploits via SQL injection - using Named Queries gets rid of this (as long as you only use values and not Query String types of parameters - the query string type of parameter opens the door for SQL injection again).
A year or so ago I ran some speed tests, you might want to as well, but last I checked, for insert/update queries, named queries do seem to run faster by a non-marginal amount, so if you’re running a high number of queries, named queries are the most efficient way.
Lastly, and this is a design thing, but it is nice to have one source of truth for your data sources/ way to update your datasources. Can’t tell you the number of times in old pre-named query ignition projects where two different windows were looking at the same table, had some data binding for a component. In this situation, changing the table or query would require me to manually data bindings in every spot it is used (and finding that itself is not necessarily easily if the queries aren’t written identically). With named queries, as long as I know I am using them everywhere, I only have to make one change in on place and know that anything that is calling my named query will have my changes propagated to it.
4 Likes