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

[John LeSueur]

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.
As for myself, I just find the explicit joins so much easier to read. As
far as I know, there's no
reason not to always use them.

John LeSueur