3 ms·

The article doesn't explain why this is a bad idea for database servers hosted on a remote machine. The first reason is obvious... Network connections take memo

The article doesn't explain why this is a bad idea for database servers hosted on a remote machine. The first reason is obvious... Network connections take memory and processing power on both client and server. Each additional query causes more resource usage. It is unnecessary overhead which is why things like multiple active result sets were created for SQL Server. The network round trip time can also add up if you run into resource constraints doing this. On a remote database, you also have to contend with multiple users and so complicated locking techniques can come into play depending upon the complexity of all database activity. Many databases have options to return multiple result sets from one connection which helps control the overhead caused by this usage pattern. EDIT: This also brings back horrible memories where developers would do this in a db client server architecture. Then they would often not close the DB connections when done. So you could have thousands of active connections basically doing nothing. Luckily, this problem was solved with better database connection handling.

simonw 2y ago
These days there are other tricks you can use to turn several SQL queries into a single round-trip too, with things like JSON aggregates. Here's an example PostgreSQL query that returns 10 rows from one table and 20 rows from another table in a single network round-trip, using JSON serialization to return the different shaped rows in one go: https://simonwillison.net/dashboard/union-json-demo/ https://simonwillison.net/dashboard/union-json-demo/ Related trick: https://til.simonwillison.net/sqlite/related-rows-single-query https://til.simonwillison.net/sqlite/related-rows-single-que...
- Nathanba 2y ago
  it is very interesting, I have a similar blog post in my bookmarks as well: https://www.crunchydata.com/blog/generating-json-directly-from-postgres https://www.crunchydata.com/blog/generating-json-directly-fr... the problem is that it only works with Postgres, not mysql or sqlite or pretty much anything else (at least not as conveniently) and the bigger problem is that the queries become more complex
  - simonw 2y ago
    It works with SQLite too - the functions have different names (json_group_array() and json_object() rather than PostgreSQL's json_agg() and json_build_object()) but they work pretty much exactly the same. The one JSON PostgeSQL feature that SQLite doesn't have yet and I miss is row_to_json(record) which serializes an entire row without you needing to specifically list the column names. I've not tried this stuff in MySQL myself yet but it looks like JSON_ARRAYAGG() might be the equivalent there: https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html#function_json-arrayagg https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions....
    - Nathanba 2y ago
      yes unfortunately only postgres has that convenient thing where you don't have to go out of your way to specify all the column names. SQLServer has it too by a different name but not MySQL
slaymaker1907 2y ago
Yes, you get some performance improvements, but I think that comes at the price of security isolation. Think about it, your application probably requires tons of libraries and evolves really quickly compared to the database itself. Additionally, having Admin/root permissions on the server hosting the DB is generally a much bigger deal than granting such permissions on an application server that talks to that DB. If none of this makes sense, don't worry, that just means you don't work in enterprise...

simonw 2y ago

These days there are other tricks you can use to turn several SQL queries into a single round-trip too, with things like JSON aggregates. Here's an example PostgreSQL query that returns 10 rows from one table and 20 rows from another table in a single network round-trip, using JSON serialization to return the different shaped rows in one go: https://simonwillison.net/dashboard/union-json-demo/ https://simonwillison.net/dashboard/union-json-demo/ Related trick: https://til.simonwillison.net/sqlite/related-rows-single-query https://til.simonwillison.net/sqlite/related-rows-single-que...

Nathanba 2y ago
it is very interesting, I have a similar blog post in my bookmarks as well: https://www.crunchydata.com/blog/generating-json-directly-from-postgres https://www.crunchydata.com/blog/generating-json-directly-fr... the problem is that it only works with Postgres, not mysql or sqlite or pretty much anything else (at least not as conveniently) and the bigger problem is that the queries become more complex
- simonw 2y ago
  It works with SQLite too - the functions have different names (json_group_array() and json_object() rather than PostgreSQL's json_agg() and json_build_object()) but they work pretty much exactly the same. The one JSON PostgeSQL feature that SQLite doesn't have yet and I miss is row_to_json(record) which serializes an entire row without you needing to specifically list the column names. I've not tried this stuff in MySQL myself yet but it looks like JSON_ARRAYAGG() might be the equivalent there: https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html#function_json-arrayagg https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions....
  - Nathanba 2y ago
    yes unfortunately only postgres has that convenient thing where you don't have to go out of your way to specify all the column names. SQLServer has it too by a different name but not MySQL

slaymaker1907 2y ago

Yes, you get some performance improvements, but I think that comes at the price of security isolation. Think about it, your application probably requires tons of libraries and evolves really quickly compared to the database itself. Additionally, having Admin/root permissions on the server hosting the DB is generally a much bigger deal than granting such permissions on an application server that talks to that DB. If none of this makes sense, don't worry, that just means you don't work in enterprise...