Member Avatar for catcit

I have a table that contains approximately 22000 rows and I used a Boolean Full-Text Search in order to find what I`m interested in. My problem is that I created a 'dynamic search feeling' that consists of a DataGridView that it is refreshed after every TextChanged event. As you might have figured out it takes a lot of time to search for the inserted string after every event.

What could I do in order to improve the search speed?

Any suggestions are welcomed!

  • 2 Contributors
  • 3 Replies
  • 198 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by C#Jaap

Dani AI

Generated

Quick, practical summary for : avoid a full round-trip to MySQL on every TextChanged. For ~22k rows the easiest, lowest-risk path is to combine a short debounce (so you only search when typing pauses), cancel any outstanding query when new input arrives, and prefer an in-memory filter or an incremental server query instead of reloading the grid end-to-end each keystroke. 's suggestion to load once and filter client-side is a good first step; the 11-thread split you tried usually doesn't help because the bottleneck is I/O and UI binding, not CPU. (stackoverflow.com)

A concrete immediate plan:

  • Debounce input (250–500 ms) and cancel stale tasks so only the latest search runs.
  • Run the actual search async (off the UI thread) and only marshal the final result to the DataGridView.
  • Avoid rebinding the whole grid each time — apply a BindingSource/DataView filter or use virtual-mode updates so the control redraw is cheap.
    Example debounce pattern (WinForms / .NET):
private CancellationTokenSource _cts;

private async void txtSearch_TextChanged(object s, EventArgs e) {
  _cts?.Cancel();
  _cts = new CancellationTokenSource();
  try {
    await Task.Delay(350, _cts.Token);   // debounce
  } catch (TaskCanceledException) { return; }
  var q = txtSearch.Text;
  var results = await Task.Run(()=> RunSearch(q, _cts.Token), _cts.Token);
  bindingSource.DataSource = results;    // update UI
}

Debouncing + UI-side filtering/virtualization keeps the app responsive while you tune server work. (stackoverflow.com)

If you keep search server-side with MySQL full-text: ensure you have a proper FULLTEXT index and use MATCH(...) AGAINST(... IN BOOLEAN MODE) with the trailing * (truncation) for prefix matches — note * only works as a suffix (prefixing inside words/leading wildcards aren’t supported). Also be aware of minimum-word-length and stopword behavior (you must change ft_min_word_len / innodb_ft_min_token_size and rebuild indexes if you need shorter tokens). InnoDB requires a FULLTEXT index on all columns used by MATCH. (dev.mysql.com)

If you need substring/fuzzy/autocomplete at scale, evaluate a proper search engine (Sphinx, Lucene/Elasticsearch/Solr). They index text with inverted/ngrams and are designed for low-latency typeahead and complex matching; Sphinx, for example, plugs into MySQL easily and is built for this use. Start by implementing debounce + cancel + async fetch, try client-side filtering for immediate gains, then iterate toward full-text tuning or an external index if requirements demand it. (sphinxsearch.com)

Recommended Answers

All 3 Replies

Member Avatar for C#Jaap

I'm not quit clear what you mean with boolean full-text search and what you're doing? Do you have an column that containing "True" or "False"?
The most dynamic feeling can be created by loading the records into memory. I cannot see what limitations you are confronted with.

Member Avatar for catcit

I'm not quit clear what you mean with boolean full-text search and what you're doing? Do you have an column that containing "True" or "False"?
The most dynamic feeling can be created by loading the records into memory. I cannot see what limitations you are confronted with.

By Boolean Full-Text Search I mean this. Also, by 'dynamic search feeling' I meant real time search. My problem is that after each TextChanged event I`m trying to load the search results into a DataGridView and that takes a lot of time for that to be done. For example if one inserts character 'a' into the search textbox it will iterate through all the database and load into a datatable and then into the DataGridView all the lines that contain the character 'a'. Afterwards, if someone inserts another character and you get the string "as", for example, it will search into the database for all the lines from the database that contain the string "as" and then load those lines into the DataGridView. Due to these repeated iterations through all the database it takes a lot of time to load the search results into the DataGridView, and I`m searching for something that could speed up the search.

So far I have tried splitting the search into 11 separate threads, each one of the first 10 searching through 2000 lines at a time and the last one searching through what is left but that does not help that much either. I also found some search engines like Lucene or Sphinx but unfortunately I did not manage to find explicit documentation for any of them. As I said, any help is welcomed, either with the actual method of searching or with one of those search engines like the ones aforementioned.

Edited by catcit because: n/a
Member Avatar for C#Jaap

What you can do is only load your dataset when the first character is entered. (and cleared when the search string is empty). When the second and next characters are entered your filter your defaultView of the tables bindingsource:

TableBindingSource.Filter = string.Format("columnname LIKE '{0}%'", filtertext);
Edited by C#Jaap because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.