Josh Vogel

TWIL – This Week I Learned – SQL Optimization, Versioning and HTML Nuances

|

Week of July 10, 2022

A (hopefully) weekly column where I keep you updated on some of the topics I learned about during the week.

SQL Optimization

Grr

Last week, a query that I wrote was given and then modified on my own went into production. Then everything started going haywire. CPU usage went up to 100%, systems slowed down. Despite testing, the query needed to be optimized, and big time. The issue was that those who were skilled at optimizing, didn’t know the table schema the query was based on, and the person (me, but not really) who knew the schema, didn’t really know optimization. Time for the self-didact to didactitate (?)! I hit the books, and along with guidance from the professional optimizer in my company, focused on something called Common Table Expressions, which according to the top hit on Google is:

A Common Table Expression (CTE) is the result set of a query which exists temporarily and for use only within the context of a larger query. Much like a derived table, the result of a CTE is not stored and exists only for the duration of the query.

chart.io.com

Many professions (medicine for example) are known to be part science, part art. Coding too also borrows from both areas. SQL Optimization though definitely falls more into the art category. This is because as those experienced in SQL know, there are a million ways to write a query, and so too each time you go and estimate your execution plan, are the ways it can be executed. Finding a fine balance between readability, execution time, and load, and a million other factors makes composing SQL quickly leave the realm of science and join the world of art. Maybe that’s why we compose SQL, it is like managing a symphony!

The Kennedy Center GIF - Find & Share on GIPHY

Versioning

This one is quick. Lost a bit of time this week working on projects because I was starting again on something old and all the versions had changed. Might be time to consider something like Docker or even just a VENV that can make this go quicker. Note to look into that.

HTML Nuances

I am running a small computer camp for my son and his friends in a few weeks, so I have been working hard on putting together the material for that. The goal (aside from teaching them that the monitor is NOT the computer) is to also teach them proper coding techniques before they can learn bad habits. So while looking into how to write HTML, I was curious if values for attributes officially need single or double quotes. The answer (a rare one in the world of coding) is that you can use both. Go figure.

Beyond!

  • Signed on a new client who needs help with a site written in ASP.NET. It’s my first time working with this codebase, but basically, it works the same as everything else. Needs its own set of packages, IDE, etc. Look here for more updates on how this progresses. Also, it is silly easy to get IIS running on your local machine. If Windows weren’t so bulky (and costly) it could have had something 20 years ago. Oh well.
  • Bought a mopping robot this week, I have it run every night. Haven’t touched a broom all week!
  • Famous YouTubers I had never heard of: Mr. Beast, Ace Family. This is an ecosystem that is way larger than anyone can imagine (note for blog post); it is so large there are even YouTube channels (with millions of subscribers of their own) that track gossip about YouTube personalities.