runprepQuery or runNamedQuery - Best Practice

[Thiago_Vilela]

Hello,

I would like to know what is a best practice / best performance using runprepQuery vs runNamedQuery.

I developed the same system and it will run in 4 different stations, with different parameters, but the insert/updates will use the same named query or script (runprepQuery).

I was wondering what would be the best way.

Thanks

[lrose]

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 difference.

Best practice is more nuanced in this case. For performance when it comes to perspective see this topic:

Building for Performance

In this example, #3 is the most performant, though it could be argued that the performance gain in this instance would be negligible. Anytime you add an additional processing step you add latency. In general for the best performance I follow these guidelines: If you can achieve what you want with a binding, use a binding. Always use the binding type that is the most direct. (e.g. if all you need is a binding to a property use a property binding, not an expression binding.) Given the choice b…

For a reference of when you should use runPrepQuery over runNamedQuery see this topic

runNamedQuery vs runPrepQuery

The original purpose of Named Queries was to make it possible to pass values into Vision Query bindings in a secure and efficient way, solving the unreliability of parameter stringification (looking at you, java.util.Date), and allowing the huge "legacy DB access" security hole to be closed. The maintenance advantages of NQs can be matched by multi-line strings for SQL in library scripts, so I tend to discount that item. However, not all is well. Some things to consider: NQs permit the use…

In short -

• If you can use a named query, you should use a binding if at all possible.
• If you don't need some type of dynamic SQL*, use a named query
• If you do need some type of dynamic SQL, use a prep query.
• If you are not using a competent DB engine (a.k.a MSSQL), and you need to provide an array of values (e.g. SELECT * FROM table WHERE column IN (value1,value2,value3)) use a prep query.
• If you need to batch insert, use a prep query.

*Dynamic SQL as in a user is providing schema level objects such as column names.

There may be other edge cases where a prep query is the solution, but for most general cases, just use a Named Query

[KathyApplebaum]

@lrose

I don't know if Named Query caching applies to the scripting function or just bindings

The caching only looks at the resource path of the query, any query limit in place, and the value of the parameters. If those match an existing, unexpired entry, and the query is marked in the config as having caching permitted, you will get back the cached value, no matter where the query originated.

[edited to add -- this is only true for SELECT queries. Using a cache for UPDATE queries doesn't make a lot of sense]

[pturmel]

It should be mentioned that for the cache to be checked, the query parameters must be an exact match. If you are feeding timestamps to named queries, you may want to normalize them to even seconds, or even multiples of five seconds, or even minutes, et cetera. To maximize the chance of hitting a cached result.

[Thiago_Vilela]

Awesome.
Thank you so much for the references.

Thank you to @KathyApplebaum and @pturmel too.

[Thiago_Vilela]

What about Scripting (project Library), Custom Method or Property Change Script(valueChanged)?
I understand it is subjective and depends on the project, but what would be the best practice?

For most of my projects I use Script in the Library, and for example, call the script when the buttons are pressed.

[pturmel]

@Thiago_Vilela

For most of my projects I use Script in the Library, and for example, call the script when the buttons are pressed.

This is the Right Answer™, in my not-so-humble opinion. Events and custom methods should always be a one-liner calling a project library script function, passing any necessary arguments.

This is especially important for gateway events, to avoid issues with those events' legacy scoping rules. (I sure hope that changes in v8.3.)