Re: [sqlite] Faster way to perform this query?

[D. Richard Hipp]

John LeSueur wrote:
Zahraie Ramin-p96152 wrote:
Hello
I have the following schema:
1)Table: dvrpAudioSourceTable
[varchar]        [int]     [int]           [int]              
[int]      [varchar]
sourceIndex | dsuIp | dsuPort | sourceType | workIp | SourceName|

2)Table: dvrpAudioFileTable
[int]    [varchar]    [int]          [int]
fileId | filePath | startTime | endTime
3)Table: dvrpIndexTable
[varchar]         [int]
sourceIndex | fileId
I need to perform the following query:
select a.sourceIndex, a.dsuIP, a.dsuPort, a.sourceType, a.workIp, 
a.SourceName, b.fileId, b.filePath, b.startTime, b.endTime from 
dvrpAudioSourceTable a, dvrpAudioFileTable b, dvrpIndexTable c where 
b.startTime>= 100 and b.endTime <= 200 and c.fileId = b.fileId and 
a.sourceIndex = c.sourceIndex order by a.sourceIndex, b.fileId;

Currently on Sqlite version 2.8.13, this query on tables with 1000 
rows each, returns 50 rows but it takes about 30 seconds. Is there 
anyway to significantly speed up this query?

Using 'PRAGMA default_synchronous = OFF;' made no difference.
Thanks very much in advance.
 

use explicit inner joins e.g.
select
    a.sourceIndex, a.dsuIP,
    a.dsuPort, a.sourceType,
    a.workIp, a.SourceName,
    b.fileId, b.filePath,
    b.startTime, b.endTime
from
    dvrpAudioSourceTable a
    inner join dvrpIndexTable c       
        on
            a.sourceIndex = c.sourceIndex
    inner join dvrpAudioFileTable b
        on
            c.fileId = b.fileId
where
    b.startTime>= 100 and b.endTime <= 200
order by a.sourceIndex, b.fileId;

and create indexes for all fields that you join on.
I know that oracle, postgres, and mysql all have
optimizers that will convert what you had written before to inner joins,
but IIRC, sqlite does not.
Actually, SQLite will convert joins of the form Zahraie first submitted
into the explicit INNER join as shown by John LaSueur.  What SQLite does
not do is reorder the FROM clause.  The original statement listed the
tables in the FROM clause as A,B,C.  John's revision changed this to
A,C,B.  That change is what makes the different, not the use of the
ON clause and the explicit INNER JOIN operator.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565