read sqlite datatable verry slow

[John_141]

Hi hope someone can help me
Read sqlite datatable is verry slow
I have a listview in detail mode with 45 columns.
and a datatable to add, read, delete and update datarows with 45 colums.
Only i have a problem to read the datatable and show it all in the listview, its verry slow
at the moment i have 10.000 datarows in the table and i have to wait about 6 seconds to load it to the listview.
Later there will be come about 10.000.000 and maby more datarows in the table so than i have to wait about 6000 seconds or almost 2 hours to load all the data
How can i make it faster.
the code i have is

 Dim myCon As SQLiteConnection

                myCon = New SQLiteConnection("Data Source=BurgerlijkeStand.db")
                Dim dta As New DataTable                            
                Dim ta As New SQLiteDataAdapter("SELECT * FROM Doopgegevens ORDER BY Landgedoopt, Provinciegedoopt, Gemeentegedoopt, Plaatsgedoopt ASC", myCon)

                ta.Fill(dta)

                Try

                    myCon.Open()
                    Dim myrow As DataRow

                    For Each myrow In dta.Rows
                        Dim lst As ListViewItem
                        lst = ListView1.Items.Add(If(myrow(0) IsNot Nothing, myrow(0).ToString, ""))
                        For m As Integer = 1 To dta.Columns.Count - 1
                            lst.SubItems.Add(If(myrow(m) IsNot Nothing, myrow(m).ToString, ""))

                        Next

                    Next

                    myCon.Close()
                    myCon.Dispose() : myCon = Nothing
                Catch eSql As System.Data.SQLite.SQLiteException
                    MessageBox.Show("code 11e " & eSql.ToString)
                End Try

Thanks i advice John

[pty]

I doubt it's your query that's slow. What happens if you simply run the query select * from Doopgegevens; in a shell? For ~10k rows, providing your table is holding 'normal' data (and not base64 encoded movie files or something) it should be fast. If it is fast, try changing your data list to only use a subset of your table's columns, which will indicate if that's where your performance problem is.

[John_141]

I created a new sqlite table with 2 columns 1 row as ID and 1 row as text
i put 10.000 datarows in it so i have only one column to read
I'ts a bit faster but not much it took about 3,5 seconds and even that is verry long time
and the query i wrote is
"Select * FROM newtable"

And i have only tekst in the table and NO images or what else

newtable is the name of new datatable

Thanks

[rproffitt]

A 10K item listview is rare. And 10 Million is just a bad design since how could any user scroll through that many.

http://stackoverflow.com/questions/4944123/how-many-items-a-listview-can-store kicks around the capacity of Listview along with the issues of design and how to load as many as you can see at a time.

I'd think over the design since such a bulky listview is a sign something is off in the desing.

[John_141]

i tried it all with a datagridview and its much faster, fast than 1 second
but i would use it in a listview if it can't faster than i have to rewrite my whole aplication.

and my answer about: And 10 Million is just a bad design since how could any user scroll through that many.
it is possible
even i have a several search functions built in so thats no problem and all works.

[rproffitt]

@OP about your search functions and all works, my bet is the search functions returns less than 10 thousand or million. I am guessing you haven't had a class on how SQL works. Sets? Remember sets?

A search in most cases will not return the entire database (select *).

-> But just in case you ask why datagridview works and listview differs, the first gets as much data as it needs for the display (fast) and listview in your current design does a Pokemon "gotta get them all."

[Reverend Jim]

If you are populating the Listview/DataGridView in a loop then I suggest you disable updates before you start the loop then enable them after the loop. This will save you a lot of processing time doing unnecessary control updates.

[John_141]

Oke thanks for the reply I will rewrite all and will use the datagridview without loop.

[Lee_15]

There is a reason they call it lite. Have you tried loading it in sql-2012? I use lite to manage a cookbook series. Everything is fine until I add volumes/journals. sqlLite doesn't have the ram mangers that normal sql-2012 ,has and the whole process slows down significantly with journals. Three to five seconds doesn't seem like a terribly long time to read an entire database. I also noticed that depending on how I'm connected to the database affects the refresh rate. USB2 is slow USB3 is slightly better, porting is terrible slow. If your just manage the database on a daily bases and just wondering why it's slow try turning off headers, loops, disable attributes until you find the cause of the lag. My database tend to be incredibly simple but I have found injecting code, & testing UI to be very slow on sqlite. I personally think it's the nature of the beast and not a good choice for a large database. The best part about it is it's free and you can fiddle around with it.