B4J Question SqlLite: question about efficiency

[JordiCP]

I have a webservice which manages some data using SQLite. I managed to do it based on examples and it works perfectly, but as I am not an expert I have some doubts about efficiency, specially if the database grows.

In this DB there are basically 2 tables, one for users with some configuration data, and another one for contacts belonging to each user.

Regularly, or under request, each user app uploads his contacts to the server. From one upload to the next, this data will be exactly the same, or will be nearly the same with some minor modifications.

My question is, what would be more efficient (in SQLite)?

a) Erase the previous data in the table belonging to that user and write it again with the fresh info
b) Read user's contacts in the table, compare them with the new ones and update them, i.e.: do nothing with the registers that do not change, delete the ones that are missing and insert the ones that were not in the previous one?
c) other options

Thanks in advance

 

[Erel]

I think that you should choose the method that is most simple for you to implement. Unless the table is huge the performance will not be an issue.

You do need to add an index in the second table for the first column.

Make sure to follow this tutorial: [WebApp] Concurrent access to SQLite databases
And to properly use transactions when you insert data.

 

[cimperia]

As mentioned in the thread that Erel referenced, Write-Ahead Logging is generally a must for concurrent access of a Sqlite database.

The simplest solution is to update the users details, whenever a change occurs (unless an insert is requested because a new user's details need recording). It won't make any noticeable difference whether you update fully or partially the database record, therefore it's easier and less prone to error, to update the full record.

Deleting and inserting is a not a good solution as it is less efficient (produces more rollback info, therefore more IO and locks the database longer) and as well fragments more quickly your database.

BTW, do you allow your users to delete their own details. If yes, how do you handle it?

 

[JordiCP]

Thanks both for the points!

BTW, do you allow your users to delete their own details. If yes, how do you handle it?

Well, not exactly. Each user-app sends the phone agenda from time to time, and the server searches which contacts (in table 1) also have the app installed. If so, it registers this "pairment" (along with other data) in table 2. Then the server sends back the ids of these registered users so that they will appear at a list.
As phone contacts can change (add/delete/modify), I had the need to keep updating this second table in the background