jeudi 17 décembre 2015

Saving data to a sqlite local database too slow. Any ideas how to make it faster?

I am having a hard time trying to save the data faster, to a local DB.

Even though this is a one time saving, when the app runs for the first time, it takes like 90 seconds, in a Lumia 920, to save "only" the "map tables".

What I do: 1) I call an API, where I receive all the grids, with its Xs, Ys, Map Id, etc. 2) I deserialize the info based on a class I have defined. 3) For each item, in that info, I save the "misc" info (since I will use it) 4) I save, in a GRIDS table, each grid inside the previous item.

This code snipet is what I use to deserialize the info, and call the function to save in the DB

public class Maps
{
        public string id { get; set; }
        public string name { get; set; }
        public string height { get; set; }
        public string width { get; set; }
        public string tile { get; set; }
        public string shopping_id { get; set; }
        public string url { get; set; }
        public string updated_at { get; set; }
        public string created_at { get; set; }
        public GridFirst gridFirst { get; set; }
        public GridLast gridLast { get; set; }
        public List<Grid> grid { get; set; }


    public class GridFirst
    {
        public string id { get; set; }
        public string x { get; set; }
        public string y { get; set; }
        public string maps_id { get; set; }
        public string value { get; set; }
    }

    public class GridLast
    {
        public string id { get; set; }
        public string x { get; set; }
        public string y { get; set; }
        public string maps_id { get; set; }
        public string value { get; set; }
    }

    public class Grid
    {
        public string id { get; set; }
        public string x { get; set; }
        public string y { get; set; }
        public string maps_id { get; set; }
        public string value { get; set; }
    }

    public void deserializeAndConvert(string aaa)
    {
        JObject myGeneral = JObject.Parse(aaa);
        IList<JToken> results = myGeneral["resp"].Children().ToList();

        // serialize JSON results into .NET objects
        IList<Maps> searchResults = new List<Maps>();
        foreach (JToken result in results)
        {
            Maps searchResult = JsonConvert.DeserializeObject<Maps>(result.ToString());
            searchResults.Add(searchResult);

        }

            var respuesta = from data in searchResults
                       select new
                       {
                           id = data.id,
                           name = data.name,
                           height = data.height,
                           width = data.width,
                           tile = data.tile,
                           url = data.url,
                           lastX = data.gridLast.x,
                           lastY = data.gridLast.y,
                           grid = data.grid
                       };
            foreach (var a in respuesta)
            {
                Database_Controller.getReadyToSaveData("mapinfo", 8, a.id, a.name, a.height, a.width, a.tile, a.url, a.lastX, a.lastY, "", "", "", "", "", "", "");

                foreach (var data in a.grid)
                {
                    Database_Controller.getReadyToSaveData("mapgrid", 5, data.id, data.x, data.y, data.maps_id, data.value, "", "", "", "", "", "", "", "", "", "");
                }
            }
    }
}

And these are the functions that save the data, in the DB

public static void getReadyToSaveData(string dbName, int numberOfParams, string param1, string param2, string param3, string param4, string param5, string param6, string param7, string param8, string param9, string param10, string param11, string param12, string param13, string param14, string param15)
        {
            List<string> myParams = new List<string>();
            myParams.Add(param1);
            myParams.Add(param2);
            myParams.Add(param3);
            myParams.Add(param4);
            myParams.Add(param5);
            myParams.Add(param6);
            myParams.Add(param7);
            myParams.Add(param8);
            myParams.Add(param9);
            myParams.Add(param10);
            myParams.Add(param11);
            myParams.Add(param12);
            myParams.Add(param13);
            myParams.Add(param14);
            myParams.Add(param15);

            List<string> myParamsToDB = new List<string>();
            for (var i = 0; i < numberOfParams; i++)
            {
                myParamsToDB.Add(myParams[i]);
            }

            insertData(dbName, myParamsToDB);
        }

        public static void insertData(string dbName, List<string> paramsToGo)
        {
            try
            {
                using (var connection = new SQLiteConnection("Unicenter.sqlite"))
                {                    
                    if (dbName == "mapgrid")
                    {
                        using (var statement = connection.Prepare(@"INSERT INTO mapgrid (ID,X,Y,MAPS_ID,VALUE)
                                    VALUES(?, ?,?,?,?);"))
                        {
                            statement.Bind(1, paramsToGo[0]);
                            statement.Bind(2, paramsToGo[1]);
                            statement.Bind(3, paramsToGo[2]);
                            statement.Bind(4, paramsToGo[3]);
                            statement.Bind(5, paramsToGo[4]);
                            // Inserts data.
                            statement.Step();
                            statement.Reset();
                            statement.ClearBindings();
                        }
                    }
                    if (dbName == "mapinfo")
                    {
                        using (var statement = connection.Prepare(@"INSERT INTO mapinfo (ID,NAME,HEIGHT,WIDTH,TILE,URL,LASTX,LASTY)
                                    VALUES(?, ?,?,?,?,?,?,?);"))
                        {
                            statement.Bind(1, paramsToGo[0]);
                            statement.Bind(2, paramsToGo[1]);
                            statement.Bind(3, paramsToGo[2]);
                            statement.Bind(4, paramsToGo[3]);
                            statement.Bind(5, paramsToGo[4]);
                            statement.Bind(6, paramsToGo[5]);
                            statement.Bind(7, paramsToGo[6]);
                            statement.Bind(8, paramsToGo[7]);
                            // Inserts data.
                            statement.Step();
                            statement.Reset();
                            statement.ClearBindings();
                        }
                    }                  
                }

            }
            catch (Exception ex)
            {
                Debug.WriteLine("Exception\n" + ex.ToString());
            }
        }

Any idea on how to make it faster? ... What am I doing wrong?

Aucun commentaire:

Enregistrer un commentaire