5 ms·

I spent a year in a role where 50% of my duties was writing sql reports. These reports where usually between 500 and 1000 lines of sql a pop. Sometimes the runt

by max76 8y ago

I spent a year in a role where 50% of my duties was writing sql reports. These reports where usually between 500 and 1000 lines of sql a pop. Sometimes the runtime of the report was measured in hours, so learning efficient sql was important. The company had a lot of people that had been writing sql for awhile, and there were lots of cool code snippets floating around. I learned a lot in that year. I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join. Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly. Every once in a while my sql skills save the day and several people in other departments contact me directly when they need excel files of data in our database we don't have UIs to pull yet.

justinclift 8y ago
Any recommendations of a place for sharing "extremely advanced" SQL skills? Asking from wanting to make use of such a place, and haven't seen anything like it. So, probably need to bootstrap one instead (etc).
- veritas3241 8y ago
  Are you asking for examples of advanced SQL skills? In my experience, if you can grok lateral joins (aka cross apply), recursive CTEs, window functions, and fully understand all the join types, that's a gold star for understanding SQL!
  - SamuelAdams 8y ago
    I would add indexes to that list. Knowing the different types and how they impact performance can be very valuable.
    - veritas3241 8y ago
      I didn't add indexes mainly b/c for analytic warehouses that are columnar, indexes are less important / meaningless. Partitions though, that's important!
- andy_boot 8y ago
  I made this: https://www.windowfunctions.com https://www.windowfunctions.com
- CRConrad 8y ago
  Are you asking for “sharing skills” as in something like StackOverflow, but for SQL? I would think there already is a Stack Exchange for SQL, possibly several (for different RDBMSes/ dialects); go have a look there, if this is what you meant.
dwd 8y ago
Knowing how to optimise SQL (and also database indexes) is a valuable skill. Reducing a highly used query's execution time by several orders of magnitude can be quite gratifying.
- faceplanted 8y ago
  Stick the --+ORDERED flag on a random select sometime and look at what happens to the estimated query cost to see how easy it would be to fuck this up if you had to make all the optimisation choices yourself.
Scaevolus 8y ago
Do you do much with Excel's SQL connectors? Sheets populates with database results are powerful and reasonably user friendly.
- hobs 8y ago
  A thing I find even more user friendly is Import-Excel https://github.com/dfinke/ImportExcel https://github.com/dfinke/ImportExcel It's a powershell module that allows you to easily dump things directly to excel files, does pretty decent datatables, multi-tabs, etc.
bas 8y ago
From a performance perspective, most ORMs are trash if used naively.
weka 8y ago
> I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join. I'd wager more don't even know what a join is.
chubot 8y ago
Once your SQL gets into 500-1000 lines, and hours of runtime, I would suggest using data frames instead (in R or Python). I wrote this post to introduce the idea: What Is a Data Frame? (In Python, R, and SQL) https://www.oilshell.org/blog/2018/11/30.html https://www.oilshell.org/blog/2018/11/30.html It's often useful to treat SQL as an extraction/filtering language, and then use R or Pandas as a computation/reporting language. I think of it as separating I/O and computation. SQL does enough filtering to cut the data down to a reasonable size, and maybe some preliminary logic. And then you compute on that smaller data set in R or Pandas -- iterating in SECONDS instead of hours. The code will likely be shorter as well, so it's a win-win (see examples in my blog post). I can't think of many situations where hours of runtime is "reasonable" for an SQL query. In 2 hours you could probably do a linear scan over every table in most production databases 10-100 times. For example, if your database is 10 GB, you could cat all of its files in less than 5 minutes (probably much less on a modern SSD). In 2 hours, you can do a five minute operation 24 times. I can't think of many reports that should take longer than 24 full passes over all the data in the database (i.e. pretending that you're not bothering to use indices in the most basic way). If it takes longer than that, the joins should be expressible with something orders of magnitude more efficient. I've mainly worked with big data frameworks, but I think that almost any SQL database (sqlite, MySQL, Postgres) should be able to do a scan of a single table with some trivial predicates within 10x the speed of 'cat' (should be within 2x really). They can probably do better on realistic workloads because of caching.
- hobs 8y ago
  Your advice is pretty good, but I would definitely say that SQL doesnt have a strong relationship between lines of code and runtime, you can line break (and many do) your wide table's select into many columns and get there pretty quick. If you are writing SQL regularly, understanding the basics of how the queries you write is not that hard for you engine of choice, and everyone should be required to understand the basics of reading an execution plan so they can find the right inflection points between data gathering and processing. I regularly sigh write and maintain SQL procedures that are >10k LOC, and their runtime never would exceed minutes, much less hours.
- busterarm 8y ago
DrPhish 8y ago
I had a similar role where I was writing boring LOB apps in a very gross language, but since we were using an SQL backend for all the data, I instead challenged myself to using bare templates in the actual programming language and writing all the extraction, transforms and logic into SQL selects and (when unavoidable) programmatic bits. I also learned a crapload about obscure SQL since I would go to extreme lengths to achieve this. There was a lot of meta-SQL programming, where I would use SQL to generate more SQL and execute that within my statement, sometimes multiple layers deep. It was beautiful in its own way, expanding out in intricate patterns.
- max76 8y ago
  I never personally wrote meta-SQL but it was used at that office and I read it. I didn't see beauty. When I read the code and understood it I felt terror.
- veritas3241 8y ago
  You might be interested in this project then https://www.getdbt.com/ https://www.getdbt.com/ it's a SQL compiler and executor which has many features like you're talking about (macros to generate SQL and so on). It makes it pretty easy to build up a complex DAG of SQL queries and transformations.
saint_abroad 8y ago
> Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly. ORM all-too-often defines data structures from code. Linus Torvalds wrote, > I will, in fact, claim that the difference between a bad programmer and a good one is whether he considers his code or his data structures more important. Bad programmers worry about the code. Good programmers worry about data structures and their relationships. https://lwn.net/Articles/193245/ https://lwn.net/Articles/193245/

by max76 8y ago

justinclift 8y ago

Any recommendations of a place for sharing "extremely advanced" SQL skills? Asking from wanting to make use of such a place, and haven't seen anything like it. So, probably need to bootstrap one instead (etc).

veritas3241 8y ago
Are you asking for examples of advanced SQL skills? In my experience, if you can grok lateral joins (aka cross apply), recursive CTEs, window functions, and fully understand all the join types, that's a gold star for understanding SQL!
- SamuelAdams 8y ago
  I would add indexes to that list. Knowing the different types and how they impact performance can be very valuable.
  - veritas3241 8y ago
    I didn't add indexes mainly b/c for analytic warehouses that are columnar, indexes are less important / meaningless. Partitions though, that's important!
andy_boot 8y ago
I made this: https://www.windowfunctions.com https://www.windowfunctions.com
CRConrad 8y ago
Are you asking for “sharing skills” as in something like StackOverflow, but for SQL? I would think there already is a Stack Exchange for SQL, possibly several (for different RDBMSes/ dialects); go have a look there, if this is what you meant.

dwd 8y ago

Knowing how to optimise SQL (and also database indexes) is a valuable skill. Reducing a highly used query's execution time by several orders of magnitude can be quite gratifying.

faceplanted 8y ago
Stick the --+ORDERED flag on a random select sometime and look at what happens to the estimated query cost to see how easy it would be to fuck this up if you had to make all the optimisation choices yourself.

Scaevolus 8y ago

Do you do much with Excel's SQL connectors? Sheets populates with database results are powerful and reasonably user friendly.

hobs 8y ago
A thing I find even more user friendly is Import-Excel https://github.com/dfinke/ImportExcel https://github.com/dfinke/ImportExcel It's a powershell module that allows you to easily dump things directly to excel files, does pretty decent datatables, multi-tabs, etc.

bas 8y ago

From a performance perspective, most ORMs are trash if used naively.

weka 8y ago

> I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join. I'd wager more don't even know what a join is.

chubot 8y ago

Once your SQL gets into 500-1000 lines, and hours of runtime, I would suggest using data frames instead (in R or Python). I wrote this post to introduce the idea: What Is a Data Frame? (In Python, R, and SQL) https://www.oilshell.org/blog/2018/11/30.html https://www.oilshell.org/blog/2018/11/30.html It's often useful to treat SQL as an extraction/filtering language, and then use R or Pandas as a computation/reporting language. I think of it as separating I/O and computation. SQL does enough filtering to cut the data down to a reasonable size, and maybe some preliminary logic. And then you compute on that smaller data set in R or Pandas -- iterating in SECONDS instead of hours. The code will likely be shorter as well, so it's a win-win (see examples in my blog post). I can't think of many situations where hours of runtime is "reasonable" for an SQL query. In 2 hours you could probably do a linear scan over every table in most production databases 10-100 times. For example, if your database is 10 GB, you could cat all of its files in less than 5 minutes (probably much less on a modern SSD). In 2 hours, you can do a five minute operation 24 times. I can't think of many reports that should take longer than 24 full passes over all the data in the database (i.e. pretending that you're not bothering to use indices in the most basic way). If it takes longer than that, the joins should be expressible with something orders of magnitude more efficient. I've mainly worked with big data frameworks, but I think that almost any SQL database (sqlite, MySQL, Postgres) should be able to do a scan of a single table with some trivial predicates within 10x the speed of 'cat' (should be within 2x really). They can probably do better on realistic workloads because of caching.

hobs 8y ago
Your advice is pretty good, but I would definitely say that SQL doesnt have a strong relationship between lines of code and runtime, you can line break (and many do) your wide table's select into many columns and get there pretty quick. If you are writing SQL regularly, understanding the basics of how the queries you write is not that hard for you engine of choice, and everyone should be required to understand the basics of reading an execution plan so they can find the right inflection points between data gathering and processing. I regularly sigh write and maintain SQL procedures that are >10k LOC, and their runtime never would exceed minutes, much less hours.
busterarm 8y ago

DrPhish 8y ago

I had a similar role where I was writing boring LOB apps in a very gross language, but since we were using an SQL backend for all the data, I instead challenged myself to using bare templates in the actual programming language and writing all the extraction, transforms and logic into SQL selects and (when unavoidable) programmatic bits. I also learned a crapload about obscure SQL since I would go to extreme lengths to achieve this. There was a lot of meta-SQL programming, where I would use SQL to generate more SQL and execute that within my statement, sometimes multiple layers deep. It was beautiful in its own way, expanding out in intricate patterns.

max76 8y ago
I never personally wrote meta-SQL but it was used at that office and I read it. I didn't see beauty. When I read the code and understood it I felt terror.
veritas3241 8y ago
You might be interested in this project then https://www.getdbt.com/ https://www.getdbt.com/ it's a SQL compiler and executor which has many features like you're talking about (macros to generate SQL and so on). It makes it pretty easy to build up a complex DAG of SQL queries and transformations.

saint_abroad 8y ago

> Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly. ORM all-too-often defines data structures from code. Linus Torvalds wrote, > I will, in fact, claim that the difference between a bad programmer and a good one is whether he considers his code or his data structures more important. Bad programmers worry about the code. Good programmers worry about data structures and their relationships. https://lwn.net/Articles/193245/ https://lwn.net/Articles/193245/