Speeding up records filtering as and when user types

Question

My application is in C# [.NET 3.5] and MySQL 5.1 back-end.

I have a Windows Form with a TextBox and a DataGridView. When the user types in few characters in the TextBox, an SQL Query with a Like clause is run to filter the records shown in the DataGridView below.

The items list has grown considerably and also I don't find running an SQL query on each character input appropriate. An alternative way I think is to create a DataSet when the application loads and fill it with the recent stock position upfront. Than use LINQ or something like that to filter the in-memory record-set. But this method is also not optimized because whenever a new bill is created, items from the stock are reduced and each time I will need to update the in-memory record-set.

Is there any other optimized and faster way?

Answer 1

There are a few optimizations that can be done

Database layer
Great post on how to optimize everything on db side

You can also try sharding and horizontal scaling

Application side
Do not use dataset, as they are heavy, bulky, slow and make it hard to maintain the same copy of data as on the db. Think over a few tricks:

• Cache user search
• Start searching only after 2 or 3 symbols typed
• Run search in background thread
• Limit search to TOP 20 (or whatever)
• Make sure search is light from various code abstractions, make it simple
• Optimize you search query, order of SQL operations does matter

Infrastructure

• Check your connection speed, any possibility to improve it?
• Put your db as close to the application as possible

Answer 2

At work I had something similar but depending on your exact requirements it might not be that helpful. What we did was actually take the app and split off the database querying into some REST services. On the server back end portion we had the application move everything into memory and cache it at start up. We sent all changes through the server so we could invalidate portions of the cache and reload as needed.

On the client side, once a couple characters were entered, we sent a request to the server. The server sent us back the list of items matching that, and we cached it client side. We then just filtered that list client side and only did another request when the field was cleared or the first couple characters were changed.

Even without the server part, you could do something similar with the client side.