CODEWITHSUNDEEP
c#sqliteselect
CC Inc
CC Inc

Reputation: 5938

c# - Optimize SQLite Select statement

I am using System.Data.SQLite for my database, and my select statements are very slow. It takes around 3-5 minutes to query around 5000 rows of data. Here is the code I am using:

        string connectionString;
        connectionString = string.Format(@"Data Source={0}", documentsFolder + ";Version=3;New=False;Compress=True;");
        //Open a new SQLite Connection
        SQLiteConnection conn = new SQLiteConnection(connectionString);
        conn.Open();

        SQLiteCommand cmd = new SQLiteCommand();
        cmd.Connection = conn;
        cmd.CommandText = "Select * From urls";
        //Assign the data from urls to dr
        SQLiteDataReader dr = cmd.ExecuteReader();

        SQLiteCommand com = new SQLiteCommand();
        com.CommandText = "Select * From visits";
        SQLiteDataReader visit = com.ExecuteReader();

        List<int> dbID2 = new List<int>();
        while (visit.Read())
        {
            dbID2.Add(int.Parse(visit[1].ToString()));
        }
        //Read from dr
        while (dr.Read())
        {
            string url = dr[1].ToString();
            string title = dr[2].ToString();
            long visitlong = Int64.Parse(dr[5].ToString());
            string browser = "Chrome";
            int dbID = int.Parse(dr[0].ToString());
            bool exists = dbID2.Any(item => item == dbID);
            int frequency = int.Parse(dr["visit_count"].ToString());

            bool containsBoth = url.Contains("file:///");

            if (exists)
            {
                if (containsBoth == false)
                {
                    var form = Form.ActiveForm as TestURLGUI2.Form1;

                    URLs.Add(new URL(url, title, browser, visited, frequency));
                    Console.WriteLine(String.Format("{0} {1}", title, browser));
                }
            }

        }
        //Close the connection
        conn.Close();

And here is another example that takes long:

IEnumerable<URL> ExtractUserHistory(string folder, bool display)
{
    // Get User history info
    DataTable historyDT = ExtractFromTable("moz_places", folder);

    // Get visit Time/Data info
    DataTable visitsDT = ExtractFromTable("moz_historyvisits",
                                           folder);



    // Loop each history entry
    foreach (DataRow row in historyDT.Rows)
    {
        // Select entry Date from visits
        var entryDate = (from dates in visitsDT.AsEnumerable()
                         where dates["place_id"].ToString() == row["id"].ToString()
                         select dates).LastOrDefault();
        // If history entry has date
        if (entryDate != null)
        {
            // Obtain URL and Title strings
            string url = row["Url"].ToString();
            string title = row["title"].ToString();
            int frequency = int.Parse(row["visit_count"].ToString());
            string visit_type;

            //Add a URL to list URLs
            URLs.Add(new URL(url, title, browser, visited, frequency));

            // Add entry to list
            //    URLs.Add(u);
            if (title != "")
            {
                Console.WriteLine(String.Format("{0} {1}", title, browser));
            }
        }
    }

    return URLs;
}



DataTable ExtractFromTable(string table, string folder)
{
    SQLiteConnection sql_con;
    SQLiteCommand sql_cmd;
    SQLiteDataAdapter DB;
    DataTable DT = new DataTable();

    // FireFox database file
    string dbPath = folder + "\\places.sqlite";

    // If file exists
    if (File.Exists(dbPath))
    {
        // Data connection
        sql_con = new SQLiteConnection("Data Source=" + dbPath +
                            ";Version=3;New=False;Compress=True;");

        // Open the Connection
        sql_con.Open();
        sql_cmd = sql_con.CreateCommand();

        // Select Query
        string CommandText = "select * from " + table;

        // Populate Data Table
        DB = new SQLiteDataAdapter(CommandText, sql_con);
        DB.Fill(DT);

        // Clean up
        sql_con.Close();
    }
    return DT;
}

Now, how can I optimize these so that they are faster?

Upvotes: 0

Views: 5301

Answers (2)

laifukang
laifukang

Reputation: 311

Make sure that you've recently run the SQL command "ANALYZE {db|table|index};".

I recently ran into a situation where queries were running fast (<1 sec) in my ER software (Navicat), ie: not debugging, but they were very slow (>1 min) debugging in Visual Studio. It turned out that because I did my database design in Navicat (SQLite v3.7), the statistics were not the same as those used by System.Data.SQLite in Visual Studio (v3.8). Running "ANALYZE;" on the entire database file from Visual Studio updated the [sqlite_statX] tables used by v3.8. Both places were the same speed after that.

Upvotes: 0

JYelton
JYelton

Reputation: 36546

In addition to moving more of the data aggregation to SQL as joins, you might also consider getting your SQLiteDataReader to provide the data types instead of always parsing the values.

For example, you have the line:

long visitlong = Int64.Parse(dr[5].ToString());

dr[5] is a Sqlite value which you are first converting to a string, then parsing it to a long. These parse operations take time. Why not instead do:

long visitlong = dr.GetInt64(5);

Or:

long visitlong = dr.GetInt64(dr.GetOrdinal("columnName"));

Check out the various methods that SqliteDataReader offers and utilize them whenever possible instead of parsing values.

Edit:

Note that this requires the data be stored as the correct type. If everything in the database is stored as a string, some parsing will be unavoidable.

Upvotes: 1

Related Questions