Yes this is the first SELECT Querie that returns a result set.
- The Client app launches and opens a connection,
- A TRANSACTION is begun
- Multiple UPSERTS are performed if data is available ( about 5k rows of:
REPLACE INTO MyTable ( RowID, UpDtUNIX, Zip, cCode, RecID, jDate, Blob1, Blob2,
Blob3) VALUES ( ?,?,?,?,?,?,?,?,? )
prepare()
bind()
step()
reset()
finalize()
- A querie is executed to retrieve a result set from the table of 40k records:
SELECT RowID, Zip, RecID, Blob1, Blob2 FROM MyTable
WHERE RecID=1 OR RecID=4 OR RecID=5
AND jDate > MinJulianDate AND jDate < MaxJulianDat
ORDER BY cCode
RowID is the Primary Key and there is an Index for jDate but not RecID or cCode.
Now I suspect your (very valid) first suggestion will be to add an index for
these columns, then the first querie will not have to do all the processing
which I assume is something like reading the entire database and making a
temporary index for the other columns that has a shelf life of the connection
hande?)
Unfortunatly the design spec is VERy focused upon the UPSERT phase of the app.
This has to meet some requirements that have intially dictated the reduction in
the number of keys.
While it would make sense to discuss ways to possibly improve that, the real
point is that no one cares that the first Querie takes so long as long as there
some progress indication (and I dont mean a endlessly repeating progress bar)
So I am wondering if there is a way for SQLite to report progress?
The other scenario where this would make sense would be when there are many
columns and a user is given the ability to design his own Querie that may
include columns that are not indexed.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
