Simple trick to optimize SQL joins

This comes up very often on StackOverflow: Make my query faster!

My first trick is always the same:

SmallTable INNER JOIN BigTable

And not the other way around. Your mileage may vary.

Remember the Ctrl-S shortcut, Keep it Holy

I’ve been using an old (really old now that I think about it) SQLite GUI simply called SQLite Administrator for years. It’s light, retro-compatible with SQLite2, and does the basics of what one wants out of an SQLite GUI:

• List tables
• Table (and triggers, indexes, views) edition/creation GUI
• On the fly queries
• Export to CSV

If you’ve used Windows enough in the last 15 years, you know Ctrl-S means save. Across dozens of programs. If for some reason there is no save function, Ctrl-S is rarely used.

SQLite Administrator broke holy law (besides being pissy about large integers and dates) by mapping Ctrl-S (undocumented) as clear screen (and of the no undo variety).

Now trying SQLiteStudio.

Fun with substring (as replacement for IN clause)

Different approach I was shown by a colleague to replace an IN clause in SQL in some cases.

Suppose you have  a limited set of small values (I wouldn’t go beyond 255 for this) and want to do have an IN clause. You can substitute it for a well crafted substring.

Create a string (in your language of choice) as long as required (as long as the max value in your field). Make it all zeroes (or character of choice). At indexes of the values desired, change for 1’s. Then, what you’re going to want to do is use a substring function (not two are identical across the many implementations of SQL, it seems), and make the search target your prepared string, the start index will be your database value, and length 1. All this equal to ‘1’ for IN or different for NOT IN.

For example, suppose:

WHERE SmallValue IN (3,4,8)

would then become (in Access):

WHERE Mid('00110001',SmallValue,1) = '1'

In some cases (long table, values not unique) this will work faster than IN/NOT IN. Your mileage may vary.

Please mind index start (1 for Access, other databases might start at 0).

Grouping by time

This guy knows his stuff.

Fun with subqueries

Today I was working with an old Access database, on a program that’s essentially meant to move it out of access into another.

One query, ran several times:

SELECT Id, SubId, SomeTime, ColA, ColB...
FROM SomeTable
WHERE SomeTime BETWEEN %STARTTIME% AND %ENDTIME%;

Some code later actually filtered out some of the SubId’s… OK, fine, let’s add that:

SELECT Id, SubId, SomeTime, ColA, ColB...
FROM SomeTable
WHERE SomeTime BETWEEN %STARTTIME% AND %ENDTIME%
AND SubId IN (%LIST_OF_SUBIDS%);

Oops, it’s slowed the whole query. But why?

The banker’s database

This is not from me, it’s from a teacher of mine. I thank him to this day for this wonderful image.

Most database classes will teach you how to store data for a few simple models (a store with an inventory, sales, and customers, for example) and through that many will believe in a purely relational model. 100% theoretical. Even when doing actual database work, first database designs will be very much purely relational. And changing database engines is only the best occasion to re-achieve that perfect model!

Let us consider the database for a bank.