Dear List,
I faced with critical performance problem on a WinCE device when db
resides on it's slow built-in flash card (Windows CE .Net Version
4.20, processor Intel PXA255, some Toshiba obsolete compact flash
card, C++ application using SQLITE API directly).
Our application uses SQLite for collecting numerous measurements data
in field and for exchange the data between the field controller and
office postprocessing software. The db schema contains a number of
tables and triggers enforcing reference integrity in the db, as usual
like
CREATE TRIGGER on_update_fk_tblStations_fkeySoPoint
BEFORE UPDATE OF [fkeySoPoint] ON [tblStations]
FOR EACH ROW BEGIN
SELECT RAISE(ABORT,'update violates foreign key constraint for
[tblStations].[fkeySoPoint]')
WHERE NEW.fkeySoPoint IS NOT NULL
AND (SELECT keySoPoint FROM tblSoPoints WHERE keySoPoint =
NEW.fkeySoPoint) IS NULL;
END;
All series of inserts and updates are done inside BEGIN and END transaction.
Having the triggers is essential for our application though it looks
like it's also essential to the problem we faced with after upgrade to
SQLITE 3.5.x. Though the problem proved to exist only for db on slow
flash cards there is no way to upgrade the devices and we have to
continue their support for a while. The problem did not exist in
earlier SQLITE versions - the performance of version 3.4.3 was quite
acceptable. Unfortunately we had to look for upgrade to more recent
version after we discovered the 3.4.3 had problems with removing
temporary files (http://www.sqlite.org/cvstrac/tktview?tn=2950 or
http://www.sqlite.org/cvstrac/tktview?tn=2350,33 for example) which
were critical for the divice. After we switched to version 3.5.3 the
speed of insert and update dropped by 10+ times. For 3.5.6 it's better
but still about 1.5-2 times slower then for 3.4.3 (again, the media
speed makes huge difference here: on very fast hard drives or flash
cards 3.5.6 works even better then 3.4.3!)
After some investigation I suspect it's the frequent access to the
temporary dbname-stmtjrnl file that slows down everything. The file
resides on the same folder as the db that's on that slow flash card.
I've tried to play with setting path to temporary files to the WinCE
standard place (\Temp which is in a RAM emulated file system and
read/write access is very fast for files there) but with no success. I
found that in pager.c (yes we can not use amalgamized version as the
VC++ debugger is getting crazy on line numbers grater then 65K) in
sqlite3PagerOpen() the field pPager->zStmtJrnl is hard coded to be set
to the same path as pPager->zJournal which will always set to the same
path as the db.
Sorry for the long introduction. Here is the question. Am I right
there is no option defined to have the stmtjrnl file in memory or in
some specified path other then the same folder as the db? It's
critical to prevent the db corruption in all kinds of software and
hardware fails. If I manage stmtjrnl file to be created in memory
instead of the slow flash card and the file disappear after a power
brake on the device will it result in unrecoverable corruption of the
data in the db?
Any other advice for my problem?
Thank you all in advance.
Regards,
Dima Dat'ko
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
