Best way of searching through a DataGridView in VB.NET?

Question

Hi peeps,

On my form there is a datagridview that may get upwards to about 70k items depending on how scan happy our customers get. I also have a textbox which allows the user to search the datagridview using the textchanged event. I'm using a select statement with the like clause and filling the dataset. I dont think this will cut it, because the program hangs and gets laggy when there is a large amount of records. Whats the best way I can do this if performance is a top priority?

Answer 1

The obvious way to improve performance is to filter at the click of a button instead of the textchanged event which gets called often.

If you don't want to change the way it works, another option would be to not start the search until a number of characters are typed, and then wait at least x seconds before firing another search.

Answer 2

The best way i've found is to actually run the SQL query on another thread, store the dataset in memory, and have a timer going adding perhaps 10 records per millisecond. This way the UI still fills up, but the main processor isn't busy drawing the whole time and your user can still type. This can be scaled up and down depending on the computers its running on. I've done this with Listboxes and combo boxes and its fairly simple (if you count using delegates and invokes as simple).

This should give you the performance you desire... that is, if you have time enough to implement it.

~~~~~~~~~~~~~~~~~~~~Edit~~~~~~~~~~~~~~~~~~~~

If you wish, I could potentially post the sample code for the listbox version of this since I haven't actually seen anyone else on the net do it.

Answer 3

Why return all the records? Implement server-side paging, so only 10/20/50/100 (let the user decide) records are returned at a time.

Answer 4

If you aren't worried about the number of records being selected, just download the entire dataset.

Create a dataview object on your filled table.

Set the DataGridView.DataSource = your dataview object.

Then, instead of using the textbox's textchanged event to fill the dataset, use it to change the .RowFilter property on your dataview.

Answer 5

To elaborate on Meta-Knights answer: I had an issue similiar to yours and decided to go with a timer that started whenever the user typed a key. I had it set for 2 seconds because my users were usually reading information as they were typing. It seems to work pretty well and the users are very happy with the auto-searching feature.

Essentially the pseudo-code is as follows:

Sub TextChanged()  
   MyTimer.Enabled= TextBox.TextLength > 0  
End Sub

Sub TimerTick()  
   MyTimer.Enabled=False  
   Lookup(TextBox.Text)  
   MyTimer.Enabled=True  
End Sub