Wsrtjtyk

I am inserting 3000 plus data from my server to my SQLite Database. The problem is the inserting process is very slow. Is there a better way to insert the data efficiently and effectively? What I am doing is I converted the data I got from my server to JSON Object and insert it one-by-one. I know what I am doing is inefficient. How can I fix this?

public class AndroidSQLiteDb : ISQLiteDB

{

    public SQLiteAsyncConnection GetConnection()

    {

        var dbFileName = "backend.db3";

        var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);

        var path = Path.Combine(documentsPath, dbFileName);



        return new SQLiteAsyncConnection(path);

    }

}



public async void FirstSyncContacts(string host, string database, string contact)

    {

        try

        {

            var db = DependencyService.Get<ISQLiteDB>();

            var conn = db.GetConnection();



            var sql = "SELECT * FROM tblContacts WHERE Coordinator = '" + contact + "'";

            var getContacts = conn.QueryAsync<ContactsTable>(sql);

            var resultCount = getContacts.Result.Count;

            var current_datetime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:00");



            //Check if the retailer has been sync

            if (resultCount < 1)

            {

                try

                {

                    syncStatus.Text = "Syncing Retailer";



                    var link = Constants.requestUrl + "Host=" + host + "&Database=" + database + "&Contact=" + contact + "&Request=9DpndD";

                    string contentType = "application/json";

                    JObject json = new JObject

                    {

                        { "ContactID", contact }

                    };



                    HttpClient client = new HttpClient();

                    var response = await client.PostAsync(link, new StringContent(json.ToString(), Encoding.UTF8, contentType));



                    if (response.IsSuccessStatusCode)

                    {

                        var content = await response.Content.ReadAsStringAsync();



                        if (content != "")

                        {

                            var contactsresult = JsonConvert.DeserializeObject<List<ContactsData>>(content);



                            foreach (var item in contactsresult)

                            {

                                // update only the properties that you have to...



                                item.LastSync = Convert.ToDateTime(current_datetime);

                                item.ServerUpdate = Convert.ToDateTime(item.ServerUpdate);

                                item.MobileUpdate = Convert.ToDateTime(item.MobileUpdate);

                            }

                            await conn.InsertAsync(contactsresult);



                        }

                    }



                    //Proceed to next function

                    FirstSyncRetailerGroup(host, database, contact);

                }

                catch (Exception ex)

                {

                    Console.Write("Syncing Retailer Error " + ex.Message);

                }

            }

            //If not get the retailer

            else

            {

                SyncContacts(host, database, contact);

            }

        }

        catch (Exception ex)

        {

            Console.Write("Syncing Retailer Error " + ex.Message);

        }

    }

1. Use the non-async Insert in one background thread, instead of 3000 separate async calls...




2. Re-use the List from your DeserializeObject step instead of creating new local objects that will just be thrown away on each loop iteration.




3. No need to assign all those json properties (item.XXX) to another local variable, just update the properties of each existing ContactsData as needed before inserting it into the DB.

Example using SQLiteConnection:

// Use the non-async version of SQLiteConnection

var conn = new SQLiteConnection(dbPath, true, null);



// code removed for example...



await System.Threading.Tasks.Task.Run(() =>

{

    var contactsresult = JsonConvert.DeserializeObject<List<ContactsData>>(content);



    // start a transaction block so all 3000 records are committed at once.

    conn.BeginTransaction();



    // Use `foreach` in order shortcut the need to retrieve the object from the list via its index

    foreach (var item in contactsresult)

    {

        // update only the properties that you have to...



        item.LastSync = Convert.ToDateTime(current_datetime);

        item.ServerUpdate = Convert.ToDateTime(item.ServerUpdate);

        item.MobileUpdate = Convert.ToDateTime(item.MobileUpdate);



        conn.Insert(item);

    }

    conn.Commit();

});

Example using SQLiteAsyncConnection:

var db = DependencyService.Get<ISQLiteDB>();

var conn = db.GetConnection();



~~~



var contactsresult = JsonConvert.DeserializeObject<List<ContactsData>>(content);



foreach (var item in contactsresult)

{

    // update only the properties that you have to...



    item.LastSync = Convert.ToDateTime(current_datetime);

    item.ServerUpdate = Convert.ToDateTime(item.ServerUpdate);

    item.MobileUpdate = Convert.ToDateTime(item.MobileUpdate);

}

conn.InsertAsync(contactsresult); // Insert the entire list at once...