using dataset gets slower as records are added to database

[jkkenzie]

The form gets slower to load and also save as more records are added to database:
Is there another way i can avoid loading the database table first?

If State = gModule.FormState.adStateAddMode Then
            Try
                Dim qryCompanies As String = "SELECT * FROM `tbl_business`"

                daCompanies.SelectCommand = New MySqlCommand(qryCompanies, conn)

                Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(daCompanies)

                daCompanies.Fill(dsCompanies, "tbl_business")

                Me.txtAppFormNo.Focus()

            Catch ex As MySqlException
                MsgBox(ex.ToString)
            Finally
                conn.Close()
            End Try
        ElseIf State = gModule.FormState.adStateEditMode Then

 

[Menthos]

Without knowing what your app is doing with the data, it's hard, however does MySQL not support paging / selecting within a range? if this data is being displayed to the user, split the retrieves up into 'paged' chunks.

 

[jmcilhinney]

What sort of numbers are we talking about?

Also, what's the command builder for if you aren't saving anything in that method?