Database query optimization is a useful skill

Recently I read an article titled “Quick tip: Improve developer habits by showing time cost of DB queries” by Dan Birken.  He claims that one common reason why websites load slowly are because of unoptimized database queries:

Websites can be slow for many reasons, but an overwhelmingly common one is time spent performing database queries. Now database queries are likely a very important part of rendering the webpage – the information to populate a page has to come from somewhere. But sometimes rendering a page is slowed down by either unnecessary queries or improperly optimized queries.

I completely agree with his view.  For most web developers, querying information from a database is quite a common action.  Therefore, I believe learning how to write efficient and optimized database queries is a good skill to have.

I was fortunate to learn about this topic when I was developing MyBB way back years ago.  At the bottom of each MyBB page, administrators are shown debugging info with a summary of loading times and other statistics.

Debug info

Debugging information presented to the administrator on each MyBB page

Clicking on the “advanced details” link yields a much more in-depth analysis of the page generation, including an explanation of all the database queries used to create the page.

Performance debugging details

Advanced performance debugging details presented to MyBB administrators.

This is very useful to determine where the bottlenecks occur when generating the page.  Some common mistakes include repeating similar queries instead of batching them together, selecting more data than is needed, and doing full table scans rather than indexed queries.

However, not all software and web frameworks has built-in performance debugging pages like what MyBB has.  If you’re programming something from scratch, it might take some extra effort to create a useful debugging interface.  What other tools can we use then?

Using the EXPLAIN query (works at least for MySQL, PostgreSQL, SQLite) will give you the details about how a specific query will be executed by the database.  All you need to do is to tack “EXPLAIN” on at the beginning of your SELECT query, and run it through your favourite database management program.  SitePoint has a thorough article on how to understand the output of an EXPLAIN query.

Of course, it’s also beneficial to have a concrete understanding of database indexes and how they work.  One of my favourite sites on this topic is Use the Index, Luke!  They also have a fun 3-minute quiz to test your knowledge, if you think you already know everything ;)  (I got 4/5 on my first try, how about you? :) )

Do you know of any other resources to help with optimizing database queries?  Let me know in the comments.

The hello world hackathon project

This past week, the co-op students at A Thinking Ape participated in an internal hackathon where they had two days to develop something to show the rest of the company.

Among the games and tools that resulted, one project stood out to me: a hello world app.  Yes, a hello world app. (For those not in the software development field, “hello world” is usually the first output that developers code when trying out a new platform or language.)

It had a white background, black text that said “Hello World,” and a green button that was labeled, “I am a button.”  It was built using Microsoft’s latest platform that supports writing universal Windows apps that can be run on Windows phones, tablets, and desktop computers.

What stood out to me was the way he presented this simple app.  He described the app not as any other hello world app, but one with a button and that it was green!  As he went on, he told us that the green button was also “self aware,” because it was labeled describing itself as a button.

Then he went on to clicking the button.  The text that appeared below said, “You clicked the button, ” or something to that effect.  He described this like predicting the future, but in reverse!

He finally proceeded to show us that how it looked on a phone simulator.

By the end of it, many of us were quite entertained by the way this seemingly simple and ordinary project was described.  This presentation reminded me of the  “reality distortion field” Steve Jobs had when he presented his keynotes (here’s one when Steve Jobs revealed Safari for Windows in 2007).

This example is to show that no matter what kind of product you have, the way you present it to people and the way you market it has a great effect on what the audience will feel and remember about the product.

Happy April Fool’s Day

Today I pranked people who surf my T-Comm site every day looking for “special sightings” of buses that are assigned to routes which they normally aren’t assigned.  I swapped buses around such as putting articulated (long) buses on regular routes, changing the types of buses on particular routes, etc.  It turns out that what caught more attention was the fact that my ‘backup’ buses in the D40LF and LFS range were being randomly assigned as cover for buses that were already swapped, rather than the actual swaps that I had intended.

Here are some screenshots of some of the swapped buses:

Some technical detail went into planning this since it was critical to also keep a copy of the actual bus assignments so that it could be replaced after April Fools.  I came up of a short list of routes and buses to swap that wouldn’t completely break the rest of the system or make it completely obvious that the data was faked.  Then I created a separate copy of T-Comm on Sunday night and took a couple of hours to code the swapping modification.  Monday was the test day, which turned out to be very useful because there were a couple glaring bugs.  Then overnight I swapped the two T-Comms and went to sleep.  By the time I woke up, I already had messages of confusion in my inbox :)  Was it worth the effort?  Yeah I think so.  Lesson behind this?  There’s nothing like transit-fanning the traditional way of sighting buses in person.

My MacBook Pro

A real MacBook Pro

I bought my MacBook Pro back in 2009.  It was a Mid-2009 (2nd generation) version with a Core 2 Duo with the basic 2GB of memory and 250GB hard disk drive.  I chose Mac because of many reasons; here are some of them, ordered by what I thought most important first:

  1. Solid construction:  The unibody construction was a huge factor.  The size was quite slim and easily portable.  The aluminum exterior felt solid.  Since getting the laptop, I’ve only dropped it once.  The hard drive died as a result (expected); was not a big deal to replace it.
  2. Battery Life:  The battery life exceeded the average of other laptops of comparable performance and price.  I didn’t end up using the advertised 7 hours most of the time but 3-5 hours was good enough for me.
  3. Compatibility with Unix/Linux:  The Mac operating system is based on Unix.  As a computer science student, being able to easily compile and run *nix programs, navigate around in the Terminal, and connect to remote *nix servers was a definite plus.
  4. Compatibility with Windows:  This doesn’t seem to be well known, but Mac easily allows you dual-boot into Windows with its Bootcamp software to run any Windows programs natively.  I also used VirtualBox to setup a virtual machine running Windows for the programs that don’t need native performance or external inputs.
  5. Plug and play with projectors/monitors: From using an external monitor at home, to plugging into monitors and projectors at school and places I volunteer, it had to be good to go without much hassle.  For the most part, Mac OS X delivered this although I still prefer the more detailed options available back with Snow Leopard and Lion.
My MacBook Pro

My MacBook Pro, now running OS X Mavericks

The MacBook Pro is an awesome work horse, able to do pretty much do everything I threw at it: homework, programming various things, projecting, editing photos and videos.  It has been my primary computer for the past 4.5 years, and having replaced the battery last year I think it could probably last for another couple of years.  I’ve slowly upgraded the hardware to max out 8GB of memory and settled with a Seagate 750GB Momentus XT Solid State Hybrid Drive, which fit the bill of having a large storage space while having slightly better performance with the flash cache.

I had hoped that Apple would be producing this line of MacBook Pros for a bit longer so that when my current one dies, I’d be able to replace it with another.  However, after surfing the Apple Store recently, I realized that my presumption may not hold true much longer.  There’s only one model of the 2nd generation MacBook Pro left and it hasn’t been updated since 2012.  The rest of the MacBook Pro lineup consists of retina display models.

MacBook Pro listing on the Apple Store

Apple Store with the 13″ MacBook Pro being the last 2nd generation MacBook Pro model still standing.

MacBook “Pro” with Retina Display

I wouldn’t really call the new MacBooks “Pro”.  In my opinion, the current Retina MacBook Pros should just be called “MacBook with Retina Display”.

  1. No network port: How am I supposed to setup a router or debug network issues if I have to have Wi-Fi first?  Also, for transfers, a cabled connection is a lot more reliable and, for most access points, faster than Wi-Fi.
  2. No optical disk drive: How am I supposed to read/write CDs and DVDs with installation media to setup older computers?  How am I supposed to play DVDs? — a lot of educational media is still on DVDs, if not tape!
  3. No user upgradable parts:  There’s no way to replace a stick of memory if one has gone bad.  There’s no way to upgrade your memory if you need more.  There’s no way to buy a larger hard drive if you run out of space.  You have to consider how much memory and space you’ll need up front, and pay Apple’s premium for that specific configuration.

Yes, I realize that down the road (even currently) probably people don’t need a network port, optical disk drive, or upgradable parts, but those people are probably not “Pro” users.  That is exactly the reason why I think the current Retina MacBook Pro should be renamed as “MacBook”, and that the MacBook Pro lineup continue to be refreshed.

A real Macbook Pro

What I would consider a real MacBook Pro would be one with the 2nd generation hardware (retaining the ethernet port and optical disk drive) updated with an Intel Haswell processor and retina display.  Now that would be something I would find worth buying.  Basically, if Apple took the old MacBook Pro line and refreshed it with a new processor and a retina display, that would be the perfect computer for me, and I’m sure I wouldn’t be the only one buying it.

Platform Signage at Granville SkyTrain Station

Comments on the new signage at SkyTrain stations

I was going through some of my photos and came across a set where I was comparing old and new signage on the SkyTrain.  Below is one example from Granville Station.  You can see the new sign in the foreground, with the existing sign further back.  What struck me is how complex the information is on the new sign.

Platform Signage at Granville SkyTrain Station

Directional signage at Granville SkyTrain Station

The primary emphasis (judging from the size of the text) of the new platform signs is placed on the platform numbers, as opposed to the direction of travel as is in the old sign.  In fact, the direction of travel isn’t even on the new sign at all.

I remembered reading the following guideline some months ago from an old New York City Transit Authority Graphics Standards Manual circa 1970.

The subway rider should be given only information at the point of decision.  Never before.  Never after.

The decision to be made at the faregates is whether I want to cross the faregates or not.  The information about the direction of the platforms is presented too early to the rider.  The information that I’d expect to see above the faregates to help me with that decision would be something along the lines of “To Trains – Expo & Millennium Lines – Westbound to Downtown; Eastbound to Burnaby, New Westminster, Surrey“.  This indicates that there are trains are behind the gates, which lines they run on, and where I could possibly go from here.  The information about the specific platforms doesn’t need to be shown at the point of the faregates.

After that, at the intersection where the old sign is, the rider can be shown information on the different platforms and destinations.  However, it would make more sense to me to emphasize the direction of travel, and the destination instead of the platform number, especially since most stations only have two platforms.  Platform numbers are only be useful for people following a trip plan, or if there are two or more lines at a station; they aren’t really useful in any other circumstance.

The effect of giving people information too early can also be seen on the signage at Burrard Station, depicted below.

New signage and fare gates at Burrard SkyTrain Station

New directional signage and fare gates at Burrard SkyTrain Station.  Notice the arrows pointing in a variety of directions.

There should actually be two decision points: one at the faregates whether to enter or not, and the second one at the intersection of the corridors to decide which train to take.  Since platform directions are given at the decision point of the faregates, the arrows pointing to the platforms go in all different directions.  The existing signs above the corridors to each of the platforms is the correct decision point (whether to enter into the corridor or not) to give platform information.

The guideline in the old NYCTA Graphic Standards manual makes a lot of sense to me now.  Putting relevant information only at the decision point makes signs less cluttered with information.

Dennis' little corner on the big big internet!