Optimization loading data in memory

Question

I need to load data from SQLite to memory, and when the table contains more than 40k entries this process lasts for a few minutes. Data in db is encrypted so I decrypt it to load in memory.

Basically, I am:

loading all the data from the table
while it is reading I decrypt the info and add it to a dictionary

The code:

internal static void LoadInfo(Dictionary<int, InfoMemory> dic)
{
    using (SQLiteConnection con = new SQLiteConnection(conString))
    {
        using (SQLiteCommand cmd = con.CreateCommand())
        {
            cmd.CommandText = String.Format("SELECT ID, Version, Code FROM Employee;");

            int ID, version, code;

            try
            {
                con.Open();
                using (SQLiteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        try { ID = Convert.ToInt32(Decrypt(rdr[0].ToString())); }
                        catch { ID = -1; }
                        try { version = Convert.ToInt32(Decrypt(rdr[1].ToString())); }
                        catch { version = -1; }
                        try { code = Convert.ToInt32(Decrypt(rdr[2].ToString())); }
                        catch { code = -1; }

                        if (ID != -1)
                        {
                            if (!dic.ContainsKey(ID)) dic.Add(ID, new InfoMemory(version, code));
                        }
                    }
                    rdr.Close();
                }
            }
            catch (Exception ex) { Log.Error(ex.Message); }

            finally { con.Close(); GC.Collect(); }
        }
    }
}

How can I make this process faster? One way I try is by loading encrypted data to memory and decrypt when needed, but if I do that I consume more memory. Since I am working in mobile devices I want to maintain the memory consumption as low as possible.

Use a conditional to determine if you can convert, the try/catches are VERY slow. — Ron Beyer, Aug 03 '15 at 14:26
Have you determined which part in your code is the bottleneck? I suspect the decrypt function might be it. — Magnus, Aug 03 '15 at 14:28
I am making suggested changes and will post results with time and bottlenecks — Ignacio Gómez, Aug 03 '15 at 15:01

score 1 · Answer 1 · answered Aug 03 '15 at 14:31

1

One thing you can do is; instead of using try/catch:

int id, version, code;

if(!int.TryParse(rdr[0], out id))
{
    id = -1;
}

if(!int.TryParse(rdr[1], out version))
{
    version = -1;
}

if(!int.TryParse(rdr[2], out code)) 
{
    code = -1;
}

answered Aug 03 '15 at 14:31

Marcus

8,230
11
61
88

Marcus is right. Never use "try catch" if the catch often happens when getting data, because the catch process is time consuming. – Graffito Aug 03 '15 at 14:34
1

At least, before parsing, test if rdr[i] is not null, DBNull.Value or empty string. – Graffito Aug 03 '15 at 22:42

Optimization loading data in memory

1 Answers1