By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Table efficiency

P: n/a
How many fields can a table have before it is considered inefficient?

The table's records will eventually number in the thousands (about 3000 per year).
Nov 12 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
I think you slipped on the keyboard!

A table can only have a max of 255 fields.

However, I am guessing that you actually meant to say how many records can
you put in a JET mdb file share before it slows down too much?

Hum, well, your designs are good, and you have a number of years of
experience in developing database applications, then you will that ms-access
runs very well, even when the tables size get to be 100,000 records.

I have few applications that generally have about 5 users on a network
share, about 60 tables (many heavily related to others), and tables sizes
are in the 30,000 to 40,000 record range, and it runs very well. So, when
you have small tables in the 1 to 100,000 record range, ms-access performs
VERY WELL indeed.

A table of 100,000 records is not really that large for ms-access.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
No************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 12 '05 #2

P: n/a
na**************@hotmail.com (Nathan Bloomfield) wrote in
news:4b**************************@posting.google.c om:
How many fields can a table have before it is considered inefficient?

The table's records will eventually number in the thousands (about 3000
per year).


I think there are many questions about data that influence the number of
fields that may be appropruate.

Here's one.

Suppose you have a table of people.

And those people have addresses.

And I am one of the people.

If the address field(s) are part of the people table, and I move, then you
will, I assume, change my address. Fine. You can send me mail. But what if
you need a record of addresses to which you sent mail in 2002? My 2002
address is gone. So you may not have the information that you need. A child
table of addresses with an [effective date] field may give your db more
power. And a table of addresses linked via another table may give you more
power again. For instance, it could prevent double mailings to the same
address. Some years ago I was given the problem of keeping track of e-mail
sends of thousands each day. What I finally did was to keep the query and
the input parameters for each "posting", and time stamp all addresses. So,
I could see what was done a year ago, just by rerunning the query with the
parameters which I had saved.

About a year ago I stopped thinking in Tables and started thinking in
Columns, with the rows of columns having unique identifiers, and the
columns being organized in various useful Views. I believe this is where DB
design is going (or maybe it's already here).

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #3

P: n/a
rkc

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
na**************@hotmail.com (Nathan Bloomfield) wrote in
news:4b**************************@posting.google.c om: About a year ago I stopped thinking in Tables and started thinking in
Columns, with the rows of columns having unique identifiers, and the
columns being organized in various useful Views.
I believe this is where DB
design is going (or maybe it's already here).


Sounds like relations to me.

Nov 12 '05 #4

P: n/a
na**************@hotmail.com (Nathan Bloomfield) wrote in message news:<4b**************************@posting.google. com>...
How many fields can a table have before it is considered inefficient?

The table's records will eventually number in the thousands (about 3000 per year).


Max fields per table is 255. Do you *really* have an entity with that
many attributes that you need to model? 10K records is not that
many...
Nov 12 '05 #5

P: n/a
na**************@hotmail.com (Nathan Bloomfield) wrote:
How many fields can a table have before it is considered inefficient?

The table's records will eventually number in the thousands (about 3000 per year).


A client has at least four tables in their MDB with over 200,000 records in each.
There are about 150 or so tables. MDB is about 300 Mb or so in size.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #6

P: n/a
Lyle -

Your column/ID/view approach sounds like Object-Role Modeling (ORM).

An alternative to Entity-Relationship modeling that is better at
capturing business rules but rather complex and academic. There is (or
used to be) a CASE tool called InfoModeler that uses ORM.

A Google search for 'Object-Role Modeling' will give you lots of
references.

- Brian

On 22 Dec 2003 01:28:37 GMT, Lyle Fairfield
<Mi************@Invalid.Com> wrote:
na**************@hotmail.com (Nathan Bloomfield) wrote in
news:4b**************************@posting.google. com:
How many fields can a table have before it is considered inefficient?

The table's records will eventually number in the thousands (about 3000
per year).
I think there are many questions about data that influence the number of
fields that may be appropruate.

Here's one.

Suppose you have a table of people.

And those people have addresses.

<snip>About a year ago I stopped thinking in Tables and started thinking in
Columns, with the rows of columns having unique identifiers, and the
columns being organized in various useful Views. I believe this is where DB
design is going (or maybe it's already here).


Nov 12 '05 #7

P: n/a
pi********@hotmail.com (Pieter Linden) wrote in
<bf**************************@posting.google.com >:
na**************@hotmail.com (Nathan Bloomfield) wrote in message
news:<4b**************************@posting.google .com>...
How many fields can a table have before it is considered
inefficient?

The table's records will eventually number in the thousands
(about 3000 per year).


Max fields per table is 255. Do you *really* have an entity with
that many attributes that you need to model? 10K records is not
that many...


Why are people reading this message as asking about lots of fields?
It clearly says 3000 *records*, not fields.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #8

P: n/a
rkc

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74.. .
pi********@hotmail.com (Pieter Linden) wrote in
<bf**************************@posting.google.com >:
na**************@hotmail.com (Nathan Bloomfield) wrote in message
news:<4b**************************@posting.google .com>...
How many fields can a table have before it is considered
inefficient?

The table's records will eventually number in the thousands
(about 3000 per year).
Max fields per table is 255. Do you *really* have an entity with
that many attributes that you need to model? 10K records is not
that many...


Why are people reading this message as asking about lots of fields?
It clearly says 3000 *records*, not fields.


You need to slow down a bit and read what's written.

The op asked:
How many fields can a table have before it is considered inefficient?


That is a question about lots of fields.


Nov 12 '05 #9

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote:
>> How many fields can a table have before it is considered
>> inefficient?
>>
>> The table's records will eventually number in the thousands
>> (about 3000 per year).
You need to slow down a bit and read what's written.

The op asked:
How many fields can a table have before it is considered inefficient?


That is a question about lots of fields.


In my opinion the original poster meant to say records in the first sentence and not
fields.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #10

P: n/a
rkc

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:8m********************************@4ax.com...
"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote:
>> How many fields can a table have before it is considered
>> inefficient?
>>
>> The table's records will eventually number in the thousands
>> (about 3000 per year).
You need to slow down a bit and read what's written.

The op asked:
How many fields can a table have before it is considered inefficient?


That is a question about lots of fields.


In my opinion the original poster meant to say records in the first

sentence and not fields.


One could interpret the question in a lot of ways. They may actually have
been asking how many records does it take to make a table with lots of
fields inefficient. Logic would say it would take more records to make
a two field table inefficient than it would to make a 255 field table
inefficient.

Any way it's interpreted, there's no answer to the question.


Nov 12 '05 #11

P: n/a
On Sun, 21 Dec 2003 19:43:04 -0800 in comp.databases.ms-access,
baustin <ba************@aol.com> wrote:
Lyle -

Your column/ID/view approach sounds like Object-Role Modeling (ORM).

An alternative to Entity-Relationship modeling that is better at
capturing business rules but rather complex and academic. There is (or
used to be) a CASE tool called InfoModeler that uses ORM.


That was Asymetrics (sp?), MS bought it and now it's part of Visio
Enterprise Architect.

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #12

P: n/a
rk*@yabba.dabba.do.rochester.rr.nope (rkc) wrote in
<ef*******************@twister.nyroc.rr.com>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74. ..
pi********@hotmail.com (Pieter Linden) wrote in
<bf**************************@posting.google.com >:
>na**************@hotmail.com (Nathan Bloomfield) wrote in
>message
>news:<4b**************************@posting.google .com>...
>> How many fields can a table have before it is considered
>> inefficient?
>>
>> The table's records will eventually number in the thousands
>> (about 3000 per year).
>
>Max fields per table is 255. Do you *really* have an entity
>with that many attributes that you need to model? 10K records
>is not that many...
Why are people reading this message as asking about lots of
fields? It clearly says 3000 *records*, not fields.


You need to slow down a bit and read what's written.


I did, whereas it seems that no one else did so.
The op asked:
How many fields can a table have before it is considered
inefficient?


That is a question about lots of fields.


No it is a question about an unspecified number of fields, maybe 3,
maybe 300.

And it was followed by a second question about number of records.

Most of the followups responded as though the question read "can I
have 3000 fields in a table?"

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #13

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote in news:efKFb.50566
$U********@twister.nyroc.rr.com:
The op asked:
How many fields can a table have before it is considered inefficient?


That is a question about lots of fields.


Yes, it is.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #14

P: n/a
Tony Toews <tt****@telusplanet.net> wrote in message news:<8m********************************@4ax.com>. ..
"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote:
>> How many fields can a table have before it is considered
>> inefficient?
>>
>> The table's records will eventually number in the thousands
>> (about 3000 per year).

You need to slow down a bit and read what's written.

The op asked:
How many fields can a table have before it is considered inefficient?


That is a question about lots of fields.


Thanks everyone for your comments. Obviously I should have made my
post clearer. I am indeed looking for advice on the "FIELDS". I have
a table which is nearing 100 fields * 3000 records per year.

Basically I have no formal training, and therefore I was interesting
in peoples experience in possible design flaws. I was aware that 255
is the max fields - but I am not sure if there is a recommended limit.

I am using queries to append data over a WAN so it makes it convenient
to use a single table for the "raw data".

The database covers daily KPIs for a logistics company whereby each
department may use up to 20 fields - some shared and some unique to
each department.

At this stage with only 5000 records the database is running very
smoothly. My concern is that with 90% of data being sourced from a
single table that queries will become painfully slow in the future.

Thanks again,

Nathan
Nov 12 '05 #15

P: n/a
na**************@hotmail.com (Nathan Bloomfield) wrote in
news:4b**************************@posting.google.c om:
Tony Toews <tt****@telusplanet.net> wrote in message news: <8m********************************@4ax.com>...
"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote:
>> >> How many fields can a table have before it is considered
>> >> inefficient?
>> >>
>> >> The table's records will eventually number in the thousands
>> >> (about 3000 per year).

>You need to slow down a bit and read what's written.
>
>The op asked:
>
>> How many fields can a table have before it is considered inefficient?
>
>That is a question about lots of fields.


Thanks everyone for your comments. Obviously I should have made my
post clearer. I am indeed looking for advice on the "FIELDS". I have
a table which is nearing 100 fields * 3000 records per year.

Basically I have no formal training, and therefore I was interesting
in peoples experience in possible design flaws. I was aware that 255
is the max fields - but I am not sure if there is a recommended limit.

I am using queries to append data over a WAN so it makes it convenient
to use a single table for the "raw data".


<snips>
Thanks again,

Nathan


I thought that it was quite clear. You wrote, "fields". You meant,
"fields". And you were concerned about whether or not many fields became
inefficient as record numbers increased.

The efficiency of scripting and programming is that we write rules and
instructions once, and the computer carries them out many times. Many
developers apply this kind of solution to the problem of appending data
over a WAN, rather than using one table, which design may lead to bloat,
reduncancy, repetition and very much less power and capability than a
properly designed normalized solution.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.