Improving Wetware

Because technology is never the issue

Utilizing SQL

Posted by Pete McBreen 05 Oct 2023 at 04:23

A surprising number of systems I have worked on in the past 15 years have been drastically slower than they need to be because the developers did not understand how to write anything other than a simple single table query. So what was visible in the application was that the application would

  1. Issue a query to the database for a container.
  2. In a loop, issue a different query to get the details of each of the contents of the container.
  3. If you were unlucky, then go through the loop again to get some related details about each of the contents.

This works fine in development with just a few rows in the table, any database can run 1 + 3 + 3 queries in milliseconds. But in production when there are 1000+ items in the container, suddenly the application is trying to do 2,000 queries, and suddenly the milliseconds turns into seconds. All because the developers used the programming language to replace a slightly more complicated SQL statement that could return the desired data in a single database request.

Now whenever I am working on a new codebase, I look for queries that are issued inside loops (or hidden loops inside streams), as practically every time I have to do performance tuning, that is where the excess delays live.