System.Data.SQLite

Hi,

I'm having a problem with slow dataGridView filling. My table has ~400000 records and it takes about 53 seconds to load the data in my code, while in SqliteAdmin it takes only 9 seconds. Here is the code :

DataSet ds = new DataSet();
string sql = "SELECT rowid, length,sec,usec,sip,dip,protocol,sport,dport,packet_id" +
                         "FROM "+tableName;
            using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + PathToDatabase))
                {
                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))
                    {
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0].DefaultView;
                    }
                }

Any ideas are welcome!

 

I have at least 2 solutions:
1.use virtual mode of datagridview with pages in cache ( http://msdn2.microsoft.com/en-us/library/ms171622.aspx )
2.control scrollbar or something :) and handle database with LIMIT in query,

And first of all: keep in mind that if user cannot see all of data then why are you loading it into datagridview? read only needed parts. :) if user wants to see more then give him it:) but not earlier:):) then your app will work like a lightning!

Thank you for the advice. I tried to query the dataGridView with the LIMIT statement which could work just fine, however there is a problem. Once I assign a data source to the dataGridView, I can no longer set the value of dataGridView1.RowCount and therefore the vertical scrollbar is restricted to the LIMITed query. Is there any way to set the scrollbar's height and handle querys for scrolling up/down events ? Thank you very much!

Sakyamuni