Mysql AutoComplete Combobox

[ssalomon]

Hi

I am try to use Mysql AutoComplete Combobox
if I add this code to TextChanged then I need to Wait for some time

How can I use AutoComplete Combobox without Delay
the database have 117739 record

      con.Open()
        dt = New DataTable
        With cmd
            .Connection = con
            .CommandText = "select * from streets where streetname LIKE  '" & Txt_textbox.Text & "%'  ORDER BY streetname Asc"
        End With

        da.SelectCommand = cmd
        da.Fill(dt)

        Dim r As DataRow
        For Each r In dt.Rows
            Txt_textbox.AutoCompleteCustomSource.Add(r.Item("HEITINF").ToString)
            Txt_textbox.AutoCompleteMode = AutoCompleteMode.Suggest
            Txt_textbox.AutoCompleteSource = AutoCompleteSource.CustomSource
        Next
        'closing the connection
        con.Close()

[si_the_geek]

There are several things you are doing that make it slower, two of which are in the SQL statement: you are using "Select *" when you only want one field (the other fields all get transferred from the database and stored in memory, which is all slow), and you are using "Order By" (which is slow) when it presumably has no effect.

As such, you should get a speed boost just by changing the SQL to this:

.CommandText = "select HEITINF from streets where streetname LIKE '" & Txt_textbox.Text & "%'"

Another issue is that you have things inside the loop that have no need to be there (only the first line does), which means you are doing over 230000 operations you don't need to. Simply moving the other two lines outside the loop should give another speed boost.


Something else to change is to swap from using a DataTable etc (which needs to load all the data before doing anything with it) to using a DataReader (which allows the rest of your code to start working as soon as the first piece of data arrives, and avoids delays from locks etc), as that should provide another speed boost.



Making all of those changes might make it acceptable, but the fact you are using the TextChanged event is likely to be problematic, because almost any delay is likely to be noticeable. There are various ways you can lessen the impact while still using the TextChanged event, but it is likely to be better to avoid that event entirely, and pre-fill the AutoComplete info with all of the data from the table when the window is first shown. You will get a delay at another point in the application, but it will only happen once rather than on every keypress in the control.