I did some tests filling a datagridview with an SQLITE table and found out some interesting points, I would like to share with you.
Test envirnoment
PC : AMD64 X2 Dual Core 5000+ 2GB RAM, XP Pro SP2, VS 2005
Database : table 249.000 records, 6 columns, no key, database size 13,6 MB
Version : phx-sqlite 1.0.48.0
Result Summary
| |
Time (seconds) |
SELECT ROWID,* |
SELECT * |
SELECT f1,f2... |
| DBShowTableReader_FastBind |
t1=0,00 t2=3,10 t3=0,16 |
Sum=2,4-3,6 |
2,7-3,0 |
2,8-3,1 |
| DBShowTableReader_TableLoad |
t1=0,00 t2=6,03 t3=0,89 |
Sum=6,1-6,9 |
3,0-4,1 |
4,0-4,2 |
| DBShowTableAdapter_DataTable |
t1=0,00 t2=3,40 t3=0,92 |
Sum=4,0-4,3 |
3,8-4,2 |
3,8-4,0 |
| DBShowTableAdapter_DataSet |
t1=0,00 t2=3,40 t3=1,56 |
Sum=4,8-5,1 |
4,7-4,9 |
4,6-4,8 |
| AutoResizeColumns |
|
Sum=0 -55 sec |
|
|
| For comparison: |
| SQLiteAdministrator |
(Delphi) |
|
2,5-3 |
|
| SQLite Database Browser |
(C++) |
|
7-11 |
|
| SQLITE2008 Pro |
(VB6) |
|
21 |
|
Conclusions:
1. Times were broken up into 3 parts (see samples below):
t1: Initialization (zero for all testcases, e.g. ExecuteReader unexpectedly takes no measurable time)
t2: Data reading (takes by far most of the time)
t3: Binding data to the datagridview (is quite quick, so usually VirtualMode is not worth the effort)
The total time is from hitting "Go" until the program is ready for the next input (not just the SELECT)
2. There is no runtime difference between Debug und Relase versions of the program
3. Measured times vary significantly, usually a second run is slower than the first probably caused by the garbage collector
4. SELECTs with ROWID are signicantly slower than without (at least in TableLoad)
5. There is no difference between SELECT * and SELECT field1,field2,...
6. FastBind is by far the fastest method. You can use the BindingNavigator control, but you are not able to sort, search or filter.
7. Table_Load is the slowest method (with ROWID) with no obvious advantages
8. Using a datatable is significantly faster than a dataset, both allow column sorting in the datagridview, but no effortless usage of the bindingnavigator (if you find one, please post it).
9. AutoResizeColums can be very slow
What is the difference between Datagridview.AutoresizeColumns() and Datagridview.Autosize = True ?
No visual difference at first glance, AutoResizeColumns() is much slower
Use DataGridViewAutoSizeColumnsMode.AllCells... with care, see Sub for details
10. Execution speed depends on the methods, not on the programming language or environment
Coding samples
Private Sub DBShowTableReader_FastBind(ByVal tablename As String)
Dim bs As New System.Windows.Forms.BindingSource
Dim cmd As New System.Data.SQLite.SQLiteCommand("SELECT * FROM " & tablename, cn)
t1 Dim dr As System.Data.SQLite.SQLiteDataReader = cmd.ExecuteReader
t2 bs.DataSource = dr
F1.Datagridview1.DataSource = bs
F1.BindingNavigator1.BindingSource = CType(F1.Datagridview1.DataSource, System.Windows.Forms.BindingSource)
t3 dr.close()
End Sub
Private Sub DBShowTableReader_TableLoad(ByVal tablename As String)
Dim cmd As New System.Data.SQLite.SQLiteCommand("SELECT * FROM " & tablename, cn)
Dim table As New System.Data.DataTable
t1 Dim dr As System.Data.SQLite.SQLiteDataReader = cmd.ExecuteReader
t2 table.Load(dr)
t3 F1.Datagridview1.DataSource = table
dr.close()
End Sub
Private Sub DBShowTableAdapter_DataTable(ByVal tablename As String)
Dim sql As String = "SELECT * FROM " & tablename
Dim da As System.Data.SQLite.SQLiteDataAdapter = New System.Data.SQLite.SQLiteDataAdapter(sql, cn)
t1 Dim dt As New System.Data.DataTable
t2 da.Fill(dt)
t3 F1.Datagridview1.DataSource = dt
End Sub
Private Sub DBShowTableAdapter_DataSet(ByVal tablename As String)
Dim sql As String = "SELECT * FROM " & tablename
Dim da As System.Data.SQLite.SQLiteDataAdapter = New System.Data.SQLite.SQLiteDataAdapter(sql, cn)
t1 Dim ds As New System.Data.DataSet
t2 da.Fill(ds)
t3 F1.Datagridview1.DataSource = ds.Tables(0)
End Sub
Private Sub AutoResizeColumns()
F1.Datagridview1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.None
'F1.Datagridview1.AutoResizeColumns()
'AllCells 55 sec, AllCellsExceptHeader 55 sec, ColumnHeader 27 sec, DisplayedCells 27 sec, DisplayedCellsExceptHeader 27 sec, Fill 27 sec, None 27 sec
F1.Datagridview1.AutoSize = True
'AllCells 27 sec, AllCellsExceptHeader 27 sec, ColumnHeader 0 sec, DisplayedCells 0 sec, DisplayedCellsExceptHeader 0 sec, Fill 0 sec, None 0 sec
End Sub