4 ms·

in terms of performance, i would err on the side of reducing work for the database, assuming the middleware/rpc layer is written in a language that has a fast r

by a-dub 5y ago

in terms of performance, i would err on the side of reducing work for the database, assuming the middleware/rpc layer is written in a language that has a fast runtime. i suspect that using a smaller number of prepared queries at the cost of shipping more data around (especially if the number of rows per query is limited) would be more performant than custom queries. prepared queries avoid repetitive string parsing of the query language and give the db backend more opportunities to optimize. less entropy in the overall workload also can result in better utilization of caching. if there are a few common display filters, additional prepared queries could be added to optimize them. in practice, it's unlikely that there would be a lot of entropy in column selection.

gruez 5y ago
>i suspect that using a smaller number of prepared queries at the cost of shipping more data around (especially if the number of rows per query is limited) would be more performant than custom queries. that depends entirely on how many columns/rows you're returning. > prepared queries avoid repetitive string parsing of the query language and give the db backend more opportunities to optimize. On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement.
- a-dub 5y ago
  > On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement. sessions should live for the lifetime of the processes of the backend application server, so they would get updated every time you deploy. if you don't have a long living backend, then usually a long living proxy is used. if you're setting up a new session on every request, it seems there's not really much point to worrying about performance! anyway, every application is different and it's worth benchmarking. if i were building something today that used a sql backend, i would err on the side of using prepared statements as much as possible, both for security and performance reasons. maybe string queries could make sense, but they're both more expensive to run and more likely to result in a vulnerability, even if they were coded carefully at first implementation.

by a-dub 5y ago

gruez 5y ago

>i suspect that using a smaller number of prepared queries at the cost of shipping more data around (especially if the number of rows per query is limited) would be more performant than custom queries. that depends entirely on how many columns/rows you're returning. > prepared queries avoid repetitive string parsing of the query language and give the db backend more opportunities to optimize. On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement.

a-dub 5y ago
> On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement. sessions should live for the lifetime of the processes of the backend application server, so they would get updated every time you deploy. if you don't have a long living backend, then usually a long living proxy is used. if you're setting up a new session on every request, it seems there's not really much point to worrying about performance! anyway, every application is different and it's worth benchmarking. if i were building something today that used a sql backend, i would err on the side of using prepared statements as much as possible, both for security and performance reasons. maybe string queries could make sense, but they're both more expensive to run and more likely to result in a vulnerability, even if they were coded carefully at first implementation.