Results 1 to 2 of 2

Thread: Mysql AutoComplete Combobox

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2002

    Mysql AutoComplete Combobox


    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

            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
            Dim r As DataRow
            For Each r In dt.Rows
                Txt_textbox.AutoCompleteMode = AutoCompleteMode.Suggest
                Txt_textbox.AutoCompleteSource = AutoCompleteSource.CustomSource
            'closing the connection

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Bristol, UK

    Re: Mysql AutoComplete Combobox

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width