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 

Have a look at his one:

http://sqlite.phxsoftware.com/forums/p/1174/4932.aspx

 

Thanks Mark

But I have already solved this issue. I declared that no more than 100,000 rows be displayed at once and added "next" and "previous" buttons to help navigate through the table. On every button click the contents of dataGridView were cleared and new ones were read using the SQL LIMIT statement. Not a very good solution, since it takes some time to navigate through the whole table, but I was short of time. Thanks for the link though, it might come in handy for future projects.

 Martin

Have you tried running this not in debug mode ie by doing a release build of your applicatioin and running that rather than hitting debug?  Debugging is known to slow down P/Invoke calls by serveral orders of magnitude and SQLite.Net is very P/Invoke heavy. 

 

My own measurements (see link above) did not show any measureble performance difference between Debug and Release code.
If SqliteAdmin (which is fast) takes 9 seconds he should be able to do it in a similar time.
Although the dataset approach is not the fastest, the cause for the long time it takes most probably lies somewhere else. E.g. AutoResizeColumns is very slow.

SQLiteAdmin, being a GUI wrapper around the SQLite DLL, doesn't have to conform to ADO.NET specifications like I do.  I'm fairly certain SQLiteAdmin isn't even written in a .NET language.

The .NET datagridview IMO is a bit of a nasty control.  It's slow to draw and has a lot of bloat.  .NET really needs a more streamlined datagrid.

 

Robert,

I have updated my DataGridView benchmark.

You have definitely no reason to excuse your wrapper, it is excellent work and it is not really slower than unmanaged stuff (SQLiteAdmin is written in Delphi).
The DataGridView is not nasty and not slow, you just need to know what you are doing. Fiddling of 500.000 records doesn't come for nothing.

:)