vely:Prepared statements

Prepared statements

Prepared queries

A prepared query differs only in that it is natively prepared for the database you are using. It is pre-compiled and (among other things) its execution plan is created once, instead of each time a query executes. The statement is cached going forward for the life of the process (with the rare exception of re-establishing a lost database connection). It means effectively an unlimited number of requests will be reusing the query statement.

In practicality it means the execution of a query may be faster due to being prepared only once and executed many times.

Note that databases do not allow prepared queries for DDL (Data Definition Language), as there is not much benefit in general, hence only DML queries (such as INSERT, DELETE etc.) and SELECT can be prepared.

Caching of queries

In order to cache a query statement, Vely will save query text that actually executes the very first time it runs. Then, regardless of what query text you supply in the following executions, it will not mutate anymore. It means from that moment onward, the query will always execute that very same query text, just with different input parameters.

In general, majority of queries are static and not dynamic, so this works just fine. When you use prepared statements with dynamic queries though, you should be aware that query text is immutable. For instance, code in a request that is called many times may look like:

char *qry;
static char is_query_built = 0;

if (!is_query_built) {
    qry = create_query();
    is_query_built = 1;
}

run-prepared-query @mydb = qry output col1, col2, col3 : inp1, inp2
end-query

In the above example, query text "qry" is built only once (by using static boolean "is_query_built"), because the actual query text is needed only for the very first execution of query "myquery", even when connection is lost and re-established. This improves performance beyond what prepared statement may already do. You can, of course, build it every time (i.e. not use the boolean or a similar mechanism), but the query text that actually executes will not change after the very first run. The input parameter variables (such as "inp1" and "inp2" in this example) may change, of course.

In a majority of code, a query may be static as in:

run-prepared-query @mydb = "select col1 from test where someID='%s'" output col1 : id_value
end-query

then clearly the query text is a string literal and will never change. All that changes is the input parameters (in this case "id_value"), and of course the output (in this case "col1"). Most queries are like this.

SQL injections

Note that regardless of whether you use prepared statements or not, the execution of your queries is guarded against SQL injections:

in case of non-prepared queries, the separation of data and logic is emulated and input parameters are sanitized by Vely; and the query sent to the server is a single text statement,
in case of prepared queries, the separation of data and logic is native to the database and thus the two are separated the entire way, so there is no need to sanitize the input; the query identification (not the query itself) and data are sent to the server separately.

Performance

In most cases, prepared statement will exhibit better performance, and this is particularly true in Vely, where Vely FCGI processes (see vely_architecture and how_vely_works) keep a single database connection (and thus a single session) open for the life of the process (re-creating it only when the connection is lost). Because of this, a prepared statement is done so once; and then reused and re-run many times over afterwards.

In some cases, you might not want to use prepared statements. Some reasons may be:

your statements are often changing and dynamically constructed to the point where managing a great many equivalent prepared statements may be impractical - for example there may be a part of your query text that comes from outside your code,
your dynamic statements do not execute as many times, which makes prepared statements slower, since they require two trips to the database server to begin with,
your query cannot be written as a prepared statement,
in some cases prepared statements are slower because the execution plan depends on the actual data used, in which case non-prepared statement may be a better choice,
in some cases the database support for prepared statements may still have issues compared to non-prepared,
typically prepared statements do not use query cache, so repeating identical queries with identical input data may be faster without them.

PostgreSQL

You may get an error like:

could not determine data type of parameter $N

when preparing statements for PostgreSQL. This is an issue with Postgres server, and has nothing to do with Vely, for example in statement:

select col1 from test where someId>='%s' and col1 like concat( '%s' ,'%')

you might get an error "could not determine data type of parameter $2". An issue like this may be that Postgres cannot determine the type, or it may be a bug in Postgres; regardless, this is not a Vely issue. In this case $2 is the second '%s' input parameters and you should specify the type manually for Postgres, generally in form of

...  '%s'::<type> ...

In this case, the type in question is "text", so your statement would be:

select col1 from test where someId>='%s' and col1 like concat( '%s'::text ,'%')

This solution generally works for any Postgres client, not just Vely, regardless of how is the positional input parameter specified.