BEGIN
DECLARE string PARAM1 = cast(? as string);
SELECT ... WHERE COLUMN1 = $1 AND COLUMN2 = $1 ...;Prepared Statements
Teiid provides a standard implementation of java.sql.PreparedStatement. PreparedStatements can be very important in speeding up common statement execution, since they allow the server to skip parsing, resolving, and planning of the statement. See the Java documentation for more information on PreparedStatement usage.
PreparedStatement Considerations
-
It is not necessary to pool client side Teiid
PreparedStatements, since Teiid performs plan caching on the server side. -
The number of cached plans is configurable (see the Admin Guide), and are purged by the least recently used (LRU).
-
Cached plans are not distributed through a cluster. A new plan must be created for each cluster member.
-
Plans are cached for the entire VDB or for just a particular session. The scope of a plan is detected automatically based upon the functions evaluated during it’s planning process.
-
Stored procedures executed through a
CallableStatementhave their plans cached just as aPreparedStatement. -
Bind variable types in function signatures, e.g. "where t.col = abs(?)" can be determined if the function has only one signature or if the function is used in a predicate where the return type can be determined. In more complex situations it may be necessary to add a type hint with a cast or convert, e.g. upper(convert(?, string)).
-
If you have the same value of a binding repeated multiple times in your query, you can consolidate that usage in a couple of ways.
-
The query can be enclosed as a anonymous procedure block:
-
Note the cast of the bind variable, which is due to a small issue with the resolver that isn’t inferring the type from the variable declaration.
-
You may also use the not officially supported PostgreSQL like feature of $n positional bindings:
SELECT ... WHERE COLUMN1 = $1 AND COLUMN2 = $1 ...