On dec 16-17 I will be teaching a new class at Enova – Database Optimization for Advanced Analytics. This class includes SQL basics: relational models, relational operation, join algorithms and data retrieval. and also short queries optimization and long queries optimization. I did teach short queries optimization multiple times for the past two years, so by now almost all examples I have are our native Enova examples. But I never tough the long queries at Enova.
Originally I’ve estimated it will take me about 4 hours to rework this portion of the class, but it turned out, it takes way longer! I’ve also realized, that I am expected to squeeze virtually everything I know into 4 hours! Which required rewriting of everything, even the parts I already tough at Enova!
Finding the right examples of long queries was (and is) rather difficult, since I didn’t really work with AA team yet. I had and old (and very good) example with run_balances view from Oracle Applications (back from the City of Chicago days), and by the end of the day today I’ve managed to construct a similar example using our local material.
I was not sure, whether I will be able to find a case, where Hash Join will be more efficient, than Nested Loops – our servers have so much memory, that Nested Loops often work “against all odds”:), but I finally found one!
We’ll see, how it will go next week. I am still far from being completely ready, and I will need to rehearse my timing, but I am more confident now, than in this morning 🙂