8 ms·

You Can Do It in SQL, Stop Writing Extra Code for That

76 points by geshan 8y ago

craftyguy 8y ago
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.
ThJ 8y ago
It's funny that this even needs to be a blog post. I can see why simple key-value stores have become so popular in recent years. Developers don't want to learn SQL properly, and end up using the database as if it were a key-value store, and at some point, they begin to question why they need SQL at all, and switch to NoSQL. Later, when they do need more sophisticated querying, they then have to slap Elastic on top of their key-value store to get that feature back, when they could've been using SQL all along. Full circle!
- ben_jones 8y ago
  I agree with what you're saying but I think its important to point out that there is a Developer experience in most mainstream languages and then there is the Operations experience of deploying, configuring, maintaining, scaling, and paying for, various RDBMS. Developers grow up crashing their programs. Crashing your DBMS (or similiarly messing it up) is not as friendly or immediate in feedback/consequence, so people shy away from it. The end result is that many developers are influenced to use code more and databases less.
  - 0db532a0 8y ago
    Elasticsearch quite conveniently makes it so difficult and fucking annoying to do data analytics with their pile of turd custom query un-language shambles that it leaves anyone stuck with it rightly reinventing oblong wheels with diamond bearings lubricated with iron filings in code. Aah, but dev-mis-ops say that it’s easy to deploy and maintain on AWS. It scales too! Well, at least the whole set-up keeps everyone employed.
    - KennyCason 8y ago
      Could you elaborate more on your specific ES problems? Having worked with Elasticsearch extensively for many years, and do not share this sentiment. Many of the ES queries fundamentally aren't meant to have a 1-to-1 equivalent in SQL as ES's goal is to specialize as a distributed search index, and not a general relative database. If your point is that ES is being more frequently used as an alternative to just writing/scaling/using a SQL service to it's full potential, then I can somewhat understand the sentiment.
AndyPatterson 8y ago
Off the top of my head I'm thinking that there's a serious lack of thought in these use cases, especially string manipulation which I could imagine becoming painfully slow as the database grows; any manipulation of the projection like this is going to incur a large performance overhead. It would be quite good to see this blog post, or a response to it, evaluating the SQL codes performance on different datasets.
wolfgang42 8y ago
Relational databases are brilliant, but SQL is terrible and I don't understand how it hasn't gone the way of COBOL yet. For a start, the bizarrely English-oriented structure forces a completely different ordering of the code than what comes out, and the syntax appears to completely ignore the underlying tabular concepts. Not to mention that IMO joining on foreign keys shouldn't be a thing--the database should be able to figure out what you want when you write `order.customer.address.taxRegion.rate` without specifying any joins at all. Even if you use an ORM you still have to understand the code it will emit to avoid building bad queries. There's just so much awkwardness in trying to write a SQL query that I can definitely see why people would avoid it.
- peatmoss 8y ago
  The brilliance of RDBMSs is the relational algebra. But, I agree SQL can sometimes feel a little verbose / obtuse. I’ve had learning datalog on my todo list for a long time. I’d love to see some other DSls for the relational algebra as well
  - contingencies 8y ago
    Re: Verbose/obtuse. There are different ways to use SQL. For example IMHO if you use the unnecessary "<LEFT/RIGHT> JOIN" syntax then statements often come across as pretty unreadable, whereas you can generally achieve the same with alternate and more readable syntax.
    - tfehring 8y ago
      As opposed to SELECT * FROM a, b WHERE a.id = b.id? Looks ok on paper, but in exchange for brevity you end up demolishing your readability on complex queries, since you can’t easily identify filters (as opposed to join conditions). Most style guides, including my team’s, consider explicit JOINs mandatory, and there’s a good reason for doing so. Admittedly my work is very heavy on data manipulation; maybe complex queries are less of a concern for other use cases.
      - 8y ago
zzo38computer 8y ago
SQL is a real programming language (even though, many people seems to not think so). I use SQLite, although there are other SQL implementations (with different features) too. It is true many thing can be done well with SQL, whether you call from other programs or use standalone. Whether it will be faster or not to use SQL probably depends on what SQL implementation is in use, whether the indexing is set up properly, and what programming language you are calling it from. Also, much things can be done with a shorter code in SQL if you are doing such things as sorting the result set.
catchmeifyoucan 8y ago
I think the problem is with Excel sheets and CSVs. We aren't taught how to manipulate data otherwise without the use of code. SQL seems to have immense overhead. Set up a db, import data, etc. Although it makes sense, it's an afterthought. We know how to write scripts, but we don't necessarily need to learn SQL to get the job done.
nycdotnet 8y ago
I understand where the author is coming from, and for purposes of aggregation or filtering, they are correct; it’s often better to do those things in SQL. There are two problems, though. CPU and IOs on your DB server are likely your organization’s most scarce resource, and SQL is often hard to test and has unpredictable performance characteristics (cliffs, blocking, etc). By all means do the things you must do to get your app servers the data they need with the fewest IOs and bytes across the wire possible, but please do your string concatenation and sorting (when not using TOP or LIMIT) on cheap app servers that you can always deploy more of.
hunter23 8y ago
Be careful in using group_concat in mysql - it actually truncates the result to the max_len setting which is by default 1024 and will not tell you it truncates the result in the logs. it took us forever to find the cause of this issue in one of our batch jobs (I wish mysql issued a warning when truncating): "In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause. To sort values in the result, use the ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. The default separator between values in a group is comma (,). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''. The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer" https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.h...
- geshan 8y ago
  There is a setting to make it longer.
  - hunter23 8y ago
    Yes this is mentioned in the documentation I quoted.
- opless 8y ago
  Then clearly mysql isn't the database you want. Perhaps MSSQL or postgresql or even (spit) Oracle to name a few. All massively more capable than the toydb that is mysql. I think the trouble with most developers is that their experience is mostly with this terrible DB.
lsiebert 8y ago
Some things are harder to do in SQL then in code. For example, assigning a rank based on a field in MYSQL. I did this for production code in a rails app, and it wasn't just ugly (requiring separate DB calls for variable initialization and output and updating) but it ran slow enough that it had to be scheduled for off peak time, and it requires temporary variables (or you can do it with whole temporary tables, I believe). https://stackoverflow.com/questions/3333665/rank-function-in-mysql https://stackoverflow.com/questions/3333665/rank-function-in... That's not to say that I would do it in Ruby if I had it to do over again, I'd probably use Redis, but still, sometimes SQL isn't as fast as other solutions. I like SQL. Optimizing DB calls to speed up stuff is fun for me. But it's not always the right choice. (There's probably a clever and fast way to do it in postgres, but DB choices were not mine to make). edit: Incidentally, if you just need to know 1 record's rank at a time, like getting a player's position on a leaderboard, instead of including each record in the DB, you can do a select on scores (or distinct scores) greater then the current record's score and add 1, and that can be much faster.

76 points by geshan 8y ago

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.

ThJ 8y ago

It's funny that this even needs to be a blog post. I can see why simple key-value stores have become so popular in recent years. Developers don't want to learn SQL properly, and end up using the database as if it were a key-value store, and at some point, they begin to question why they need SQL at all, and switch to NoSQL. Later, when they do need more sophisticated querying, they then have to slap Elastic on top of their key-value store to get that feature back, when they could've been using SQL all along. Full circle!

ben_jones 8y ago
I agree with what you're saying but I think its important to point out that there is a Developer experience in most mainstream languages and then there is the Operations experience of deploying, configuring, maintaining, scaling, and paying for, various RDBMS. Developers grow up crashing their programs. Crashing your DBMS (or similiarly messing it up) is not as friendly or immediate in feedback/consequence, so people shy away from it. The end result is that many developers are influenced to use code more and databases less.
- 0db532a0 8y ago
  Elasticsearch quite conveniently makes it so difficult and fucking annoying to do data analytics with their pile of turd custom query un-language shambles that it leaves anyone stuck with it rightly reinventing oblong wheels with diamond bearings lubricated with iron filings in code. Aah, but dev-mis-ops say that it’s easy to deploy and maintain on AWS. It scales too! Well, at least the whole set-up keeps everyone employed.
  - KennyCason 8y ago
    Could you elaborate more on your specific ES problems? Having worked with Elasticsearch extensively for many years, and do not share this sentiment. Many of the ES queries fundamentally aren't meant to have a 1-to-1 equivalent in SQL as ES's goal is to specialize as a distributed search index, and not a general relative database. If your point is that ES is being more frequently used as an alternative to just writing/scaling/using a SQL service to it's full potential, then I can somewhat understand the sentiment.

AndyPatterson 8y ago

Off the top of my head I'm thinking that there's a serious lack of thought in these use cases, especially string manipulation which I could imagine becoming painfully slow as the database grows; any manipulation of the projection like this is going to incur a large performance overhead. It would be quite good to see this blog post, or a response to it, evaluating the SQL codes performance on different datasets.

wolfgang42 8y ago

Relational databases are brilliant, but SQL is terrible and I don't understand how it hasn't gone the way of COBOL yet. For a start, the bizarrely English-oriented structure forces a completely different ordering of the code than what comes out, and the syntax appears to completely ignore the underlying tabular concepts. Not to mention that IMO joining on foreign keys shouldn't be a thing--the database should be able to figure out what you want when you write `order.customer.address.taxRegion.rate` without specifying any joins at all. Even if you use an ORM you still have to understand the code it will emit to avoid building bad queries. There's just so much awkwardness in trying to write a SQL query that I can definitely see why people would avoid it.

peatmoss 8y ago
The brilliance of RDBMSs is the relational algebra. But, I agree SQL can sometimes feel a little verbose / obtuse. I’ve had learning datalog on my todo list for a long time. I’d love to see some other DSls for the relational algebra as well
- contingencies 8y ago
  Re: Verbose/obtuse. There are different ways to use SQL. For example IMHO if you use the unnecessary "<LEFT/RIGHT> JOIN" syntax then statements often come across as pretty unreadable, whereas you can generally achieve the same with alternate and more readable syntax.
  - tfehring 8y ago
    As opposed to SELECT * FROM a, b WHERE a.id = b.id? Looks ok on paper, but in exchange for brevity you end up demolishing your readability on complex queries, since you can’t easily identify filters (as opposed to join conditions). Most style guides, including my team’s, consider explicit JOINs mandatory, and there’s a good reason for doing so. Admittedly my work is very heavy on data manipulation; maybe complex queries are less of a concern for other use cases.
    - 8y ago

zzo38computer 8y ago

SQL is a real programming language (even though, many people seems to not think so). I use SQLite, although there are other SQL implementations (with different features) too. It is true many thing can be done well with SQL, whether you call from other programs or use standalone. Whether it will be faster or not to use SQL probably depends on what SQL implementation is in use, whether the indexing is set up properly, and what programming language you are calling it from. Also, much things can be done with a shorter code in SQL if you are doing such things as sorting the result set.

catchmeifyoucan 8y ago

I think the problem is with Excel sheets and CSVs. We aren't taught how to manipulate data otherwise without the use of code. SQL seems to have immense overhead. Set up a db, import data, etc. Although it makes sense, it's an afterthought. We know how to write scripts, but we don't necessarily need to learn SQL to get the job done.

nycdotnet 8y ago

I understand where the author is coming from, and for purposes of aggregation or filtering, they are correct; it’s often better to do those things in SQL. There are two problems, though. CPU and IOs on your DB server are likely your organization’s most scarce resource, and SQL is often hard to test and has unpredictable performance characteristics (cliffs, blocking, etc). By all means do the things you must do to get your app servers the data they need with the fewest IOs and bytes across the wire possible, but please do your string concatenation and sorting (when not using TOP or LIMIT) on cheap app servers that you can always deploy more of.

hunter23 8y ago

Be careful in using group_concat in mysql - it actually truncates the result to the max_len setting which is by default 1024 and will not tell you it truncates the result in the logs. it took us forever to find the cause of this issue in one of our batch jobs (I wish mysql issued a warning when truncating): "In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause. To sort values in the result, use the ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. The default separator between values in a group is comma (,). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''. The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer" https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.h...

geshan 8y ago
There is a setting to make it longer.
- hunter23 8y ago
  Yes this is mentioned in the documentation I quoted.
opless 8y ago
Then clearly mysql isn't the database you want. Perhaps MSSQL or postgresql or even (spit) Oracle to name a few. All massively more capable than the toydb that is mysql. I think the trouble with most developers is that their experience is mostly with this terrible DB.

lsiebert 8y ago

Some things are harder to do in SQL then in code. For example, assigning a rank based on a field in MYSQL. I did this for production code in a rails app, and it wasn't just ugly (requiring separate DB calls for variable initialization and output and updating) but it ran slow enough that it had to be scheduled for off peak time, and it requires temporary variables (or you can do it with whole temporary tables, I believe). https://stackoverflow.com/questions/3333665/rank-function-in-mysql https://stackoverflow.com/questions/3333665/rank-function-in... That's not to say that I would do it in Ruby if I had it to do over again, I'd probably use Redis, but still, sometimes SQL isn't as fast as other solutions. I like SQL. Optimizing DB calls to speed up stuff is fun for me. But it's not always the right choice. (There's probably a clever and fast way to do it in postgres, but DB choices were not mine to make). edit: Incidentally, if you just need to know 1 record's rank at a time, like getting a player's position on a leaderboard, instead of including each record in the DB, you can do a select on scores (or distinct scores) greater then the current record's score and add 1, and that can be much faster.