How to speed up query with multiple INNER JOINs

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.[oCod开发者_运维技巧e] = 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:

1. Is SQLite just not optimized to handle multiple INNER JOIN statements?
2. 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.

---

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.

---

Do you have issues with referencial integrity? I ask because have the impression you've got unnecessary joins, so I re-wrote your query as:

SELECT DISTINCT 
       t.[oCode] AS OptionCode, 
       t.[descShort] AS OptionDescription 
  FROM DESCRIPTIONS t
  JOIN tbl_D_H h ON h.[D] = t.[oCode]
                AND h.[oType] = 'STANDARD'
  JOIN tbl_D_J j ON j.[D] = t.[oCode]
                AND j.[oType] = 'STANDARD'
  JOIN tbl_Y_D d ON d.[D] = t.[oCode]
                AND d.[Y] = '41'
                AND d.[oType] ='STANDARD'
 WHERE t.[oMod] = 'D'

---

If DESCRIPTIONS and tbl_D_E have multiple row scans then oCode and D should be indexed. Look at example here to see how to index and tell how many row scans there are (http://www.siteconsortium.com/h/p1.php?id=mysql002).

This might fix it though ..

CREATE INDEX ocode_index ON DESCRIPTIONS (oCode) USING BTREE; CREATE INDEX d_index ON tbl_D_E (D) USING BTREE;

etc ....

Indexing correctly is one piece of the puzzle that can easily double, triple or more the speed of the query.