LARGE List/Combo Handling

[Steve101]

I am developing an application on an Access ADP fronent database on a SQL Server 7 backend.

My client is in the training business, where he has about 1000 (large corporate) customers, each of which has on average 30 employees, who are in fact Financial Planners, who are the recipients of our training and assessment.

These employees (say 30,000 people in total) can move around between companies, and may in fact be employed by more than one of these companies at a time.

I am considering the following simple structure:

- a Company table
- a People table
- an intersecting CompanyPeople table which shows the
relationship between companies and employees at any
one time.

Here are my questions:

(1) To minimise data entry problems, I want to check for existence of employees in the People table before adding a new entry. Is a list of 30,000 (say displaying 3 or 4 columns of associated data), with the AutoExpand option set to True, a "tall order", or is this a reasonable ask?

(2) Are the standard Microsoft Combo and List controls smart enough to only get the list/combo items as required, or am I looking at a large upfront overhead, as the list/combo is initially loaded?

(3) Should I be looking at 3rd party, more intelligent equivalent controls, which do "demand loading" of combo/list box content, or should I be "rolling my own" on an unbound control?

(4) I like the idea of using list/combo's for identifying whether the students exist, but is this approach valid given the numbers, or should I be using an unbound lookup approach to get better optimisation.

(5) At a guess, the student count could double in the next two to three years, so lets cap the number of records at say 100,000. We're talking about 20 online users, half doing enquiries, the rest some form of data entry.

(6) The application will for the most part be running on a LAN, but a small number of users may be coming in over a WAN (assume broadband) using a Citrix type connection.

Any opinions regarding a good high performance approach would be appreciated. I'm not a "large database" person, so input should be limited to people who are experienced in this area and environment.

Thanks in advance,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[fneily]

I have to smile when people think 100,000 is large. I used to work for AT&T and the smallest file was 2,500,000 records. We had files of 750,000,000.

I would have the tables you mentioned. Company with a primary key built from the company's name. People with a primary key built from their name. And a junction table CompanyPeople with both primary keys of the other tables as fields and they make up a multifield primary key for this table.

I would use what has been described as a roledex approach. I use a "letter pad", a form with the letters on buttons that will only bring up that part of the alphabet.
You could also have it scroll on maybe the first two letters.
Or you could create a "find" button. A user types in a name, clicks a button and it'll search the table. If it doesn't find it, then the form could go into entry mode.
You can also program the "Filter By Form" button that's on the standard form's toolbar to test for conditions.

Maybe others you see this have more ideas.

Neil

 

[Steve101]

Thanks Neil; yes I know 100,000 is not really "large", but in the environment where I'm used to using combo/list boxes of say 300-1500 say etc. its "large" enough to merit a possible change in approach.

Appreciate all that you say, and have thought about that sort of approach, ... but, what I'm really trying to establish is whether 100,000 is still sufficiently "small" to not have to merit the sort of approaches you talk about. Perhaps I was'nt succinct enough.

I will also do some prototyping and testing, but in particular I'm interested in whether others have used the standard bound controls on a similar sized database in a similar environment, and their experiences.

Many thanks for your input; and yes you're right, "large" is relative.

Cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MichaelRed]

combobox limit is ~~ ?32K? so you would already be on the 'bleeding" edge. Double the count and it goes over hte edge.

The speed / response time thing is more of a question of how it is coded than the actual conrtrol properties.

Dealing with "large" recordsets in Ms. A. takes a bit of patience -on the part of the designer/programmer. It becomes necessary to have and operate the app as a split db, and to generally have the FE reside on individual desktops. These consideration, in turn, complicate the maintenance aspects, particularly for the FE, as you need to update the FE more-or-less on demand, and w/o user intervention.

Further issues will arise in many situations, as the 'norm' for networks is to set up for the 'office' apps. Tuning for database operation is generally different and often 'neglected' unless some pressure (and a bit of knowledge and understanding) are applied to the problems which can arise.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CosmoKramer]

Personally, I like to use your option 4 from above: an unbound lookup approach to get better optimisation.

I have written a time and attendance system for my employer (only about 1,500 employees) and I utilized a DLookup approach instead of the list/combo box method. The reason really wasn't run time, it was code optimization.

If the user must actually look to see if an employee exists in a list box before they attempt to add them, you also have to code for the lazy ones who don't look, or the optically challenged who don't see it. Why not have them enter the employee ID in an unbound text box and use DLookup to search the table for it?? If the employee exists, allow editing, if not, ask if they want to add the employee. I have found that DLookup on a properly indexed table is fast enough.....

 

[DonQuichote]

As you are using a database server for a backend, you can use pass-through queries. This will cause the queries to be handled on the server instead of the client, saving a lot of network traffic. There's enough info on them in tek-tips...

Best regards

 

[Steve101]

Many thanks all for your input.

Michael, you sort of come closest to what I'm trying to establish, though I dont understand the 32K limitation you talk about.

I've created a combo box, fed from sql from a local table, which returns about 20,000 rows, with about 50 characters per row. This makes 1 million characters. I presume that your 32K limit relates to the number of entries in the combo, so I'll double up the rows and test again. Anyway, on the 20K rows above, with the fields appropriately indexed, the speed is very good ... still needs to be tried on a SQL Server backend over a network ...

Does the combo box retrieve all entries upfront, or does it do 'load on demand' filling. I realise that I can control this if I use an unbound control ... but how does the bound mechanism work. I guess I'll figure this out as I play more, but if anyone has ready answers to this question, it would be appreciated.

Cheers,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[CosmoKramer]

Steve,

In taking future growth into account, you may want to consider this limitation:

Microsoft Access limits the number of rows displayed in a combo box or a list box to 65,536 rows.

http://support.microsoft.com/default.aspx?scid=kb;en-us;207601

 

[MichaelRed]

OOps - not 32K but 64K. Sorry about the err. concept still applies.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Steve101]

Thanks all for your input; much appreciated:

Michael: By a 32K limit I presume you mean 32K entries. I'm currently testing with 20,000 entries, and the performance on a local database is very good.

Thanks also Cosmo, and Don for your input.

Cheers,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

oops, just seen the prior posts about the number of rows limit.

thanks again all,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)