Needed code optimization

My introduction to SQL was circa 1995 when a large data warehousing project had managed to build a month-end data analysis routine that they discovered was going to take more than 31 days to run. They read somewhere that embedded SQL was faster & I was asked to rewrite it in C with embedded SQL. I worked with an SQL "specialist" & I did the C part, except he occasionally modified the C when I wasn't available.

After a few cycles I knew that every time I checked out his changes, I'd be up for rewriting the likes of "EXEC SQL Select 1 from sys.dual into :x" as "x=1;"

If I remember correctly, the program ended up about 9,000 lines of code, of which I wrote well over ⅔, but because of the language difficulties there were large chunks of almost, but not quite, duplicated code.

A small part of the performance boost I got was the C, the rest was things like hash tables with hand crafted hash routines to allow for very fast lookup of static data pre-loaded in memory and a custom memory allocator that let me allocate a then huge 40 megabyte chunk of memory then suballocate from that. The profiler kept telling me where to optimize next.

"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

Definitely not premature, but I only spent time on the bits that were slow. Quite frustrating that I'd speed up (or occasionally eliminate) the slowest bit, then I'd be told the next slowest, then ...

About 15 years later I needed to speed optimise another  program. This time in C++ and not one that was built from scratch. C++'s templates & standard library made it easier, as did a much more flexible profiler but the million plus line existing code-base meant I couldn't hold the whole thing in my head.  At least the earlier practice came in handy.