newbie question: max fields in a table

[dlazenby]

Just starting to use A5, v9, Plat.; (previously used MS Access)
I see that the max fields in a table is 1023.
In MS access you can only have 255 fields in a table.
Do people find that the DB bogs down when you get close to the field number limit in a table. Is it better to split it up into "smaller" tables?
Thanks

[DaveM]

Re: newbie question: max fields in a table

Doug,

We all seem to have our own preferences, but I keep mine around 100 as a guide. The problem is the size oth yhe tables on the hard drive. Windows wants less that 2 gigabytes.

[dlazenby]

Re: newbie question: max fields in a table

Dave,
Thanks for the info.

[Bob Arbuthnot]

Re: newbie question: max fields in a table

Hi Doug,

I don't believe I've heard that there is a limit to the number of fields per table. As Dave mentioned, there is a 2 Gigabyte limit to table size. There are several threads discussing this. One is here:

http://msgboard.alphasoftware.com/al...elds+per+table

Hope this helps.

Bob Arbuthnot

[Bill Griffin]

Re: newbie question: max fields in a table

As per the documentation (not sure how old or if this this is still the case, but this is from V9 docs)

Tables/Fields Fields per table record 1,023

Records per table 2,000,000,000

Bytes per record 19,600

Number of characters in a character field 255

Number of bytes in a memo field Unlimited

Size of OLE field Unlimited

Number of characters in a field name 32 (10 is recommended)

Number of calculated fields per table 512

Number of characters in a password 8

Number of tables in a set 40

[csda1]

Re: newbie question: max fields in a table

Hi Doug,

Welcome to the message board!

Do people find that the DB bogs down when you get close to the field number limit in a table. Is it better to split it up into "smaller" tables?

First of all, the answer is no, the DB does not slow down as you get close to the field number limit. Size of the record is almost irrelevant, because fixed structured files can be (well assuming good coding by Alpha in the underlying code - normally the case) very quickly accessed. Accessing records when using an index is also extremely fast.

However, if you need to read every record in the table in order to process something, you are limited to the speed of reading the raw data from the storage media (plus the overhead of processing), so bigger is not necessarily better

But while people have commented whether you could, the real question is whether you SHOULD? In general, tables should be normalized, and typically broken into their logical groups if a lot of fields. When you need to relate things, use a set.

If you really need a lot of fields in a record, maybe you are doing it wrong. Consider a different methodology. E.g. having fields indicating membership renewal for each year is wrong. Instead, have a child set that has the member ID, year and other renewal info and relate it to the the member info.

Calculated fields should not normally be stored in the database, but should be calculated on the fly (exceptions are for needed speed, posting issues, linking issues and similar).

One other thing. Bad coding, whether explicitly coded by you, or by using genies, can slow an application down to be almost unusable. Using good techniques will help circumvent this.

Hope this helps!