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.
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.
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).
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? Read more…
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. Read more…