CODEWITHSUNDEEP
c#sqldatabasesqlite
james prisma
james prisma

Reputation: 11

C# SQLite reader in for loop slow

I am retrieving data from a sqlite database using a data reader. This is executed in a double for loop. But it's very slow, because now it's executing the sql query multiple times.

Is there a more quick way to do this? Here is a short version of what I am doing now:

for(int i=0;i < horizontal; i++)
{
  for(int j=0;j < vertical; j++)
  {
    SQLiteConnection con = new SQLiteConnection(dbConnection);
    con.Open();
    SQLiteCommand cmd = new SQLiteCommand(con);
    cmd.CommandText = query;        
    SQLiteDataReader reader = cmd.ExecuteReader();
    dt.Load(reader);
    con.Close();

    // Perform some actions onto DataTable data
  }
}

EDIT: Opening and closing before the loop doesn't help a lot. I am trying to query all the rows of the table where the position column is between 4 points, perform some calculations on it and move to the next cell (think of a grid).

Upvotes: 0

Views: 895

Answers (3)

Mystic Lin
Mystic Lin

Reputation: 375

Do you using Finisar.SQLite;?

If so, try change to using System.Data.SQLite;. It's very helpful to me, form 6s reduce to 150 ms.

Upvotes: 0

jonhy05
jonhy05

Reputation: 11

SQLiteConnection con = new SQLiteConnection(dbConnection);
con.Open();
SQLiteCommand cmd = new SQLiteCommand(con);
for(int i=0;i < horizontal; i++)
{
  for(int j=0;j < vertical; j++)
  {
    cmd.CommandText = query;    
    SQLiteDataReader reader = cmd.ExecuteReader();
    dt.Load(reader);
    // Perform some actions onto DataTable data
  }
}
con.Close();

only change the command text, no need to instance a new one each time. Also, only close it when you are done doing queries.

Upvotes: 1

Bart
Bart

Reputation: 1

If you are using the same connection you can create SQLiteConnection object and open the connection above iterations, you should also close the connection under iterations. It should be faster.

Upvotes: 0

Related Questions