* 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?
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.
In an ideal world with a sanely (not over the top or inadequately) normalised database I'd agree with you. But in my experience there have been times where adding an index for a rarely run query was absolutely the wrong decision.
Of course, if you over-index a table, there can be some impact but in the general case that I've seen over the years that's seldom the problem.
* 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.
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.
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.
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.
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.
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.
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.
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.
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.
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'"?
The example you give is sooo right. And understanding the many levels of compromise between code speed, quality, legal implications, customer needs, management needs, cost, deadline, maintainability, technological choices made elsewhere, etc. is part of the job.
(personally, I have some priorities : meet the deadline comes first (descoping included), management at customer side comes next, then end users and, in the end code speed.
I'm gonna repost a chart I made previously[0]:
Spectrum of performance:
LO |---*-------*--------*------------*-------| HI
^ ^ ^ ^
| | | |_root of all evil if premature
| | |_you should be here
| |_you can be here if you don't do stupid things
|_you are here
--
If you've figured out that this is the case, the optimization is no longer premature.
For one, there are usually better arguments around code comprehension.
Pretty much the only time I hear this statement is as a response to criticism. That should raise some questions about the motivations of the speaker. Quite often it comes off as a dodge, and why and what are they dodging? Additionally, the whole quote is often at odds with the point the speaker is trying to make. It's not small inefficiencies, it's factors of 5, or 10, or an extra 6 months before we run out of headroom on something.
Aka the first refuge of the intellectually lazy.
(and the one who answers you has spent thousands of hours optimizing assembly routines for 3D engines, optimizing SQL queries to get the max out of some server, optimizing network traffic to optimize parallel computations,...)
If you're making a query that lots of users will run regularly, that needs an index. Period. Lots of the time there'll already be one, but that doesn't excuse you for not confirming and adding it if it's not there.
"Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%." ~Donald Knuth (1974)
Now, this won't work for any kind of industry. Right now i'm in the business/government stuff. There, prototyping is much more important than speed of code. When I was in the gaming industry, the lack of speed was most of the time a technical debt. But even then, having my code working was much more important to the overall team effort than my code being fast.
So instead of your chart, I prefer : 1/ Code is working, 2/ Code is correct 3/ check for other priorities 4/ optimize as needed.
As for what you're expected to do and company value, my experience is that not so many people understand the link between actual code and company value (esp. in the top management where I sit regularly). You'd be surprised to see how much a deadline is more important than a fully working/optimized program ('cos for example the deadline is a trigger for a enormous change in the organization you work for (although we know the lack of speed in the code will have negative impact on dozens of end users))
so, well, it depeends :-) but still, a word of caution sound right to me :-)
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?
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.
Obviously you are, in fact, a meat robot, and if you'd care you could do better than "make an educated guess".