Speeding up textbox textchanged event with sql server connection to search for values

Question

I have an application with a textbox textchanged event to search values in a database.

Now its realy slow typing characters in the textbox. I read somonewhere that this is happening becase it needs to make a connection to the database for each character you type in the textbox. But i have also read that all the sql connections stay open in a pool so you can use them again. (so what is true becase if the pool thing is true it should be fast right?)

the solution i trie was implementing a timer so that i know when user stops typing and then search the database so it only has 1 connection. the speed was good when i was typing but because it now came on another thread i needed to implement this rule in page_load

System.Windows.Forms.Control.CheckForIllegalCrossThreadCalls = False

but now i am getting all kind of errors on the datagridview and some weird behavior i dont understand.

So my question is: is there another solution to speed this up or is the timer event the only one.

thanks in advance

Answer 1

Dont use CheckForIllegalCrossThreadCalls = False! And if you do not want to block your UI-Thread, you have to run the (time)expensive operation in the background.

Private Sub ExpensiveFoo(s As String)
    If Me.InvokeRequired Then
        System.Threading.Thread.Sleep(1000)
        Me.Invoke(Sub() ExpensiveFoo(s))
    Else
        TextBox2.Text = s
    End If


End Sub


Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged

    Console.WriteLine(TextBox1.Text)
    Dim s As String = TextBox1.Text
    Task.Run(Sub() ExpensiveFoo(s))

End Sub

The TextChanged Event now simply starts a task which will do some work in the background and when it's done, will update a second textbox - WITHOUT using the dreaded CheckForIllegalCrossThreadCalls = False