4 ms·

Fave speedup story, in the 80s a good friend had written a custom accounting system in CBASIC and he came to me in regards to one client, a boat charter service

Fave speedup story, in the 80s a good friend had written a custom accounting system in CBASIC and he came to me in regards to one client, a boat charter service management business that maintained a set of 'virtual double-entry books' for every one of its 200+ member yachts. The monthly process was taking 4 hours to complete. Could I improve on that? Well in a couple hours I discovered that his routines assembling the main batch file did repeated lookups in other files without caching any operation results, and because it was MS-DOS the OS didn't cache many sectors either. So the hundreds-of-thousands of preparatory operations were waiting for the hard disk platter to come around again, for each. Yes, even hard disk platter speed was a significant factor in those days. So I added a 15 element array that cached lookup operations in memory. From 4 hours to 15 minutes, Thank You Very Much.

agumonkey 7y ago
May I contribute with an hybrid optimization. Some workers were tasked to remove duplicate in excel files by hand. They'd have to create filtered view for each entry, Excel being slow it took hours. Some woman had a 3000 lines spreadsheet and was about to lost it. I wrote a 8 lines VBA to count duplicates, just enough so she could filter anything that had count > 1 and then delete things on her own. Turning multi-day job into a two clicks operation. Probably my most happy lines of code in my entire life.
Twirrim 7y ago
My favourite speed up never got used :( Had a vendor we got partnered with for a government contract. The company I worked for usually did all its own development of java based web applications for the state, but on this one it was decided to partner with someone else as they already had a solution in the space. We'd just run it for them. It was terrible. The UI/UX was fine. It did actually solve the problem for the state, and was pretty intuitive. On the software side, though it just flat out did not scale for a number of reasons (including the same one OP had, they loved to dump everything in a single directory). My particular favourite reason was the way they chose to produce reports. They'd take the business requirement, sit there with a visual database tool, and piece together a SQL statement to produce the report, no matter how complicated that query got. Then they'd drop that in their code and voila, effortless reporting! There were queries in their code base that would end up nesting some 40+ selects deep, e.g. SELECT foo FROM bar WHERE id = (SELECT id FROM monkey WHERE id = (SELECT ... and so on down the line. The MySQL query optimiser, at the time, didn't handle those queries very well, and it could end up taking 45 minutes or so to produce the final report, and it was showing signs of potentially exponential execution time growth. This was around the time MariaDB was starting to take off, and it turned out this issue was one of the first they'd fixed in the optimiser (MySQL followed suit with a completely overhauled query optimiser they'd been working on for a while), and so I switched over to MariaDB instead of MySQL. Free speed boost! Now it only took 10 minutes to produce the report! Everyone loved me... but I still wasn't happy. The final reports didn't look that complicated to me. I picked up the most egregious example, and rewrote it in perl (the only language I wrote in at the time). Along the way I identified that if they wanted to keep it in a single query they could easily cut it down to something significantly simpler, but also if I just made it four or five queries and wrote some _very_ simple logic around it in perl, I could get the report out within seconds, while drastically reducing load on the database server. I gave the developers the proof of concept and simplified SQL query, but neither got used. In the end the company that produced the software was facing bankruptcy, they couldn't support what they'd sold the state, at the price they'd sold it for. They weren't ready for that scale, didn't know how to code to that scale, and didn't really have a grasp of the costs of supporting at that scale. For various reasons, my employer ended up taking over the code base from them, with legal agreements that we'd only use it for this purpose, never sell it anywhere else etc. etc. The code base was _awful_. I thought the things I could see (queries) were bad. The code base was worse. Thankfully that was someone else's problem to work on :D

agumonkey 7y ago

May I contribute with an hybrid optimization. Some workers were tasked to remove duplicate in excel files by hand. They'd have to create filtered view for each entry, Excel being slow it took hours. Some woman had a 3000 lines spreadsheet and was about to lost it. I wrote a 8 lines VBA to count duplicates, just enough so she could filter anything that had count > 1 and then delete things on her own. Turning multi-day job into a two clicks operation. Probably my most happy lines of code in my entire life.

Twirrim 7y ago

My favourite speed up never got used :( Had a vendor we got partnered with for a government contract. The company I worked for usually did all its own development of java based web applications for the state, but on this one it was decided to partner with someone else as they already had a solution in the space. We'd just run it for them. It was terrible. The UI/UX was fine. It did actually solve the problem for the state, and was pretty intuitive. On the software side, though it just flat out did not scale for a number of reasons (including the same one OP had, they loved to dump everything in a single directory). My particular favourite reason was the way they chose to produce reports. They'd take the business requirement, sit there with a visual database tool, and piece together a SQL statement to produce the report, no matter how complicated that query got. Then they'd drop that in their code and voila, effortless reporting! There were queries in their code base that would end up nesting some 40+ selects deep, e.g. SELECT foo FROM bar WHERE id = (SELECT id FROM monkey WHERE id = (SELECT ... and so on down the line. The MySQL query optimiser, at the time, didn't handle those queries very well, and it could end up taking 45 minutes or so to produce the final report, and it was showing signs of potentially exponential execution time growth. This was around the time MariaDB was starting to take off, and it turned out this issue was one of the first they'd fixed in the optimiser (MySQL followed suit with a completely overhauled query optimiser they'd been working on for a while), and so I switched over to MariaDB instead of MySQL. Free speed boost! Now it only took 10 minutes to produce the report! Everyone loved me... but I still wasn't happy. The final reports didn't look that complicated to me. I picked up the most egregious example, and rewrote it in perl (the only language I wrote in at the time). Along the way I identified that if they wanted to keep it in a single query they could easily cut it down to something significantly simpler, but also if I just made it four or five queries and wrote some _very_ simple logic around it in perl, I could get the report out within seconds, while drastically reducing load on the database server. I gave the developers the proof of concept and simplified SQL query, but neither got used. In the end the company that produced the software was facing bankruptcy, they couldn't support what they'd sold the state, at the price they'd sold it for. They weren't ready for that scale, didn't know how to code to that scale, and didn't really have a grasp of the costs of supporting at that scale. For various reasons, my employer ended up taking over the code base from them, with legal agreements that we'd only use it for this purpose, never sell it anywhere else etc. etc. The code base was _awful_. I thought the things I could see (queries) were bad. The code base was worse. Thankfully that was someone else's problem to work on :D