6 ms·

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

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
      you said you work in a corporate context, right? let's take an example that's not too far-fetched: legal comes and says that every month, you need to generate an report of some sort to comply with some regulation that corporations over a certain size must comply with. you sit down and run v1 of the software which works but takes 24 hours to run. is your first instinct to get infuriated, as you said, even though from a requirements/company perspective, this is completely "done right"? "the issue" is that changing software involves risks, which may be acceptable to you, but may not be acceptable to e.g. legal. they couldn't care less if it took 29 days to run or 29 ms. what they require - again, this is the requirement - is a monthly report generated, correctly. and yeah, 99 times out of 100 you change the SQL correctly and it runs in 1/1000 of the time the first time. then for whatever reason it messes up one month and legal asks "what the F was this guy doing mucking around with this software which worked 'right'"?
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).

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
    you said you work in a corporate context, right? let's take an example that's not too far-fetched: legal comes and says that every month, you need to generate an report of some sort to comply with some regulation that corporations over a certain size must comply with. you sit down and run v1 of the software which works but takes 24 hours to run. is your first instinct to get infuriated, as you said, even though from a requirements/company perspective, this is completely "done right"? "the issue" is that changing software involves risks, which may be acceptable to you, but may not be acceptable to e.g. legal. they couldn't care less if it took 29 days to run or 29 ms. what they require - again, this is the requirement - is a monthly report generated, correctly. and yeah, 99 times out of 100 you change the SQL correctly and it runs in 1/1000 of the time the first time. then for whatever reason it messes up one month and legal asks "what the F was this guy doing mucking around with this software which worked 'right'"?

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).