dimanche 1 mars 2015

Windows Phone 8 optimized insertion into sqlite table from json array

My Windows Phone 8 app is downloading a list of items from a server in json format:



{"error":false,"lists":[{"code":1,"name":"item 1"},{"code":2,"name":"item 2"}, ... ,{"code":100000,"name":"item 100000"}]}


As should be noticed, my app is downloading 100.000 items.


Then my app decodes the json and iterates each item (of 100.000) in order to insert it into the mobile app sqlite database:



JObject content = JObject.Parse(result);
string jsonLists = content.GetValue("lists").ToString().Trim();
JArray jarrTAR = JArray.Parse(jsonLists);

foreach (JObject content2 in jarrTAR.Children<JObject>())
{
string code = content2.GetValue("code").ToString().Trim();
string name = content2.GetValue("name").ToString().Trim();
...
using (var db = new SQLiteConnection(MainPage.DBPath))
{
db.RunInTransaction(() =>
{
db.Insert(new Table1()
{
Code = code,
Name = name,
...
});
});
}
}


This works, but it takes more than 10 minutes inserting the 100.000 items into the sqlite database.


I think this approach (insert items one by one into the table) may be acceptable for a few items downloading, but what should be the recommended strategy for inserting the items when the amount of them are about 100.000?


Is there any way for inserting the total items in one query in order to optimize the insertion time?


Excuse me my bad English.


Aucun commentaire:

Enregistrer un commentaire