[Gpephone-devel] problem about sqlite

[YE NAN RD-ILAB-PEK]

Hi yum,
 
In your last email, you ask two questions:
 
1) When the records in database are removed, the size of database file CANNOT be truncate automatically. You need to use VACUUM to cleanup the backend database files and shrink the size. But performance is really bad and seems not be suitable on embedded devices. You also found that "PRAGMA auto_vacuum = 1" is not effective on ARM devices.
 
Yes, some operations that cause a mass data exchange will bring a bad performacen on embedded devices. Currently we have two approach to resolve this problem. One approach is to pack a set of these operations in one transaction, begin with "BEGIN TRANSACTION" and "END TRANSACTION". For example,
 
BEGIN TRANSACTION
INSERT INTO ...
INSERT INTO ...
INSERT INTO ...
...
DELETE FROM ...
DELETE FROM ...
...
END TRANSACTION
 
Large INSERTs and DELETEs could use this approach bring a great improvement of performance on embedded device.  
 
Another approach is to make this kind of operations ocurr peridically in background. For example, we need NOT to set auto_vacuum but do VACUUM command manually in background after the device is started up per one or two days. 
We have tested "PRAGMA auto_vacuum = 1", but it's really not effective even on our i386 desktop. I check the documentation of SQLITE, and it's said that when auto-vacuum is enabled for a database, large delete cause the size of the database file to shrink. So, maybe our test deletes is NOT large enough to lead auto-vacuum occur. It's need further tests I think. But automatically vacuum will cause excess fragmemntation of database file, so it's NOT recommanded.
 
2) How to deal with the database collapse?
We haven't think much about this matter till now. So if you've some good idea, we can discuss about this :)
 
Best regards,
Nan YE

________________________________

From: gpephone-devel-bounces at linuxtogo.org [mailto:gpephone-devel-bounces at linuxtogo.org] On Behalf Of 余民
Sent: 2007年5月8日 15:11
To: gpephone-devel at linuxtogo.org
Subject: [Gpephone-devel] problem about sqlite


在使用sqltie 数据库时遇到问题:
 
1 在数据库中删除记录后,数据库空间不能自己变小,手动调用vacuum指令可以回收空间,但在嵌入式机器上速度很慢,不可取.
设置auto_vacuum标志位好象在arm上未起作用
不知道应该如何处理?
 
2当数据库文件崩溃如何处理?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.linuxtogo.org/pipermail/gpephone-devel/attachments/20070509/747af06a/attachment.htm