DataSet Fill Extremely SLOW

[djcybex]

Connecting to MySQL Database

I need to read table "foto" which has 180.000 records

 

this is the code i use:

Dim MyConStr As String = "DRIVER={MySQL};" & _
                 "SERVER=localhost;" & _
                 "DATABASE=mzp_sem;" & _
                 "UID=user;" & _
                 "PASSWORD=pass;" & _
                 "OPTION=3;"

Dim MyConn As New OdbcConnection(MyConStr)
Dim MyCom As New OdbcCommand()
Dim MyDataReader As OdbcDataReader
Dim DS As DataSet = New DataSet()
Dim rowCount As Long

' THIS IS FAST .. I GET RECORD COUNT IN LESS THAN A SEC
MyCom.Connection = MyConn
MyCom.CommandText = "SELECT COUNT(id) FROM foto"
rowCount = MyCom.ExecuteScalar()

Dim adapter As New OdbcDataAdapter("SELECT * FROM foto ORDER BY id", MyConn)
adapter.Fill(DS, DBTable) 'THIS TAKES AGES to open the FORM!! ABOUT 10 minutes

 

 

I have also tried this way:

Dim rowNumber As Long
Dim adapter As New OdbcDataAdapter("SELECT * FROM foto ORDER BY id LIMIT " & rowNumber & ",1", MyConn)
adapter.Fill(DS, DBTable)

 

rowNumber is for browsing through records...

here the FORM opens quickly but when browsing to the last records it takes 10 seconds to show next record...

 

if i move from first record to last one it takes a minute!!

 

what am i doing wrong?

[Derek Stone]

It's going to take forever, since you're essentially taking what could be millions of bytes of data all in one big chunk. I really don't see any need for doing this. Take smaller chunks at a time.

[Nerseus]

If your ID field on the foto table is a Primary Key, Identity, you can use it to filter records. Try something like:

 

SELECT TOP 20 * FROM foto WHERE id > @LastID ORDER BY id

 

In this case, @LastID stores the last known ID that you viewed. The "TOP 20" brings back the first 20 records it finds. Using both pieces should give you a 20-record paging mechanism. I sure hope you've got a key set up on the ID field so that the id ordering is fast. :)

 

Note that this will mostly only work if you're moving forward through the data. You can't jump, for instance, to page 10 (records 200 through 210) since there's no guarantee that id is in sequential order with no id's missing.

 

A *much* better solution would be to somehow ask the user for a filter and apply that first. Or, apply their filter and a "TOP 250" so that they never get back more than 250 records (or some number). Sometimes users want the world and you've just got to reel them in :)

 

-ner

[Arch4ngel]

Be sure not to have big binary data in your DataSet. Like lots of pictures. Cause it'll take so much time... that' you'll be in age to wear diappers. :p