what is best practice to populate large combobox?

Hello,

I have a search application to search data in tables in a database (3 sql
server tables). I populate 2 comboboxes with with data from each table. One
combobox will contain unique CompanyID's. The second combobox will contain
unique memberID's. Each of the tables that I have to search contain a
CompanyID and a memberID field, and these fields are not unique in the
respective tables. Like

CompanyID, MemberID
Clorox Smith

can exist several times in a table. Each table contains hundreds of
thousands to millions of rows.

Right now I use a dataAdapter to fill a dataTable (for each combobox) with
unique/distinct CompanyID and fill a combobox with unique/distinctMemberID
data for each table to be searched. Then I base the combo1.Datasource =
dataTable1.... When I switch tables, I was having to repopulate the
datasource tables for each combobox. Each combobox will contain around
40,000 unique ID's per table to be searched. This was taking way too long.
So then I thought since I have to search 3 Tables (each contains CompanyID
and MemberID fields), I would populate 6 dataTables when the application
first opens up. Then I could switch the datasource of each combobox as
needed. Ths was still taking a while. So my last effort was to have 6
individual comboboxes (placed on top of each other) populate them all at the
opening of the application and make them visible only according to the table
being searched at the time. But now I have all this data in memory.

If memory isn't an issue (like each workstation that would use this search
app would contain 2 gigs of memory a piece) would loading up 6 comboboxes
with 40,000 items apiece all at once be a reasonable practice? Or if there
is a better/more correct way to do this - could someone tell me what this way
is?

Thanks,
Rich

What is the likelyhood that someone will try to scroll through your 40,000
items in the combobox? Is it possible to replace the combobox with a search
algorhythm whereby the user enters the desired search criteria in a dialog
box and only bring up the filtered results. Alternatively, you can use a
text box and then fill the suggested items after they have entered a number
of characters, returning a filtered list at that point. Having a user wade
through 40,000 records is unrealistic from their standpoint and a performance
hog from yours. Even if they have enough ram to hold the records, think about
the required network bandwidth to transfer that many records.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

> Hello,
>
> I have a search application to search data in tables in a database (3
> sql server tables). I populate 2 comboboxes with with data from each
> table. One combobox will contain unique CompanyID's. The second
> combobox will contain unique memberID's. Each of the tables that I
> have to search contain a CompanyID and a memberID field, and these
> fields are not unique in the respective tables. Like
>
> CompanyID, MemberID
> Clorox Smith
> can exist several times in a table. Each table contains hundreds of
> thousands to millions of rows.
>
> Right now I use a dataAdapter to fill a dataTable (for each combobox)
> with unique/distinct CompanyID and fill a combobox with
> unique/distinctMemberID data for each table to be searched. Then I
> base the combo1.Datasource = dataTable1.... When I switch tables, I
> was having to repopulate the datasource tables for each combobox.
> Each combobox will contain around 40,000 unique ID's per table to be
> searched. This was taking way too long. So then I thought since I
> have to search 3 Tables (each contains CompanyID and MemberID fields),
> I would populate 6 dataTables when the application first opens up.
> Then I could switch the datasource of each combobox as needed. Ths
> was still taking a while. So my last effort was to have 6 individual
> comboboxes (placed on top of each other) populate them all at the
> opening of the application and make them visible only according to the
> table being searched at the time. But now I have all this data in
> memory.
>
> If memory isn't an issue (like each workstation that would use this
> search app would contain 2 gigs of memory a piece) would loading up 6
> comboboxes with 40,000 items apiece all at once be a reasonable
> practice? Or if there is a better/more correct way to do this - could
> someone tell me what this way is?
>
> Thanks,
> Rich

Hi Jim,

Thank you for your reply. In response to your questions, there are a
couple of hard core data processing people at my place. They know all 40,000
of the companies and members that my places deals with. The thing with the
combobox is that the data processing people can type in part of a name (ID)
in the combobox and the dropdown of the combobox will display a list of all
names that are close to what they typed in. This is pretty nice
functionality.

I have, in fact, considered your suggestion of typing in a few chars and
then autopopulating a dialog box with a filtered name. That would be the
most ideal. But would this be more realistic than than the combobox approach
against tables with hundreds of thousands of rows? If it is realistic, may I
request a simple example of how to implement something like this?

Select ID from tbl1 where ID Like '" & txtID.Text & "%'"

which would returns several rows which I could read with sqlDataReader and
further filter. Except I would have to run this for every keystroke in
textID. But if you think this is better than the combobox method, I would
certainly give it a shot.

Thanks,
Rich

"Jim Wooley" wrote:

> What is the likelyhood that someone will try to scroll through your 40,000
> items in the combobox? Is it possible to replace the combobox with a search
> algorhythm whereby the user enters the desired search criteria in a dialog
> box and only bring up the filtered results. Alternatively, you can use a
> text box and then fill the suggested items after they have entered a number
> of characters, returning a filtered list at that point. Having a user wade
> through 40,000 records is unrealistic from their standpoint and a performance
> hog from yours. Even if they have enough ram to hold the records, think about
> the required network bandwidth to transfer that many records.
>
> Jim Wooley
> http://devauthority.com/blogs/jwooley/default.aspx
>
> > Hello,
> >
> > I have a search application to search data in tables in a database (3
> > sql server tables). I populate 2 comboboxes with with data from each
> > table. One combobox will contain unique CompanyID's. The second
> > combobox will contain unique memberID's. Each of the tables that I
> > have to search contain a CompanyID and a memberID field, and these
> > fields are not unique in the respective tables. Like
> >
> > CompanyID, MemberID
> > Clorox Smith
> > can exist several times in a table. Each table contains hundreds of
> > thousands to millions of rows.
> >
> > Right now I use a dataAdapter to fill a dataTable (for each combobox)
> > with unique/distinct CompanyID and fill a combobox with
> > unique/distinctMemberID data for each table to be searched. Then I
> > base the combo1.Datasource = dataTable1.... When I switch tables, I
> > was having to repopulate the datasource tables for each combobox.
> > Each combobox will contain around 40,000 unique ID's per table to be
> > searched. This was taking way too long. So then I thought since I
> > have to search 3 Tables (each contains CompanyID and MemberID fields),
> > I would populate 6 dataTables when the application first opens up.
> > Then I could switch the datasource of each combobox as needed. Ths
> > was still taking a while. So my last effort was to have 6 individual
> > comboboxes (placed on top of each other) populate them all at the
> > opening of the application and make them visible only according to the
> > table being searched at the time. But now I have all this data in
> > memory.
> >
> > If memory isn't an issue (like each workstation that would use this
> > search app would contain 2 gigs of memory a piece) would loading up 6
> > comboboxes with 40,000 items apiece all at once be a reasonable
> > practice? Or if there is a better/more correct way to do this - could
> > someone tell me what this way is?
> >
> > Thanks,
> > Rich
>
>
>

If you are using 2.0, look at the AutoCompleteSource property of the ComboBox
and TextBox. You can fill it when X number of characters have been typed
and not re-query unless the first x characters are changed.
Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

> Hi Jim,
>
> Thank you for your reply. In response to your questions, there are a
> couple of hard core data processing people at my place. They know all
> 40,000 of the companies and members that my places deals with. The
> thing with the combobox is that the data processing people can type in
> part of a name (ID) in the combobox and the dropdown of the combobox
> will display a list of all names that are close to what they typed in.
> This is pretty nice functionality.
>
> I have, in fact, considered your suggestion of typing in a few chars
> and then autopopulating a dialog box with a filtered name. That
> would be the most ideal. But would this be more realistic than than
> the combobox approach against tables with hundreds of thousands of
> rows? If it is realistic, may I request a simple example of how to
> implement something like this?
>
> Select ID from tbl1 where ID Like '" & txtID.Text & "%'"
>
> which would returns several rows which I could read with sqlDataReader
> and further filter. Except I would have to run this for every
> keystroke in textID. But if you think this is better than the
> combobox method, I would certainly give it a shot.
>
> Thanks,
> Ric

Thanks. The autocomplete property is what I was missing. Yes, I have .Net
2.0. this is nice. Now I have something.

Thanks for your help.

Rich

"Jim Wooley" wrote:

> If you are using 2.0, look at the AutoCompleteSource property of the ComboBox
> and TextBox. You can fill it when X number of characters have been typed
> and not re-query unless the first x characters are changed.
> Jim Wooley
> http://devauthority.com/blogs/jwooley/default.aspx
>
> > Hi Jim,
> >
> > Thank you for your reply. In response to your questions, there are a
> > couple of hard core data processing people at my place. They know all
> > 40,000 of the companies and members that my places deals with. The
> > thing with the combobox is that the data processing people can type in
> > part of a name (ID) in the combobox and the dropdown of the combobox
> > will display a list of all names that are close to what they typed in.
> > This is pretty nice functionality.
> >
> > I have, in fact, considered your suggestion of typing in a few chars
> > and then autopopulating a dialog box with a filtered name. That
> > would be the most ideal. But would this be more realistic than than
> > the combobox approach against tables with hundreds of thousands of
> > rows? If it is realistic, may I request a simple example of how to
> > implement something like this?
> >
> > Select ID from tbl1 where ID Like '" & txtID.Text & "%'"
> >
> > which would returns several rows which I could read with sqlDataReader
> > and further filter. Except I would have to run this for every
> > keystroke in textID. But if you think this is better than the
> > combobox method, I would certainly give it a shot.
> >
> > Thanks,
> > Rich
>
>
>

I have a similiara situation and I use a treevew control with the first level
being the letters if the alphabet and a folder icon. When the user clicks on
a folder, I load that node with sub nodes from all members starting with the
leter on the folder. This way, I only have to populate those nodes that the
user is interested in. Much quicker.
--
Dennis in Houston


"Rich" wrote:

> Thanks. The autocomplete property is what I was missing. Yes, I have .Net
> 2.0. this is nice. Now I have something.
>
> Thanks for your help.
>
> Rich
>
> "Jim Wooley" wrote:
>
> > If you are using 2.0, look at the AutoCompleteSource property of the ComboBox
> > and TextBox. You can fill it when X number of characters have been typed
> > and not re-query unless the first x characters are changed.
> > Jim Wooley
> > http://devauthority.com/blogs/jwooley/default.aspx
> >
> > > Hi Jim,
> > >
> > > Thank you for your reply. In response to your questions, there are a
> > > couple of hard core data processing people at my place. They know all
> > > 40,000 of the companies and members that my places deals with. The
> > > thing with the combobox is that the data processing people can type in
> > > part of a name (ID) in the combobox and the dropdown of the combobox
> > > will display a list of all names that are close to what they typed in.
> > > This is pretty nice functionality.
> > >
> > > I have, in fact, considered your suggestion of typing in a few chars
> > > and then autopopulating a dialog box with a filtered name. That
> > > would be the most ideal. But would this be more realistic than than
> > > the combobox approach against tables with hundreds of thousands of
> > > rows? If it is realistic, may I request a simple example of how to
> > > implement something like this?
> > >
> > > Select ID from tbl1 where ID Like '" & txtID.Text & "%'"
> > >
> > > which would returns several rows which I could read with sqlDataReader
> > > and further filter. Except I would have to run this for every
> > > keystroke in textID. But if you think this is better than the
> > > combobox method, I would certainly give it a shot.
> > >
> > > Thanks,
> > > Rich
> >
> >
> >