title: c# - Optimize SQLite Select statement
body: <p>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:</p>
  
  <pre><code>        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&lt;int&gt; dbID2 = new List&lt;int&gt;();
          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 =&gt; 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();
  </code></pre>
  
  <p>And here is another example that takes long:</p>
  
  <pre><code>IEnumerable&lt;URL&gt; 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;
  }
  </code></pre>
  
  <p>Now, how can I optimize these so that they are faster?</p>