Parameterised frozen queries

[peterswords]

In many SQL frameworks (e.g. ODBC, JDBC) you have the concept of a PreparedStatement which is an SQL statement that is precompiled for efficiency. The statement can contain placeholders which are provided by the caller before the statement is executed.

Do Piccolo frozen queries have anything likes this? Can I freeze a query but still provide values for, e.g. a WHERE clause, on the frozen query at runtime?

[dantownsend]

Hi Peter. Currently frozen queries can't be modified.

An example use case is getting the latest posts for a blog, as the query never changes:

LATEST_POSTS = Post.objects().order_by(Post.created_on).where(Post.draft == false).limit(5).freeze()

await LATEST_POSTS.run()

However, we can't do something like:

await LATEST_POSTS.where(Post.created_by = some_user).run()

It might be something we can add in the future. The underlying database adapter (asyncpg) does have support for prepared statements, so it's certainly possible.

https://magicstack.github.io/asyncpg/current/api/index.html#prepared-statements

[peterswords]

Many thanks for the quick reply. Have you measured the relative performance between frozen and unfrozen queries? If the difference is small compared to the database I/O overhead I wouldn't be too concerned. I'd expect my app (like most) to be constrained by I/O performance, and the processing part can always be horizontally scaled.

[dantownsend]

It's not a huge difference - most of the time taken is waiting for the database. It's in the order of 0.5 milliseconds difference, maybe slightly more if it's a really complex query.

It's useful for endpoints which are very performance sensitive, but usually not required.

[peterswords]

👍