database retrieval unexplainably slow

Programming Language EVB
This line of code
Set rstBible = cnBible.Execute(strRstBible)
takes about 7 seconds to execute.

strRstBible is
Select * From Bible
Where Bookid=1 and Chapter=1
Order by verse ASC

The database is a cdb database of the Bible. The command retrieves 1 chapter from 1 book of the Bible. The database is located on the storage card of the Jornada. It is 7.15 MB.
All the fields in the select statement are indexed or at least they were when I converted them from .mdb to cdb when I transferred the database to my Jornada 720.
7 seconds does not seem reasonable for this task. Isn't the 720 processor capable of doing this much faster? Does anyone know how I can optimize performance?

Using SELECT * in any SQL statement carries a performance hit. You should sequentially call the table.field values individually

strRstBible = SELECT Bible.Chapter, Bible.Bookid, bible.verse FROM Bible WHERE Bible.Bookid=1 and Bible.Chapter=1 ORDER BY Bible.verse ASC

Also remember to order the values hierachically along the record fields and then the ORDER BY in logical order.

You also may find it worth while running a compact and repair on the MDB file you are synching up to the device.
Also understand the speed restriction imposed by having it on a CF card, the CF bottlenecks the data stream.

The suggested changes in the select made no difference in the time. All the records in the database are already ordered. I am trying to create my own Bible reader similar to Laridian and Olive Tree. When I install their free readers on my Jornada, the Bible Chapters load very quickly. There must be some way to make the data access faster.

Are the lardian books really CDB files?

As effectivly you are asking the HPC to filter throug the entire 8MB file for every request.
If they don't need to be ordered then don't add the sort statement.

All it needs is an index

I'm a bit rusty here, but have you done a basic retrieve recordset call to see if one record takes a while to even load?

What other fields are there actually? Can I assume that Bookid = 1 and Chapter = 1 would retrieve only one recordset? I would think there are many records in chapter 1 itself. How large is that one chapter? If its the whole of genesis ...

C:Aime is right. Olive tree may not be using cdb altogether. For my own app, I actually use the Palm PDB format, and its very zippy if you ask me. Just a record pointer table to look up with fixed offset, and use the record pointer to retrieve the record. Whatever works for the job!

Also, the sort could be rather memory intensive. I don't think the sort flag will cause much rereading from the CF, but prob be done in memory. OLEDB (not sure about CDB) APIs mostly massage the records in memory, working directly on the file is unthinkable.

oh last question ... how many records are there in the cdb of yours?

You assume that the device has sufficent storage / processing and paging memory for an 8MB CDB.

I'm with you here Snappy!, 8MB is a lot of ASCII.

This reminds me of my talk at TechEd2000 back in Singapore ... I think its within the same circa, so I believe this might apply.

PocketPC (and presumably HPCs, since they aren't any beefier than PPCs) Apps that dealt with databases are almost relegated to work as offline aggregators instead of processing them directly. I remember how developers are encouraged to use SQLCE to have disconnected records stored on the PPCs and synced up with the main SQL store when network is available.

Then again, PocketOutlook and Contacts uses the database, so I dun see why it should be a problem. Just gotta take note of the size of the whole cdb and the recordset itself.

Databases are fun. .... yikes ...

ADOCE has its limitations though, it's really only for just that - PIM data. Not designed for much heavier use.
Microsoft keep upping the support of ADOCE as CE grows up. Before CE4 databgase size was limited to under 128MB - and you were basically crazy to try to get it up there.