5 ms·

This discusses the "how" but not the "why." I'm not a SQL expert, so I find it far easier to maintain my code in a language I am familiar with than to maintain

This discusses the "how" but not the "why." I'm not a SQL expert, so I find it far easier to maintain my code in a language I am familiar with than to maintain (seemingly cryptic) SQL equations, queries, etc. Is there a quantifiable performance gain with moving more operations to SQL rather than implementing it in my language of choice? The conclusion states: > Exploit the power of SQL to write less code because “the best code is the code that was never written”. If it is not written there is no need to maintain it. But they're wrong, since you still need to maintain all of the SQL implementations... Edit: I previously said "I'm not a DBA", but some folks seem to be getting hung up on that rather than the rest of my comments/questions.

thecopy 8y ago
There is a significant difference between being a DBA and knowing SQL Regarding your question: > Is there a quantifiable performance gain with moving more operations to SQL rather than implementing it in my language of choice? It depends, of course, but in my experience the answer is yes for 90% of the cases.
- notahappycamper 8y ago
  Is this due to query optimization, or something else?
  - vonseel 8y ago
    I’d love to see an answer from someone more experienced on this as well. In my experience, the database is just faster than application code at most stuff and queries can be written in a way that similar logic can’t be optimized at the application level. Additionally, database code is usually (always?) going to be faster than something like Python/Ruby. Code review is more difficult at the database level, however, and SQL talent is more rare. It may be more challenging to scale and maintain a team of engineers working on a codebase with a lot of application logic implemented in stored procs/SQL versus ORM code (such as active record or Django). Just my opinion, have worked on teams with ORM usage as well as separate DBA teams and all logic in SQL. SQL by hand is more powerful and flexible, surely, but for most use cases I would prefer to keep code in one place (application) and only reach for the tool (raw SQL) when needed for optimizations.
  - perfmode 8y ago
    RMBDS systems are very good at pruning results. They can perform complex multi step operations without round trips. Etc
  - PeterisP 8y ago
    There's a meaningful difference to where a particular calculation happens. Even if the exact same steps are done, it's more efficient to look at 10 items in the database, pick 1 item, and transfer it out to the application rather than transfer 10 items to the application and then pick one item there; doing the calculation close to the source data enables ways to do it efficiently.
  - eveningcoffee 8y ago
    Optimizer (with the statistical knowledge about the data) and being closer to the data (important data may be always in memory) and most importantly supportive data structures like indexes and materialized views.
TimJYoung 8y ago
Key-value stores are fine if you are always simply grabbing a single row/tuple from a single dataset. Any time you move beyond that, you're in for a world of hurt compared to a relational database that uses SQL. The reasons are: 1) You often need a transactional, consistent view of the data across multiple datasets (tables). Relational databases using SQL provide that, key-value stores may or may not (most likely not). 2) Any time you're doing any kind of aggregation, key-value stores will require that you pull all data in locally and perform the operation there. It's questionable whether the local environment even has the resources to accomplish such a task, and the efficiency of such operations will be dismal compared to a SQL-based relational database server. Key-value stores are, from an interface perspective, very much like the older ISAM databases that were popular before SQL and relational databases gained a foothold in the industry. They also recycled the exact same problems that SQL-based relational databases solved so elegantly (round and round we go). Another way to put it is this: leaving aside scaling for now, SQL-based relational databases can typically do everything that a key-value store can do, whereas key-value stores cannot typically do everything that a SQL-based relational database can do.
- craftyguy 8y ago
  I wasn't so much asking about relational databases vs key/value stores, but rather why should I implement things like advanced sorting, mathematical formulas, etc in SQL rather than in the application code that fetches data from SQL?
  - tomnipotent 8y ago
    > why should I implement things ... in SQL It's simply more efficient? If I have a table with millions of rows, I'm not going to bring down the entire data set on every request just to sort by latest and grab the 10 most recently updated rows that haven't been soft deleted (WHERE active=1 ORDER BY updated_at DESC LIMIT 10).
    - craftyguy 8y ago
      Well, obviously limiting results/rows should be done in SQL.. but that's not what the article is demonstrating..
CuriouslyC 8y ago
The difference really becomes apparent when your database isn't an app specific data store, but a business data warehouse that is used in many places. You could build a full API layer and force people to go through that for everything, then have another thing to maintain, or you can implement it at the data level. Each method has pros and cons, it depends on the capabilities of your database, your requirements in terms of security, and the degree of cross connection between data stores. For internal business tools I like to do stuff at the database layer, but it would be the wrong choice for a high volume, simple, public API.

thecopy 8y ago

There is a significant difference between being a DBA and knowing SQL Regarding your question: > Is there a quantifiable performance gain with moving more operations to SQL rather than implementing it in my language of choice? It depends, of course, but in my experience the answer is yes for 90% of the cases.

notahappycamper 8y ago
Is this due to query optimization, or something else?
- vonseel 8y ago
  I’d love to see an answer from someone more experienced on this as well. In my experience, the database is just faster than application code at most stuff and queries can be written in a way that similar logic can’t be optimized at the application level. Additionally, database code is usually (always?) going to be faster than something like Python/Ruby. Code review is more difficult at the database level, however, and SQL talent is more rare. It may be more challenging to scale and maintain a team of engineers working on a codebase with a lot of application logic implemented in stored procs/SQL versus ORM code (such as active record or Django). Just my opinion, have worked on teams with ORM usage as well as separate DBA teams and all logic in SQL. SQL by hand is more powerful and flexible, surely, but for most use cases I would prefer to keep code in one place (application) and only reach for the tool (raw SQL) when needed for optimizations.
- perfmode 8y ago
  RMBDS systems are very good at pruning results. They can perform complex multi step operations without round trips. Etc
- PeterisP 8y ago
  There's a meaningful difference to where a particular calculation happens. Even if the exact same steps are done, it's more efficient to look at 10 items in the database, pick 1 item, and transfer it out to the application rather than transfer 10 items to the application and then pick one item there; doing the calculation close to the source data enables ways to do it efficiently.
- eveningcoffee 8y ago
  Optimizer (with the statistical knowledge about the data) and being closer to the data (important data may be always in memory) and most importantly supportive data structures like indexes and materialized views.

TimJYoung 8y ago

Key-value stores are fine if you are always simply grabbing a single row/tuple from a single dataset. Any time you move beyond that, you're in for a world of hurt compared to a relational database that uses SQL. The reasons are: 1) You often need a transactional, consistent view of the data across multiple datasets (tables). Relational databases using SQL provide that, key-value stores may or may not (most likely not). 2) Any time you're doing any kind of aggregation, key-value stores will require that you pull all data in locally and perform the operation there. It's questionable whether the local environment even has the resources to accomplish such a task, and the efficiency of such operations will be dismal compared to a SQL-based relational database server. Key-value stores are, from an interface perspective, very much like the older ISAM databases that were popular before SQL and relational databases gained a foothold in the industry. They also recycled the exact same problems that SQL-based relational databases solved so elegantly (round and round we go). Another way to put it is this: leaving aside scaling for now, SQL-based relational databases can typically do everything that a key-value store can do, whereas key-value stores cannot typically do everything that a SQL-based relational database can do.

craftyguy 8y ago
I wasn't so much asking about relational databases vs key/value stores, but rather why should I implement things like advanced sorting, mathematical formulas, etc in SQL rather than in the application code that fetches data from SQL?
- tomnipotent 8y ago
  > why should I implement things ... in SQL It's simply more efficient? If I have a table with millions of rows, I'm not going to bring down the entire data set on every request just to sort by latest and grab the 10 most recently updated rows that haven't been soft deleted (WHERE active=1 ORDER BY updated_at DESC LIMIT 10).
  - craftyguy 8y ago
    Well, obviously limiting results/rows should be done in SQL.. but that's not what the article is demonstrating..

CuriouslyC 8y ago

The difference really becomes apparent when your database isn't an app specific data store, but a business data warehouse that is used in many places. You could build a full API layer and force people to go through that for everything, then have another thing to maintain, or you can implement it at the data level. Each method has pros and cons, it depends on the capabilities of your database, your requirements in terms of security, and the degree of cross connection between data stores. For internal business tools I like to do stuff at the database layer, but it would be the wrong choice for a high volume, simple, public API.