Need for speed.

[meerkat]

I have a database that allows users to schedule assets.

Has 2 tables. One is the asset and the other are schedules for the assets
Assets can be equipment, conference rooms, just about anything you need to schedule

The second table is the schedules.
It has the asset that is scheduled and the begin and end time of the schedule.
The time is stored as "YYYY-MM-DD HH:MM". I only schedule to the nearest minute, so don't carry seconds.

Assets file only has a couple hundred records.
Schedule contains a couple thousand

The user request a asset, the date they'd like it, and the number of hours, and minutes.
schBy$ = the begin date they requested.
numSlots = the number of open slots they requested.
laps = the lapse time they want in minutes.

It returns a list of available times that fit their requirement. They select the best slot of time and schedule it/or not.

It takes about 1/4 second or less to find all the open slots.

Does anyone see a way to speed this up.

The SQLite DB tables involved

CREATE TABLE    schTime (
schNum INT(2),
userNum INT(5),
schBeg DATETIME,
schEnd DATETIME,
assetNum INT(3),
event CHAR(4),
name VARCHAR(10),
descr VARCHAR(50) );
CREATE UNIQUE INDEX schNum ON schTime ( schNum );
CREATE INDEX schTime_schBeg ON schTime ( schBeg );



The sql code is not very complex, yet there is a slight pause when the users request a schedule.

sql$ = "
SELECT  
s.schNum,
s.assetNum,
s.schEnd as preEnd,
s1.schBeg as nxtBeg,
((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) as laps,
(strftime('%w',s.schEnd))   as dowBeg,
asset.descr as aDescr,
asset.assetType
FROM schTime as s
JOIN asset
ON asset.assetNum = s.assetNum
JOIN schTime   as s1
ON s1.assetNum = s.assetNum
AND s1.schBeg > s.schEnd
AND ((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) >= "+minSlots+"
AND s1.schBeg = (SELECT min(s2.SchBeg)
FROM schTime   as s2
WHERE s2.assetNum = s.assetNum
AND s2.schBeg > s.schEnd) "+schBy$+"
AND     s.schBeg >= "+begSlotDate$+" LIMIT "+numSlots



Thanks for the help...
Dan..

[jerry]

I see that this is over a year old, but let me add my $0.02 worth none-the-less.

Sqlite is a wonderful database and is very forgiving. That in mind, I have a runbasic program being accessed by over 30 users, banging away at a sqlite database. The best possible performance improvement for me was to move the database file(s) to a ramdrive.

That said, I have to ask... is your pause between request for data and the recieved data... is this due to sqlite or network latency? a QUARTER SECOND is pretty typical across the itnet. if the latter, you have no recourse.

Jerry