4 ms·

Let's say I have a table in my application, and I want the user to be able to select which columns of a very wide table to show. I could of course return all co

Let's say I have a table in my application, and I want the user to be able to select which columns of a very wide table to show. I could of course return all columns all the time and filter on the frontend, but the more efficient way would be to determine the columns to be fetched from user input. And at that point I need to derive column names from a user string. Of course this should be implemented as picking from a whitelisted set of column names.

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.
function_seven 5y ago
I've had to do similar in the past. I ended up enumerating all the columns, intersecting that set with the ones submitted by the user, and using the result in the SELECT clause of the query. Not as easy as just dropping in the user's choices, but guarantees they have no control over the resulting query string.

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.

function_seven 5y ago

I've had to do similar in the past. I ended up enumerating all the columns, intersecting that set with the ones submitted by the user, and using the result in the SELECT clause of the query. Not as easy as just dropping in the user's choices, but guarantees they have no control over the resulting query string.