I’ve been working with SQL since around the year 2000. Over that time I have worked with MySQL, Oracle, SQL Server, PostgreSQL, and SQLite.
A couple years ago the company I worked for had had just launched a free consumer-facing application, and had been pushing it hard on social media. They were seeing hundreds to thousands of active users. It was painfully slow, and the culprit appeared to be the database. One of the main queries on the application was pushing the database server to its limits. Some users […]
Optimizing Away a LEFT JOIN in a Pinch Continue Reading »
A common problem I have worked on in my career has been to troubleshoot and improve slow, complex SQL queries. Usually these are part of codebases with few or no tests. If there are tests, they usually are testing some other part of the code and assume that the query is correct. It is rare to see tests that prove the columns being selected are
Quickly Testing A Refactored SQL Query for Equivalence Continue Reading »
From time to time, I have had to go back to a legacy database and for one reason or another add a non-integer surrogate key. Sometimes this has come up when working on an old API that exposes a primary key in the URL (GET /account/123). This generally can be bad for security, as a user could attempt to access other resources by incrementing the
Generating a Short Random ID in MySQL Continue Reading »
About 5 years ago I was introduced to Flyway, a tool for managing schema migrations using simple SQL scripts. I’ve used it on several projects now, and have more recently been introduced to Liquibase. The two tools both solve similar problems, with slightly different approaches. The main purpose of these tools is to version changes to the schema of the databasse. Liquibase has a good
Database Schema Migrations : A Few Lessons Learned Continue Reading »
I’ve been doing a lot of tweaking of SQL queries as of late, trying to squeeze some performance out of a query by adding indexes, optimizing joins, and some other operations. To do a quick check to ensure that the queries return the same results, I’ve been hashing the results to see if I get an identical hash back. This solution was adapted from https://stackoverflow.com/a/13948327/206480.
Getting a quick hash of a query in PostgreSQL Continue Reading »
PostgreSQL has two functions that are not standard SQL, but are very helpful in certain situations : GREATEST and LEAST. In SQL MIN and MAX are aggregate functions that operate across all rows. But there are times where you want the smallest or largest value in a finite number of columns, a scalar min/max. That is where GREATEST and LEAST come in. Both allow for
PostgreSQL’s GREATEST (and LEAST) function Continue Reading »
There have been a handful of times over the last few years where I have needed to take time series data and group the runs of data together to determine when a certain value changed, and how long it stayed that way. Sample Data We have a log table that, after keeping only the columns relevant for this post, looks like the diagram above. logged_at
Grouping runs of data in SQL Continue Reading »
At work we have a copy of the zip-codes.com Business database, which I tend to reference somewhat frequently due to the nature of projects I work on. Today I needed a list of postal codes from major metro areas which would be used to drive a test data generator. The data generator has access to a simpler, non-commercial zip code list, and can do radius
Use SQL to Find Central ZIP Codes for Metro Areas Continue Reading »
Over the last couple of weeks, I have been spending some time troubleshooting performance issues in some of our Java-based RESTful services. This week I came across one that required some different steps to troubleshoot. First, the tl;dr – our JDBC driver (jTDS) converts CHAR to NCHAR, which causes an index SCAN on a CHAR column instead of an index SEEK. The Architecture Here is
Troubleshooting a SQL Server Implicit Conversion Issue Continue Reading »
