dcsimg
Results 1 to 2 of 2
  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2002
    Posts
    91

    Mysql AutoComplete Combobox

    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


    Code:
          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()

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

    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:
    Code:
    .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
  •  



Featured


Click Here to Expand Forum to Full Width


×
By using this site, you agree to the Privacy Policy