runprepQuery or runNamedQuery - Best Practice

[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