What is the best way to query data?

[Pat_Cunningham]

What is the best way to query data? Named Queries or Queries in scripting.

Should I write python functions for each call and use runScript?

[drojas]

Named Queries help organize and author/test and cache data which can be very useful when re-using the same queries in multiple places and projects (via inheritance). Queries in scripting are for those who like to live dangerously and use cases where you want to dynamically generate your queries or queries with dynamic length parameters (be very careful of sql injections). Try not to use runScript. That is an expression language function that uses the UI thread to run...slow responses could affect your user's experience.

[Pat_Cunningham]

What about queries in tags?

[ryan.white]

Useful for querying data that a lot of clients/projects will be using. Instead of having every client run the same query to get the same data, you let the gateway run it at a set rate and then bind the client data/data display to that tag.

We use it to store top level configuration data, and will use an expression binding to filter the data down for use in drop downs on configuration or entry screens.

For extremely long lived data (data that rarely changes) we utilize a dataset memory tag and use a named query called from a script to fill it on gateway startup. Then, if we make any changes to that db table, we have the same interface that made the change call a script to update the memory tag using the new data from the db.

[nminchin]

@drojas

That is an expression language function that uses the UI thread to run

This is irrelevant in Perspective, since everything runs on the gateway

[drojas]

Agree. I do have some runscript() function calls in Perspective that uses some parameters...and now I'm tempted to change that to an Expression Structure to get the parameters and perform the function call within the script transform. Arrrgg...so many ways to skin a cat...which to choose...which to choose.

[pturmel]

runScript() is faster (less overhead) than a script transform.

See this experiment I ran a year ago:

Tag binding or expression to read dynamic list of tags

Collected some overhead stats. Added a sixth binding for comparison, and made them all trigger from a common reference. The new expression transform looks like this: nanoTime() - {value} It is notably faster than the other transforms. To make the recorder() run smoothly, I made a trigger timestamp with now({this.custom.pace}) and set pace to 200. Then I modified the transforms' first stages to look like this: asList( {this.custom.trigger}, nanoTime() )[1] The non-transform tests of run…

[drojas]

I don't see a problem using tags to query data. I do that to get notified when new records are available in a database for example...then you can have gateway tag change scripts or value changed event scripts within the tag itself.

[lrose]

@drojas

I don't see a problem using tags to query data

So long as they are configured correctly. If someone comes along and sets them to be polled quickly you can hammer your DB.

I avoid them unless absolutely needed, preferring to query data in an environment where I can control absolutely how often it is queried.

[Pat_Cunningham]

I am trying to take this all in.

I currently have all of my data select (dataset and scalar) and updates in script and I am calling them either through runScript, Events or Scripts.

I think I am going to stay with what I have and avoid tag query's and named queries because I can't really see a good reason to use them and doing everything in script seems cleaner.

[Alejandro_Alaco]

The problem I found with named queries is that you can´t filter them with a multiselect of a parameter, have to do it with scripting query.

Filtering table from multiselection dropdown

You cannot pass lists into single JDBC parameters. If you script your query with runPrepQuery instead of using a named query, you can dynamically generate question mark placeholders for all of the list elements, then pass the correct length argument list.

But also named queries are quite useful to have all queries well organized, easy to edit and test and can recall them from any part of the app.

[pturmel]

@Pat_Cunningham

can't really see a good reason to use them

You can use Named Queries in bindings with proper JDBC parameters. Bindings are always more performant without scripts (or instead of scripts). Scripts have more overhead than anything else in Ignition, but are needed for certain cases (as mentioned).

Named queries are the right tool for many tasks in Ignition. I recommend using them in Query bindings in UIs.

[lrose]

@Pat_Cunningham

I think I am going to stay with what I have and avoid tag query's and named queries because I can't really see a good reason to use them and doing everything in script seems cleaner.

There are good reasons to use them.

1. They keep your queries organized in a single place and callable from anywhere in the project.
2. Make reusing the same query simpler.
3. Can be cached.
4. Can be used in bindings.

Here is some more advise about when you should and should not use a Named Query

runprepQuery or runNamedQuery - Best Practice

With the exception of some special cases (ahmmm QueryString parameters). You should almost always default to Named Queries. Performance wise, I doubt there is anything more than negligible gains or losses between the two functions in a scripting environment. They both use the same JDBC driver, so the same queries will basically run exactly the same. Although, I will admit that I don't know if Named Query caching applies to the scripting function or just bindings, so that could make a differe…

[Pat_Cunningham]

Thank you everyone for the information. I am going to change all of my bindings to Named queries and if I need to cache one I will use them but I think it is much easier to organize and make updates to my queries in script functions.