6 ms·

Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If yo

Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know you'll throw out NA's on certain variables, if you need a certain date or age range, or even the way you format these filters: these all lead to important savings. And this saves R from being full on memory, which would often happen if you fed it way too much data. Within a system of 1400 analysts, it makes a big difference if everyone's taking 2X or 4X the time pulling that they could be. Then, even the efficiently written pulls get slowed down, so you have to run things overnight...and what if you had an error on that overnight pull? Suffice to say, it'd have been much simpler if people wrote solid SQL from the start.

solaxun 5y ago
I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?
- protomyth 5y ago
  Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.
  - deleted 5y ago
    [deleted]
  - alextheparrot 5y ago
    This is where I think the original data admins were deluding themselves. Expecting 1,400 analysts to write better code is a really non-trivial problem, but easy to proclaim. An actual solution is creating pre-joined tables and having processes ("Hey your query took forever, have you considered using X?") or connectors (".getPrejoinedPatientTable()") that make sure those tables are being used in practice.
  - tomrod 5y ago
    > why isn't someone rolling those tables into something a bit easier with some backend process. You'd identified why we're all going to have jobs in 100 years. Automation sounds great. It's exponentially augmenting to some users in a defined user space. Until you get to someone like me, who looks at the production structure and goes: "this is wholly insufficient for what I need to build, but it has good bones, so I'm going to strip it out and rebuild it for my use case, and only my use case, because to wait for a team to prioritize it according to an arcane schedule will push my team deadlines exceedingly far." This is why you don't roll everything into backend processes. Companies set up for production (high automation value ROI) and analytics (high labor value ROI) and has a hard time serving the mid tail. EVERYTHING on either direction works against the mid-tail -- security policies, data access policies, software approvals, you name it. People, policy, and technology. These are the three pillars. If your org isn't performing the way it should, then by golly work at one of these and remember that technology is only one of three.
    - punnerud 5y ago
      Tree pillars where you only can choose two to be perfect. Just like databases and CAP theorem
      - tomrod 5y ago
        Technology is the easiest to adjust, ironically.
  - steve-chavez 5y ago
    > why isn't someone rolling those tables into something a bit easier with some backend process. To put it in more concrete terms(plain SQL): the tables could be aggregated on a set returning function or a view.
    - cgh 5y ago
      Yes, in these situations materialized views with indexes are generally the correct answer.
  - CRConrad 5y ago
    May I introduce: Protomyth, meet DW and ETL.
- tharkun__ 5y ago
  Disclaimer: It's been a while, but my very first job out of uni was dealing with large queries like that for reporting and import/export out of our OLTP database directly. 15 tables would've been not very common for us, but something between 5 and 10 was normal. Many of those tables would've had millions upon millions of rows (while some were simple 'key tables' with only hundreds or a few thousand records) The one thing that I learned really fast is that yes, "prefiltering/subgrouping" as the OP calls it, is very important. If you cut down on the number of rows that your query "starts out with" was very very important, as it cuts down on the amount of data that needs to be dealt with in the rest of the query. This was for an old Sybase ASE based system. IIRC, ASE would only be able to automatically optimize this across 4 clauses (might misremember the number and I left before they upgraded to the new newer version with a better optimizer), so ordering of your join clauses was important. If the filtering that cut down on the amount of data needed from other tables came first, your query would run way faster, than if the filters were way down with the rest of the joins. Just think about it, if you start out with getting 6 million patient records and then start collecting 6 million records from the next table for a join and so on and so forth, that's way more data that needs to be read and churned through than if you can 'start on the other end' so to speak, whittle it down to say 50000 records that now need to be looked up in the patient table.
protomyth 5y ago
I don't disagree with writing solid SQL. I would go so far as to say some things (most) need to be in stored procedures that are reviewed by competent people. But, some folks don't think about usage sometimes. This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have some basic distinct scenarios and to plan for them. The most common is adding or querying data about a single entity. Most application developers really only deal with this scenario since that is what most applications care about and how you get your transactional data. Basic database knowledge gets most people to do this ok with proper primary and secondary keys. Next up is a simple rule, "if you put a state on an entity, expect someone to need to know all the entities with this state." This is a killer for application developers for some reason. It actually requires some database knowledge to setup correctly to be performant. If the data analysts have problems with those queries, then its to to get the DBA to fix the damn schema and write some stored procedures for the app team. At some point, you will need to do actual reporting, excuse me, business intelligence. You really should have some process that takes the transactional data and puts it into a form where the queries of data analysts can take place. In the old days that would be something to load up Red Brick or some equivalent. Transactional systems make horrid reporting systems. Running those types of queries on the same database as the transactional system is currently trying to work is just a bad idea. Of course, if you are buying something like IBM DB2 EEE spreading queries against a room of 40 POWER servers, then ignore the above. IBM will fix it for you.
- jnsie 5y ago
  At its simplest its OLTP vs OLAP. Separate the data entry/transactional side of things from the reporting part. Make it efficient for data analysts do do their jobs.
  - derefr 5y ago
    But sometimes, your line-of-business application does analytics queries. Which means you need your app developers to understand how to do OLAP, and you also need a schema design that can run arbtrary OLAP queries within a few orders of magnitude of OLTP speeds (e.g. <10s.)
fifilura 5y ago
I can't help thinking that with better processes and tools those 1400 analysts could instead be 50 analysts? (or even 5?) And that building an aggregation ETL pipeline, maybe inspired by this post, could be the solution?
- 1980phipsi 5y ago
  VA -> government -> bloat
- ivalm 5y ago
  VA is a massive org, this is not a particularly large number of analysts for a healthcare system of this size (or honestly any complex org).
  - fifilura 5y ago
    Yes, but the answer that it is a massive organization does not satisfy my curiosity regarding what they are actually doing. I come from a small country, in total comparable to the 6 million veterans mentioned here. 1400 analysts is just a lot and I wouldn't imagine for example our national healthcare service could ever employ that amount of analysts? Hm, or would they? It would make a pretty big dent in the total amount of persons in that field in our country.
    - WhompingWindows 5y ago
      6 million Veterans are just the subgroup from one of the studies I did. In reality, the VA system serves 15-20 million patients, given there are 17.4 million Vets, some who use private health care, and some whose families also use VA. The reason there are 1400 analysts: research studies each require one or two analysts. At this very moment, there are thousands of research studies taking place in the US medical system. Without these number of analysts, you'd have to completely revamp the system, killing all current projects, all current code, and creating a HUGE HUGE headache for everyone, not to mention laying off 1000+ through a system which it is NOT easy to layoff individuals through. As a matter of fact, they want to transition to a new data infrastructure at the VA, but it's been delayed many times and the logistics have been very vague.
sagarm 5y ago
How many TBs of data are we really talking here, if it's just 6M rows? Surely this processing could easily be done on a single machine.
- sztanko 5y ago
  It doesn't say it is just 6m rows. It is 6m patient, which only hints that one of the dimension tables is 6m. Facts gathered in patients might be significantly larger. Also, my experience is saying, if you have hundreds of queries running simultaneously, it is not the volume of data that can be a bottleneck. Depending on the system it can be anything, starting from acquiring lock on a record or initiating a transaction.
  - sagarm 5y ago
    True, the number of events / records is probably significantly larger than 6M. I still have a hard time believing any reasonably modern datawarehouse would struggle with queries on this dataset. If you've got 1400 analysts, I hope you've exported your data from an OLTP database to an OLAP database. Those generally are much easier to scale to many users.
- ivalm 5y ago
  I work for a large healthcare org and from personal experience these things can get large. 6m patients is prob 20m encounters, each encounter may have 10 different kinds of meta data (each one it’s own table) and each kind has 10-100 rows. So really you are doing joins between 10 tables each with ~1-10 billion rows. It actually does slow down even running on expensive hardware.
  - sim_singh 5y ago
    Hi, I saw your comment on the wechat page, I was wondering if you have the account and can verify me?
  - sagarm 5y ago
    Ah yes that does sound painful. I'd generally recommend that data be denormalized in the warehouse so the joins don't need to be done at query time. That can make the queries more complex, however.

solaxun 5y ago

I understand your point generally but I don't understand this example. If you need data from 15 tables, you need to do those joins, regardless of prefiltering or subgrouping, right?

protomyth 5y ago
Well, yes but. Why do you need 15 tables for analysis queries and why isn't someone rolling those tables into something a bit easier with some backend process.
- deleted 5y ago
  [deleted]
- alextheparrot 5y ago
  This is where I think the original data admins were deluding themselves. Expecting 1,400 analysts to write better code is a really non-trivial problem, but easy to proclaim. An actual solution is creating pre-joined tables and having processes ("Hey your query took forever, have you considered using X?") or connectors (".getPrejoinedPatientTable()") that make sure those tables are being used in practice.
- tomrod 5y ago
  > why isn't someone rolling those tables into something a bit easier with some backend process. You'd identified why we're all going to have jobs in 100 years. Automation sounds great. It's exponentially augmenting to some users in a defined user space. Until you get to someone like me, who looks at the production structure and goes: "this is wholly insufficient for what I need to build, but it has good bones, so I'm going to strip it out and rebuild it for my use case, and only my use case, because to wait for a team to prioritize it according to an arcane schedule will push my team deadlines exceedingly far." This is why you don't roll everything into backend processes. Companies set up for production (high automation value ROI) and analytics (high labor value ROI) and has a hard time serving the mid tail. EVERYTHING on either direction works against the mid-tail -- security policies, data access policies, software approvals, you name it. People, policy, and technology. These are the three pillars. If your org isn't performing the way it should, then by golly work at one of these and remember that technology is only one of three.
  - punnerud 5y ago
    Tree pillars where you only can choose two to be perfect. Just like databases and CAP theorem
    - tomrod 5y ago
      Technology is the easiest to adjust, ironically.
- steve-chavez 5y ago
  > why isn't someone rolling those tables into something a bit easier with some backend process. To put it in more concrete terms(plain SQL): the tables could be aggregated on a set returning function or a view.
  - cgh 5y ago
    Yes, in these situations materialized views with indexes are generally the correct answer.
- CRConrad 5y ago
  May I introduce: Protomyth, meet DW and ETL.
tharkun__ 5y ago
Disclaimer: It's been a while, but my very first job out of uni was dealing with large queries like that for reporting and import/export out of our OLTP database directly. 15 tables would've been not very common for us, but something between 5 and 10 was normal. Many of those tables would've had millions upon millions of rows (while some were simple 'key tables' with only hundreds or a few thousand records) The one thing that I learned really fast is that yes, "prefiltering/subgrouping" as the OP calls it, is very important. If you cut down on the number of rows that your query "starts out with" was very very important, as it cuts down on the amount of data that needs to be dealt with in the rest of the query. This was for an old Sybase ASE based system. IIRC, ASE would only be able to automatically optimize this across 4 clauses (might misremember the number and I left before they upgraded to the new newer version with a better optimizer), so ordering of your join clauses was important. If the filtering that cut down on the amount of data needed from other tables came first, your query would run way faster, than if the filters were way down with the rest of the joins. Just think about it, if you start out with getting 6 million patient records and then start collecting 6 million records from the next table for a join and so on and so forth, that's way more data that needs to be read and churned through than if you can 'start on the other end' so to speak, whittle it down to say 50000 records that now need to be looked up in the patient table.

protomyth 5y ago

I don't disagree with writing solid SQL. I would go so far as to say some things (most) need to be in stored procedures that are reviewed by competent people. But, some folks don't think about usage sometimes. This is one of those things I just don't get about folks setting up their databases. If you have a rather large dataset that keeps building via daily transactions, then its time to recognize you really have some basic distinct scenarios and to plan for them. The most common is adding or querying data about a single entity. Most application developers really only deal with this scenario since that is what most applications care about and how you get your transactional data. Basic database knowledge gets most people to do this ok with proper primary and secondary keys. Next up is a simple rule, "if you put a state on an entity, expect someone to need to know all the entities with this state." This is a killer for application developers for some reason. It actually requires some database knowledge to setup correctly to be performant. If the data analysts have problems with those queries, then its to to get the DBA to fix the damn schema and write some stored procedures for the app team. At some point, you will need to do actual reporting, excuse me, business intelligence. You really should have some process that takes the transactional data and puts it into a form where the queries of data analysts can take place. In the old days that would be something to load up Red Brick or some equivalent. Transactional systems make horrid reporting systems. Running those types of queries on the same database as the transactional system is currently trying to work is just a bad idea. Of course, if you are buying something like IBM DB2 EEE spreading queries against a room of 40 POWER servers, then ignore the above. IBM will fix it for you.

jnsie 5y ago
At its simplest its OLTP vs OLAP. Separate the data entry/transactional side of things from the reporting part. Make it efficient for data analysts do do their jobs.
- derefr 5y ago
  But sometimes, your line-of-business application does analytics queries. Which means you need your app developers to understand how to do OLAP, and you also need a schema design that can run arbtrary OLAP queries within a few orders of magnitude of OLTP speeds (e.g. <10s.)

fifilura 5y ago

I can't help thinking that with better processes and tools those 1400 analysts could instead be 50 analysts? (or even 5?) And that building an aggregation ETL pipeline, maybe inspired by this post, could be the solution?

1980phipsi 5y ago
VA -> government -> bloat
ivalm 5y ago
VA is a massive org, this is not a particularly large number of analysts for a healthcare system of this size (or honestly any complex org).
- fifilura 5y ago
  Yes, but the answer that it is a massive organization does not satisfy my curiosity regarding what they are actually doing. I come from a small country, in total comparable to the 6 million veterans mentioned here. 1400 analysts is just a lot and I wouldn't imagine for example our national healthcare service could ever employ that amount of analysts? Hm, or would they? It would make a pretty big dent in the total amount of persons in that field in our country.
  - WhompingWindows 5y ago
    6 million Veterans are just the subgroup from one of the studies I did. In reality, the VA system serves 15-20 million patients, given there are 17.4 million Vets, some who use private health care, and some whose families also use VA. The reason there are 1400 analysts: research studies each require one or two analysts. At this very moment, there are thousands of research studies taking place in the US medical system. Without these number of analysts, you'd have to completely revamp the system, killing all current projects, all current code, and creating a HUGE HUGE headache for everyone, not to mention laying off 1000+ through a system which it is NOT easy to layoff individuals through. As a matter of fact, they want to transition to a new data infrastructure at the VA, but it's been delayed many times and the logistics have been very vague.

sagarm 5y ago

How many TBs of data are we really talking here, if it's just 6M rows? Surely this processing could easily be done on a single machine.

sztanko 5y ago
It doesn't say it is just 6m rows. It is 6m patient, which only hints that one of the dimension tables is 6m. Facts gathered in patients might be significantly larger. Also, my experience is saying, if you have hundreds of queries running simultaneously, it is not the volume of data that can be a bottleneck. Depending on the system it can be anything, starting from acquiring lock on a record or initiating a transaction.
- sagarm 5y ago
  True, the number of events / records is probably significantly larger than 6M. I still have a hard time believing any reasonably modern datawarehouse would struggle with queries on this dataset. If you've got 1400 analysts, I hope you've exported your data from an OLTP database to an OLAP database. Those generally are much easier to scale to many users.
ivalm 5y ago
I work for a large healthcare org and from personal experience these things can get large. 6m patients is prob 20m encounters, each encounter may have 10 different kinds of meta data (each one it’s own table) and each kind has 10-100 rows. So really you are doing joins between 10 tables each with ~1-10 billion rows. It actually does slow down even running on expensive hardware.
- sim_singh 5y ago
  Hi, I saw your comment on the wechat page, I was wondering if you have the account and can verify me?
- sagarm 5y ago
  Ah yes that does sound painful. I'd generally recommend that data be denormalized in the warehouse so the joins don't need to be done at query time. That can make the queries more complex, however.