dataset & datagrids speed

[Phylum]

If I have a table that contains 400,000 records and I need to do a lookup on said table, am I going to hit a speed issue because of the nature of a dataset? To the best of my knowledge a dataset is disconnected and therefore any select command excuted against the source will return ALL of the rows requested at once, to the client machine, where they will reside in memory. One object that comes to mind, which is faster would be a datareader. Unfortunately a datagrid (best suited for lookup type situations) cannot be bound to a datareader. Is there a better alternative to creating a lookup?

 

Phylum

[Nerseus]

If you're binding to a combo's dropdown, you have a couple of options. You can basically divide your binding (or just filling) into two basic thoughts - download everything at once, or download on demand.

 

If you download all at once, you could use a DataReader or DataSet but they're going to be about the same in your case since you really need to wait til all the data is read before showing the form.

 

If you download on demand, you'll have to decide when to do it and what you want. For instance, does the user really need to choose from 400,000 records? Or can they enter a character or two (or more) and then filter? A common solution is to check for a pause after a character (or 2 or 3) has been pressed and then load a filtered list to the combo.

 

From my experience, combos are used in two ways. Either they're filled with a few values that the user doesn't want to type (such as a description), or they're filled with some kind of code + description, such as "UD112 - Broken Arm". In the former, there's no problem filling in advance because there will never be much data. In the latter you'll need to find out from the user's what they want. Normally a textbox is fine - they enter "UD112" and tab out. When they leave the textbox you can do a lookup and fill a label with the description "Broken Arm" so that they can verify the right code. If they really need it, you could provide a button to do a popup (search and select type of thing) or a "Fill Dropdown" type of button. Normally on data entry for code-related fields, the users know all the codes they're entering and don't need the dropdown filled at all.

 

Back to your main issue:

If you find a DataReader going much faster, you can always populate an ArrayList object and bind a combo to that.

 

-Ner