5 ms·

It performs well because the context switch between the application and the database is costly in terms of performance. If there is logic to be performed per ro

by jasfi 4y ago

It performs well because the context switch between the application and the database is costly in terms of performance. If there is logic to be performed per row, and that logic also requires data that's in the DB, then the performance can be much faster with PL/SQL or similar DB code. That context switch is routing queries and results between the client and server using IO requests.

Loic 4y ago
Most databases allow you to write extensions in your language of choice, this can be a good middle ground where you write domain specific DB extension in the language of your application, this way, you can share logic between your database and your application layer. I do that in Go for chemical stuff, I am pleased with this approach.
- bborud 4y ago
  Which database are you using and what kind of extensions are we talking about? (I often create Go types which are then serialized to some useful representation in the database, and de-serialized to Go types when I query the database. I usually use SQLX for most interaction with SQL databases since it does a lot of the tedious work for me (without introducing ORM'y madness). On the rare occasion where this eats too much into performance I can always use the DB library directly)
  - justinclift 4y ago
    Interestingly, there's an unofficial Go procedural language for PostgreSQL: https://gitlab.com/microo8/plgo https://gitlab.com/microo8/plgo Haven't tried it out myself though.
  - Loic 4y ago
    SQLite, I register specific functions written in Go if I want to not load the stuff in memory.
scarface74 4y ago
The argument is not that you should use application code where SQL is more efficient. The argument is that the sql shouldn’t be in the database itself as stored procedures separate from your application code.
- jasfi 4y ago
  Only DB portability would be a concern to not used stored procedures, that I can think of. If you want to write an application that can use multiple DBs then you might have a problem.
  - scarface74 4y ago
    Even if you don’t use stored procedures, simple stuff like how you limit the return results is different between databases like SQL Server and MySQL as is how you do bulk inserts. How you specify locks, the support data types, casting, intrinsic functions, conditional statements (case functions) are all different between databases.
    - jasfi 4y ago
      ORMs can abstract most things, but not stored procedures.
bborud 4y ago
I used to write a lot of PL/SQL and your assertion is simplistic at best, and misleading at worst. First off it depends on what processing you are doing on what kinds of data sizes. If you are doing relatively simple things, sure. But remember that PL/SQL isn't particularly fast if you need to do anything more complicated. Don't do anything complicated (we'll get to that in point three). Second, someone has to write that code. Writing PL/SQL is laborious because the tooling is bad. Pick any half-decent language today and the tooling is going to be fantastic. You have language servers, you have optimizing compilers, you have profilers, you have debuggers, you have lots of ways to instrument, inspect, and verify code. Where do you plan to recruit PL/SQL developers and how much are you going to pay them? Because in my experience: there really aren't that many usefully competent PL/SQL developers available on the market. There are lots of people willing to "give it a go", but competence is rare. Third, it complicates your database. Instead of just doing relational math and executing queries it now also runs code that might not be as easy to model on the same cores, eating into your database IO and memory budget and causing lots of variability. Sure you can hound your PL/SQL developers to adopt various tricks in order to lower the variability of database performance, but that gets expensive. And the code gets ugly. Fourth: It makes no sense to apply extra load to your most expensive component, which also happens to be the component class that most often also ends up being the bottleneck of your entire system. (Cloud provider pricing is a somewhat useful proxy for the cost of different systems. Have a look at the cost of RDS capacity compared to EC2) Fifth: did you measure or did you assume? In the cases where you measured a clear gain, was it still worth the cost of all of the above? Did you measure the right thing? Could you argued your case with numbers?

by jasfi 4y ago

Loic 4y ago

Most databases allow you to write extensions in your language of choice, this can be a good middle ground where you write domain specific DB extension in the language of your application, this way, you can share logic between your database and your application layer. I do that in Go for chemical stuff, I am pleased with this approach.

bborud 4y ago
Which database are you using and what kind of extensions are we talking about? (I often create Go types which are then serialized to some useful representation in the database, and de-serialized to Go types when I query the database. I usually use SQLX for most interaction with SQL databases since it does a lot of the tedious work for me (without introducing ORM'y madness). On the rare occasion where this eats too much into performance I can always use the DB library directly)
- justinclift 4y ago
  Interestingly, there's an unofficial Go procedural language for PostgreSQL: https://gitlab.com/microo8/plgo https://gitlab.com/microo8/plgo Haven't tried it out myself though.
- Loic 4y ago
  SQLite, I register specific functions written in Go if I want to not load the stuff in memory.

scarface74 4y ago

The argument is not that you should use application code where SQL is more efficient. The argument is that the sql shouldn’t be in the database itself as stored procedures separate from your application code.

jasfi 4y ago
Only DB portability would be a concern to not used stored procedures, that I can think of. If you want to write an application that can use multiple DBs then you might have a problem.
- scarface74 4y ago
  Even if you don’t use stored procedures, simple stuff like how you limit the return results is different between databases like SQL Server and MySQL as is how you do bulk inserts. How you specify locks, the support data types, casting, intrinsic functions, conditional statements (case functions) are all different between databases.
  - jasfi 4y ago
    ORMs can abstract most things, but not stored procedures.

bborud 4y ago

I used to write a lot of PL/SQL and your assertion is simplistic at best, and misleading at worst. First off it depends on what processing you are doing on what kinds of data sizes. If you are doing relatively simple things, sure. But remember that PL/SQL isn't particularly fast if you need to do anything more complicated. Don't do anything complicated (we'll get to that in point three). Second, someone has to write that code. Writing PL/SQL is laborious because the tooling is bad. Pick any half-decent language today and the tooling is going to be fantastic. You have language servers, you have optimizing compilers, you have profilers, you have debuggers, you have lots of ways to instrument, inspect, and verify code. Where do you plan to recruit PL/SQL developers and how much are you going to pay them? Because in my experience: there really aren't that many usefully competent PL/SQL developers available on the market. There are lots of people willing to "give it a go", but competence is rare. Third, it complicates your database. Instead of just doing relational math and executing queries it now also runs code that might not be as easy to model on the same cores, eating into your database IO and memory budget and causing lots of variability. Sure you can hound your PL/SQL developers to adopt various tricks in order to lower the variability of database performance, but that gets expensive. And the code gets ugly. Fourth: It makes no sense to apply extra load to your most expensive component, which also happens to be the component class that most often also ends up being the bottleneck of your entire system. (Cloud provider pricing is a somewhat useful proxy for the cost of different systems. Have a look at the cost of RDS capacity compared to EC2) Fifth: did you measure or did you assume? In the cases where you measured a clear gain, was it still worth the cost of all of the above? Did you measure the right thing? Could you argued your case with numbers?