8 ms·

I've been taking this philosophy to heart the past few years. Working in the corporate world, I find users acceptance level to be in the tens of seconds/minutes

I've been taking this philosophy to heart the past few years. Working in the corporate world, I find users acceptance level to be in the tens of seconds/minutes/HOURS?! for queries and reports. Projects I've worked on have focused on being as minimal and lightning fast as I can possibly make them. If I add a new feature/query, and it's not instant, I don't ship it until it is. I don't know where or when this became norm, but often times the difference between hours and near instant, is a matter of a simple query optimization. Waiting more than a few seconds for something to run is infuriating to me.

the_duke 7y ago
The other side of the coin: * Why waste development time on something that is only executed once a month, or once a quarter, or even once a year? If you don't need to iterate on the reports, waiting a while for something that is almost never done is fine * Often simply adding a new index can reduce a query from hours to seconds. But why bloat your DB with an index and potentially reduce insert performance for something that's almost never needed?
- agumonkey 7y ago
  I think of users when queries are slow and async, it creates burden to their dept to manage the state of running operations. Technically that's not my business but I think about it anyway.
  - hinkley 7y ago
    But you have similar concerns with which you can empathize. Builds that take 5 minutes are a lower cognitive burden than builds that take 35, or two hours. If this is a worthwhile goal for your team, why shouldn't other teams want the same things?
- overcast 7y ago
  It wouldn't be a waste of time if it was just done right the first time. Generally these are queries running much more frequently than once a year, because that's how I get involved with them. Sure, there is always outlier consequences, but more often then not, these messes are the result of non technical people diving into the world of databases through excel and access.
  - maximente 7y ago
    "done right" is a nice phrase to toss around, but unless you're also looking at companion requirements then it's completely subjective. if it works but slowly, then it was almost certainly done correctly by my book. you've implied as such - otherwise, you'd be making it done correctly first and foremost. "done fast" more often than not involves non-measured details from parties often hovering outside the orbit of the customer, and you can bet trying to hit both targets leads to delays in shipping v1, which appears to work. i praise those who ship software that works correctly, understanding that requirements, deadlines, and project timelines are completely lost when the source code is read on its own.
    - overcast 7y ago
      I don't see how any of that makes sense in the context of database queries. Slow running queries are done correctly? Google has it all wrong then. I can't believe we're even arguing about the merits of low latency. If I can return the same data in 1/1000th of the time, what exactly is the issue?
      - adamc 7y ago
        This issue is that it takes time and effort by good people to get there. No one prevents you from writing fast queries at BigCo. Mostly they end up being slow because the person writing them doesn't know any better, and there is no motivation by BigCo. to push back and have the developer spend time learning optimizations. Nothing is free. Developers with more skills tend to cost more. BigCo. probably has some, but they are probably tasked with something else that BigCo. finds of greater value.
        reallydontask 7y ago
        There is also your own gained knowledge or a fresh outlook if you look at the problem again but as you said these things take time.
      - maximente 7y ago
- bartread 7y ago
  > But why bloat your DB with an index and potentially reduce insert performance for something that's almost never needed? Oh, for crying out loud: will you please stop trotting out this tired nonsense. This may have been a problem sometimes in the 80s and 90s. With any good modern RDBMS, say SQL Server 2017 (but you can pretty much pick any you like the look of[1]), and a half-competent developer and/or DBA, insert performance is almost never a problem when adding an index. You generally need some extra storage space for the index, but so what? I have seen, with surprising regularity over the years, tables with no indexes at all where adding a single clustered index (which is the table itself) can massively improve performance. In this scenario no additional storage is required. [1] I pick SQL Server because I know Microsoft started seriously addressing insert performance, which had been a problem with SQL Server 6.5 and 7, in SQL Server 2000. Nowadays, if you're half-competent, there really is no issue worth talking about. If you're not it is, of course, still possible to do crazy things that don't perform well. All truly powerful programming languages, database systems, et al., will cheerfully hand you the rope you need to hang yourself.
  - z3t4 7y ago
    Instead of carefully investigate which fields should have an index (all fields used for joins) the brute strategy is to index them all :P
    - rcarmo 7y ago
      Clustered indexes fix that perfectly.
      - mastry 7y ago
        How? You can only have one (on SQL Server anyway). Maybe I don't understand your point.
  - mastry 7y ago
    It depends on how much data you have in the table you're adding the index to and how much traffic you're dealing with. I deal with this every day (SQL 2017) and I assure you that adding an index to a large table has noticeable overhead in a heavily used system.
- azernik 7y ago
  Often, things are run infrequently because they are slow and inconvenient. Improving performance gives the user more freedom to design their workflow.
- bitcurious 7y ago
  The third side of the coin: * How many processes aren't executed more often because they are inconvenient? How much less efficient does this make the org? * How much is index fear driven by 20th century hardware cost? Anecdata: In a prior job, I worked at a nonprofit that partially shares a CRM codebase with another ~30 peers. I cut query runtime 10x with a few indexes. I cut query write-time similarly by writing a few denormalized SQL views. The cost on CRUD transactions was marginal. These queries drove mailings, marketing efforts, financial reporting, PowerBI dashboards, etc. Our peer organizations that used the out-of-the-box query tool favored SSRS reports for the same functionality, which meant that reporting was less flexible and more expensive to maintain. I can't prove causality but I believe query ease-of-use made a big difference.
  - tracker1 7y ago
    Likewise, I recently had a "discussion" with a colleague and their contention was that they had too many writes for an SSD to be reasonable (they don't)... However, still complaining about performance in their app. Every time you want 10x the users, you need to make compromises in design to reach that goal... 100 users is easy, 1000-10000 you can hardware your way out. More than that, your design better be decent.
    - flukus 7y ago
      So many performance problems I see with database writes are developers not understanding implicit transactions, they think transactions equate with more work and don't realize how much processing and disk writing can be saved by not having to update indexes on every insert. I've lost count of the number of times I've seen transactions reinvented with staging tables too. I suspect this contributes to a lot of the fear about indexes creating performance problems too. IME if you've got less than 100GB of data (maybe more on SSD) then correctly using indexes, joins and transactions will solve 90+% of performance problems and most things will be quite snappy without much tuning.
- Jtsummers 7y ago
  On the other other side: If a process takes a day to run, it won't be run daily. Period. It'll be run weekly or monthly or quarterly. But there may be value in running it more frequently, even continuously. But the present time cost makes that undesirable, and it's not perceived as adding value. Workarounds and alternatives are found, maybe. Or business units act on half-baked, likely out-of-date, information.
- ChrisCinelli 7y ago
  Too keep in mind: https://xkcd.com/1205/ https://xkcd.com/1205/
  - overcast 7y ago
    Now multiply all that saved time out by thousands of potential end users.
    - ShinTakuya 7y ago
      I disagree, this may sound a bit heartless, but it's the engineer's salary that should be measured here. The hard part is translating frustrated users into financial cost (e.g. lost revenue when users are leaving for competitors and such) which in turn can be translated into hourly wage which then can be used to calculate if optimisation is worth it. And then compare to the lost opportunity of making other optimisations or improvements. Obviously this is near impossible so the best you can do is make an educated guess.
      - tripzilch 7y ago
        Yes, if you are a money robot that communicates with money and can only reason about monetary amounts. Obviously you are, in fact, a meat robot, and if you'd care you could do better than "make an educated guess".
- FridgeSeal 7y ago
  Today my teammate and I debugged a service that was querying a database. Many queries were failing and clients had started to notice, because the quiet would take 45 seconds and API gateway would shut off the connection after 30 servings. We did a tiny bit of debugging, added an couple of indices, and now the quietly takes <1 second. It was 2 hours work, tops. We couldn’t understand why on earth the team who made this database hadn’t bothered to make any considerations for read queries, but I guess worrying about insert speeds are more important than basic usability.
  - ShinTakuya 7y ago
    It's dreadfully common to not understand database performance. Junior engineers I can understand, they're given a task and hack it together and don't understand why it's slow, but at least it works. But I've seen some pretty shitty work from "senior" engineers too. They know how to put a product together but they shy away from the database besides creating the table itself.
- dahfizz 7y ago
  It's so pretentious to assume that your "developer time" is so much more valuable than the time of your users. It's plain selfish. As you say, it's often as simple as adding an index, which have negligible downsides in 2019. It's a matter of caring about your craft and taking 5 extra minutes to do something right.
- lorenzhs 7y ago
  I attended a talk by Michael Bender last year on B^epsilon trees and how when users say they have a query speed problem, most of the time it's an insertion speed problem for just this reason. He founded a company with two other professors and they apparently achieved some impressive performance numbers with their database (TokuDB).
davesmith1983 7y ago
Because a business report typically doesn't need to be run quickly, it needs to be accurate.
- overcast 7y ago
  This type of attitude is what scares me.
  - davesmith1983 7y ago
    Your type of attitude scares me. Developers making up arbitrary requirements based on what they want has causes me many a headache.
    - overcast 7y ago
      Sorry, being fast is an arbitrary requirement? That's the entire premise of this article and discussion. The point being that most ARE NOT taking it into consideration and making it one.
      - davesmith1983 7y ago
        I was replying specifically to you wondering why what you consider to be poor performance became the norm. Generally as long as the feature is acceptably fast making it faster is likely a waste of time in most circumstances, especially when it comes to things like reports. Also it may come at the detriment of other considerations e.g cost, accuracy, code quality etc. I used to work on high performance JavaScript and typically the things done in the name of performance make the code a lot harder to read and modify, this pushes up development costs of a feature quite substantially. I haven't seen the stats myself but I doubt it brings in enough extra revenue to justify the cost. I am obviously not saying you shouldn't care about it, but it has to be taken with consideration of the bigger picture.
      - ilikehurdles 7y ago
        > Sorry, being fast is an arbitrary requirement? Yes. In all cases "being fast" is an arbitrary requirement, and a project lead or product manager should when hearing this requirement, interject and ask for a definition of "fast". There are a thousand things at any company that could be done faster than they are. I could throw in eager caching or reindex data into aggregated documents in order to optimize that once-monthly report that takes an hour to generate, or I could automate the other once-monthly report that a group of folks assembles manually for each client. Which one is more valuable to work on? I could rewrite the server in Rust to get a 10ms response time speed up or I could work on the feature that will allow our sales team to be finally able to say "Yes, we do that" to the folks who previously turned us down due to feature parity. I don't live in a world where time is free, or where I can work on all of these things at the same time and still be done in the time it takes to do just one of them. And all that additional logic and caching that could be added to improve the performance of some reports will be an increase of surface area for bugs and data discrepancies, and bring upon the team one of those hardest problems in computer science[1]. [1]:https://www.martinfowler.com/bliki/TwoHardThings.html https://www.martinfowler.com/bliki/TwoHardThings.html
UK-AL 7y ago
Some queries can only fast by essentially having a materialised view
- ben509 7y ago
  And MVs are generally the best way to separate support for infrequent reports or ETL jobs from support from common CRUD operations.
ChrisCinelli 7y ago
The evil is that overtime products add more and more features. When a feature is added and it slows down the app only 2%, they think it is no biggie. But overtime they ship 40 features in that way... and now the app take 220% more time. It just slowly happen overtime and that it is why they may not notice it. Products need to have performance tests with time limits that you do not want to compromise overtime.
- jstimpfle 7y ago
  I was confused about those "overtime products". But what you were saying is written "over time", or "as time progresses"
Lammy 7y ago
> I don't know where or when this became norm I assume people who aren't as driven in their careers wouldn't want to mess with something that gives them hour-long breaks "waiting" on queries. I know I wouldn't :)
- tripzilch 7y ago
  if you had shorter work weeks you would feel like you're wasting your time yourself, too.
- overcast 7y ago
  Well that's the thing right. All that freed up IO time, can now be used for other queries!

the_duke 7y ago

The other side of the coin: * Why waste development time on something that is only executed once a month, or once a quarter, or even once a year? If you don't need to iterate on the reports, waiting a while for something that is almost never done is fine * Often simply adding a new index can reduce a query from hours to seconds. But why bloat your DB with an index and potentially reduce insert performance for something that's almost never needed?

agumonkey 7y ago
I think of users when queries are slow and async, it creates burden to their dept to manage the state of running operations. Technically that's not my business but I think about it anyway.
- hinkley 7y ago
  But you have similar concerns with which you can empathize. Builds that take 5 minutes are a lower cognitive burden than builds that take 35, or two hours. If this is a worthwhile goal for your team, why shouldn't other teams want the same things?
overcast 7y ago
It wouldn't be a waste of time if it was just done right the first time. Generally these are queries running much more frequently than once a year, because that's how I get involved with them. Sure, there is always outlier consequences, but more often then not, these messes are the result of non technical people diving into the world of databases through excel and access.
- maximente 7y ago
  "done right" is a nice phrase to toss around, but unless you're also looking at companion requirements then it's completely subjective. if it works but slowly, then it was almost certainly done correctly by my book. you've implied as such - otherwise, you'd be making it done correctly first and foremost. "done fast" more often than not involves non-measured details from parties often hovering outside the orbit of the customer, and you can bet trying to hit both targets leads to delays in shipping v1, which appears to work. i praise those who ship software that works correctly, understanding that requirements, deadlines, and project timelines are completely lost when the source code is read on its own.
  - overcast 7y ago
    I don't see how any of that makes sense in the context of database queries. Slow running queries are done correctly? Google has it all wrong then. I can't believe we're even arguing about the merits of low latency. If I can return the same data in 1/1000th of the time, what exactly is the issue?
    - adamc 7y ago
      This issue is that it takes time and effort by good people to get there. No one prevents you from writing fast queries at BigCo. Mostly they end up being slow because the person writing them doesn't know any better, and there is no motivation by BigCo. to push back and have the developer spend time learning optimizations. Nothing is free. Developers with more skills tend to cost more. BigCo. probably has some, but they are probably tasked with something else that BigCo. finds of greater value.
      - reallydontask 7y ago
        There is also your own gained knowledge or a fresh outlook if you look at the problem again but as you said these things take time.
    - maximente 7y ago
bartread 7y ago
> But why bloat your DB with an index and potentially reduce insert performance for something that's almost never needed? Oh, for crying out loud: will you please stop trotting out this tired nonsense. This may have been a problem sometimes in the 80s and 90s. With any good modern RDBMS, say SQL Server 2017 (but you can pretty much pick any you like the look of[1]), and a half-competent developer and/or DBA, insert performance is almost never a problem when adding an index. You generally need some extra storage space for the index, but so what? I have seen, with surprising regularity over the years, tables with no indexes at all where adding a single clustered index (which is the table itself) can massively improve performance. In this scenario no additional storage is required. [1] I pick SQL Server because I know Microsoft started seriously addressing insert performance, which had been a problem with SQL Server 6.5 and 7, in SQL Server 2000. Nowadays, if you're half-competent, there really is no issue worth talking about. If you're not it is, of course, still possible to do crazy things that don't perform well. All truly powerful programming languages, database systems, et al., will cheerfully hand you the rope you need to hang yourself.
- z3t4 7y ago
  Instead of carefully investigate which fields should have an index (all fields used for joins) the brute strategy is to index them all :P
  - rcarmo 7y ago
    Clustered indexes fix that perfectly.
    - mastry 7y ago
      How? You can only have one (on SQL Server anyway). Maybe I don't understand your point.
- mastry 7y ago
  It depends on how much data you have in the table you're adding the index to and how much traffic you're dealing with. I deal with this every day (SQL 2017) and I assure you that adding an index to a large table has noticeable overhead in a heavily used system.
azernik 7y ago
Often, things are run infrequently because they are slow and inconvenient. Improving performance gives the user more freedom to design their workflow.
bitcurious 7y ago
The third side of the coin: * How many processes aren't executed more often because they are inconvenient? How much less efficient does this make the org? * How much is index fear driven by 20th century hardware cost? Anecdata: In a prior job, I worked at a nonprofit that partially shares a CRM codebase with another ~30 peers. I cut query runtime 10x with a few indexes. I cut query write-time similarly by writing a few denormalized SQL views. The cost on CRUD transactions was marginal. These queries drove mailings, marketing efforts, financial reporting, PowerBI dashboards, etc. Our peer organizations that used the out-of-the-box query tool favored SSRS reports for the same functionality, which meant that reporting was less flexible and more expensive to maintain. I can't prove causality but I believe query ease-of-use made a big difference.
- tracker1 7y ago
  Likewise, I recently had a "discussion" with a colleague and their contention was that they had too many writes for an SSD to be reasonable (they don't)... However, still complaining about performance in their app. Every time you want 10x the users, you need to make compromises in design to reach that goal... 100 users is easy, 1000-10000 you can hardware your way out. More than that, your design better be decent.
  - flukus 7y ago
    So many performance problems I see with database writes are developers not understanding implicit transactions, they think transactions equate with more work and don't realize how much processing and disk writing can be saved by not having to update indexes on every insert. I've lost count of the number of times I've seen transactions reinvented with staging tables too. I suspect this contributes to a lot of the fear about indexes creating performance problems too. IME if you've got less than 100GB of data (maybe more on SSD) then correctly using indexes, joins and transactions will solve 90+% of performance problems and most things will be quite snappy without much tuning.
Jtsummers 7y ago
On the other other side: If a process takes a day to run, it won't be run daily. Period. It'll be run weekly or monthly or quarterly. But there may be value in running it more frequently, even continuously. But the present time cost makes that undesirable, and it's not perceived as adding value. Workarounds and alternatives are found, maybe. Or business units act on half-baked, likely out-of-date, information.
ChrisCinelli 7y ago
Too keep in mind: https://xkcd.com/1205/ https://xkcd.com/1205/
- overcast 7y ago
  Now multiply all that saved time out by thousands of potential end users.
  - ShinTakuya 7y ago
    I disagree, this may sound a bit heartless, but it's the engineer's salary that should be measured here. The hard part is translating frustrated users into financial cost (e.g. lost revenue when users are leaving for competitors and such) which in turn can be translated into hourly wage which then can be used to calculate if optimisation is worth it. And then compare to the lost opportunity of making other optimisations or improvements. Obviously this is near impossible so the best you can do is make an educated guess.
    - tripzilch 7y ago
      Yes, if you are a money robot that communicates with money and can only reason about monetary amounts. Obviously you are, in fact, a meat robot, and if you'd care you could do better than "make an educated guess".
FridgeSeal 7y ago
Today my teammate and I debugged a service that was querying a database. Many queries were failing and clients had started to notice, because the quiet would take 45 seconds and API gateway would shut off the connection after 30 servings. We did a tiny bit of debugging, added an couple of indices, and now the quietly takes <1 second. It was 2 hours work, tops. We couldn’t understand why on earth the team who made this database hadn’t bothered to make any considerations for read queries, but I guess worrying about insert speeds are more important than basic usability.
- ShinTakuya 7y ago
  It's dreadfully common to not understand database performance. Junior engineers I can understand, they're given a task and hack it together and don't understand why it's slow, but at least it works. But I've seen some pretty shitty work from "senior" engineers too. They know how to put a product together but they shy away from the database besides creating the table itself.
dahfizz 7y ago
It's so pretentious to assume that your "developer time" is so much more valuable than the time of your users. It's plain selfish. As you say, it's often as simple as adding an index, which have negligible downsides in 2019. It's a matter of caring about your craft and taking 5 extra minutes to do something right.
lorenzhs 7y ago
I attended a talk by Michael Bender last year on B^epsilon trees and how when users say they have a query speed problem, most of the time it's an insertion speed problem for just this reason. He founded a company with two other professors and they apparently achieved some impressive performance numbers with their database (TokuDB).

davesmith1983 7y ago

Because a business report typically doesn't need to be run quickly, it needs to be accurate.

overcast 7y ago
This type of attitude is what scares me.
- davesmith1983 7y ago
  Your type of attitude scares me. Developers making up arbitrary requirements based on what they want has causes me many a headache.
  - overcast 7y ago
    Sorry, being fast is an arbitrary requirement? That's the entire premise of this article and discussion. The point being that most ARE NOT taking it into consideration and making it one.
    - davesmith1983 7y ago
      I was replying specifically to you wondering why what you consider to be poor performance became the norm. Generally as long as the feature is acceptably fast making it faster is likely a waste of time in most circumstances, especially when it comes to things like reports. Also it may come at the detriment of other considerations e.g cost, accuracy, code quality etc. I used to work on high performance JavaScript and typically the things done in the name of performance make the code a lot harder to read and modify, this pushes up development costs of a feature quite substantially. I haven't seen the stats myself but I doubt it brings in enough extra revenue to justify the cost. I am obviously not saying you shouldn't care about it, but it has to be taken with consideration of the bigger picture.
    - ilikehurdles 7y ago
      > Sorry, being fast is an arbitrary requirement? Yes. In all cases "being fast" is an arbitrary requirement, and a project lead or product manager should when hearing this requirement, interject and ask for a definition of "fast". There are a thousand things at any company that could be done faster than they are. I could throw in eager caching or reindex data into aggregated documents in order to optimize that once-monthly report that takes an hour to generate, or I could automate the other once-monthly report that a group of folks assembles manually for each client. Which one is more valuable to work on? I could rewrite the server in Rust to get a 10ms response time speed up or I could work on the feature that will allow our sales team to be finally able to say "Yes, we do that" to the folks who previously turned us down due to feature parity. I don't live in a world where time is free, or where I can work on all of these things at the same time and still be done in the time it takes to do just one of them. And all that additional logic and caching that could be added to improve the performance of some reports will be an increase of surface area for bugs and data discrepancies, and bring upon the team one of those hardest problems in computer science[1]. [1]:https://www.martinfowler.com/bliki/TwoHardThings.html https://www.martinfowler.com/bliki/TwoHardThings.html

UK-AL 7y ago

Some queries can only fast by essentially having a materialised view

ben509 7y ago
And MVs are generally the best way to separate support for infrequent reports or ETL jobs from support from common CRUD operations.

ChrisCinelli 7y ago

The evil is that overtime products add more and more features. When a feature is added and it slows down the app only 2%, they think it is no biggie. But overtime they ship 40 features in that way... and now the app take 220% more time. It just slowly happen overtime and that it is why they may not notice it. Products need to have performance tests with time limits that you do not want to compromise overtime.

jstimpfle 7y ago
I was confused about those "overtime products". But what you were saying is written "over time", or "as time progresses"

Lammy 7y ago

> I don't know where or when this became norm I assume people who aren't as driven in their careers wouldn't want to mess with something that gives them hour-long breaks "waiting" on queries. I know I wouldn't :)

tripzilch 7y ago
if you had shorter work weeks you would feel like you're wasting your time yourself, too.
overcast 7y ago
Well that's the thing right. All that freed up IO time, can now be used for other queries!