How to speed up query with multiple INNER JOINs
database, ms-access, sql, sqlite
Solution
As requested, I'm reposting my previous comment as an actual answer (when I first posted the comment I was not able, for some reason, to post it as an answer):
MS Access is very aggressive about indexing columns on your behalf, whereas SQLite will require you to explicitly create the indexes you need. So, it's possible that Access has indexed either [Description] or [D] for you but that those indexes are missing in SQLite. I don't have experience with that amount of JOIN activity in SQLite. I used it in one Django project with a relatively small amount of data and did not detect any performance issues.
Problem
I've been toying around with switching from ms-access files to SQLite files for my simple database needs; for the usual reasons: smaller file size, less overhead, open source, etc. One thing that is preventing me from making the switch is what seems to be a lack of speed in SQLite. For simple SELECT queries, SQLite seems to perform as well as, or better than MS-Access. The problem occurs with a fairly complex SELECT query with multiple INNER JOIN statements: ``` SELECT DISTINCT DESCRIPTIONS.[oCode] AS OptionCode, DESCRIPTIONS.[descShort] AS OptionDescription FROM DESCRIPTIONS INNER JOIN tbl_D_E ON DESCRIPTIONS.[oCode] = tbl_D_E.[D] INNER JOIN tbl_D_F ON DESCRIPTIONS.[oCode] = tbl_D_F.[D] INNER JOIN tbl_D_H ON DESCRIPTIONS.[oCode] = tbl_D_H.[D] INNER JOIN tbl_D_J ON DESCRIPTIONS.[oCode] = tbl_D_J.[D] INNER JOIN tbl_D_T ON DESCRIPTIONS.[oCode] = tbl_D_T.[D] INNER JOIN tbl_Y_D ON DESCRIPTIONS.[oCode] = tbl_Y_D.[D] WHERE ((tbl_D_E.[E] LIKE '%') AND (tbl_D_H.[oType] ='STANDARD') AND (tbl_D_J.[oType] ='STANDARD') AND (tbl_Y_D.[Y] = '41') AND (tbl_Y_D.[oType] ='STANDARD') AND (DESCRIPTIONS.[oMod]='D')) ``` In MS-Access, this query executes in about 2.5 seconds. In SQLite, it takes a little over 8 minutes. It takes the same amount of time whether I'm running the query from VB code or from the command prompt using sqlite3.exe. So my questions are the following: - Is SQLite just not optimized to handle multiple INNER JOIN statements? - Have I done something obviously stupid in my query (because I am new to SQLite) that makes it so slow? And before anyone suggests a completely different technology, no I can not switch. My choices are MS-Access or SQLite. :) UPDATE: Assigning an INDEX to each of the columns in the SQLite database reduced the query time from over 8 minutes down to about 6 seconds. Thanks to Larry Lustig for explaining why the INDEXing was needed.