Hello As you may recall, yesterday I asked for help on optimizing a query. I received a series of outstanding suggestions that I incorporated which made a great difference. As a matter of review, here is my schema:
Create TABLE dvrpAudioFileTable(fileId varchar(256), filePath varchar(256),timeRangeStartSeconds INTEGER, timeRangeStartuSeconds INTEGER, timeRangeStartTotal FLOAT, timeR angeEndSeconds INTEGER, timeRangeEnduSeconds INTEGER, timeRangeEndTotal FLOAT); Create TABLE dvrpAudioSourceTable(sourceIndex VARCHAR(256), dsuIPAddress INTEGER, dsuPortNumber INTEGER, sourceType SMALLINT, workStationIpAddress INTEGER, sourceName VAR CHAR(256)); Create TABLE dvrpDataFileTable(fileId varchar(256), filePath varchar(256), timeRangeStartSeconds INTEGER, timeRangeStartuSeconds INTEGER,timeRangeStartTotal FLOAT, timeRa ngeEndSeconds INTEGER, timeRangeEnduSeconds INTEGER, timeRangeEndTotal FLOAT); Create Table dvrpIndexTable(sourceIndex VARCHAR(256), fileId INTEGER); CREATE INDEX FILEIDX ON dvrpAudioFileTable(fileId); CREATE INDEX SOURCEIDX ON dvrpAudioSourceTable(sourceIndex); CREATE INDEX SOURCEINDEX ON dvrpIndexTable(fileId, sourceIndex); CREATE INDEX TIMEINDEX on dvrpAudioFileTable(timeRangeStartTotal, timeRangeEndTotal); The indices were created after series of tests based on the suggestions I received yesterday. This set seems to give the best performance. When I execute the following query from the sqlite command line: select a.sourceName, a.dsuIPAddress, a.dsuPortNumber, a.sourceType, a.workStationIpAddress, b.filePath, b.timeRangeStartSeconds, b.timeRangeStartuSeconds, b.timeRangeEndSeconds, b.timeRangeEnduSeconds from dvrpAudioFileTable b, dvrpIndexTable c, dvrpAudioSourceTable a where b.timeRangeStartTotal >= 9 and b.timeRangeEndTotal <= 9000 and c.fileId = b.fileId and a.sourceIndex = c.sourceIndex order by a.sourceIndex, b.fileId; A 1000 rows of data return instantaneously. However when this very query is executed through the use of the C API using a very similar code to this: http://www.hwaci.com/sw/sqlite/quickstart.html The query takes about 19 seconds at best to complete. Does anyone know why there is such a discrepancy? Thanks very much in advance. Regards
