What is the max number of fields in a Progress table

[Dmitri Levin]

OE 10.2B07

I seems could not find an answer for that question. Some of our tables have close to 200 fields, so how much is enough looks like a reasonable question to me. I did a test with sports database, I was able to load a .df of a table with 1505 fields. But could not load .df with 1506. So I assume 1505 is a limit. Well, that is enough for me at least. Here is how the bottom of my table looks like

Order Field Name                       Data Type   Flags
----- -------------------------------- ----------- -----
  10 field1                           char        i

15020 field1502                        char                                  
15030 field1503                        char                              
15040 field1504                        char                              
15050 field1505                        char                              

Field Name                       Format
----- --------------------------
field1504                        x(5)
field1505                        x(5)

Interesting that when I load a table .df with 1505 fields it has at the end alert-box "Load is completed"
When I load .df with 1506 fields it does not show the alert-box, just silently end it's work. 
And nothing is added to the schema.

/* program to create a df file */
def var i as i.
output to /tmp/test.df.
put unformatted "ADD TABLE ""test""" skip.
put unformatted "  AREA ""Info Area""" skip.
put unformatted "  DUMP-NAME ""test""" skip.
put unformatted "  " skip.
do i = 1 to 1505:
put unformatted "ADD FIELD ""field" + string(i) +
  """ OF ""test"" AS character" skip.
put unformatted "  FORMAT ""x(5)""" skip.
put unformatted "  INITIAL """"" skip.
put unformatted "  POSITION " + string(i + 1) skip.
put unformatted "  MAX-WIDTH 10" skip.
put unformatted "  ORDER " + string(i * 10) skip.
put unformatted "  " skip.
end.
put unformatted "ADD INDEX ""Prim-idx"" ON ""test""" skip.
put unformatted "  AREA ""Info Area""" skip.
put unformatted "  PRIMARY" skip.
put unformatted "  INDEX-FIELD ""field1"" ASCENDING" skip.
put unformatted "  " skip.
put unformatted "." skip.
put unformatted "PSC" skip.
put unformatted "cpstream=ISO8859-1" skip.
put unformatted "." skip.
put unformatted "0000000405" skip.
output close.

Any one could try.

Dmitri Levin
alphabroder Co.

[Thomas Mercer-Hursh, Ph.D.]

Isn't this going to depend on the types of the fields?

[Chris Ruprecht]

imho tables with that many fields are unmanageable and often contain information that would better be stored in a sub-table. you might argue that having sub-records leads to more disk reads, but if you write your application right, that won't happen. 1500 fields will never fit into a db block, i don't know off the top of my head how many bits are needed for an empty field, but i know somebody who does. and if you have data in those fields it's a different story.

Best regards,
Chris
--
Chris Ruprecht · Email: crup...@progress.com · Direct number: 678.225.6327

[Rob Fitzpatrick]

From the docs:

"Tables have a maximum number of field definitions: SQL supports 500, ABL supports 1000."

So if there's a bug here it's that you're able to load (or appear to load) more field definitions than you should for a single table.

Is there a problem you're trying to solve or is this just intellectual curiosity?

Rob Fitzpatrick

[Gus Bjorklund]

there is no defined maximum number.

the effective limit is the (just under) 32k byte /row size/. it doesnt
mater how many fields that is.

in the best case, each variable-length field value requires 1 byte when
the value is Unknown (same as NULL in SQL) (i know, that¹s not useful).
more when it is not Unknown. the data type and the value together
determine how much space is needed for a single field value. on top of
that there is some overhead (record length, schema version info, skip
table, etc.) that lowers the limit a bit below 32kb.

--
regards,
gus bjorklund

"Qu'on me donne six lignes écrites de la main du plus honnête homme, j'y
trouverai de quoi le faire pendre." -- Cardinal Richelieu.

[Gus Bjorklund]

On 2/27/15, 4:52 PM, "Rob Fitzpatrick" <ro...@stratinfotech.com> wrote:


>"Tables have a maximum number of field definitions: SQL supports 500, ABL
>supports 1000."
>

docs is wrong.

-gus

[Rob Fitzpatrick]

And KB is wrong...
http://knowledgebase.progress.com/articles/Article/000039694/p

[George Potemkin (peg)]

Dmitri,

When I set the -s 30000 then I was able to load the test.df with 5,000
fields.

find first _File where _File-name eq "test".
message record-length(_File) length(_File._Field-Map)
view-as alert-box info buttons OK.

It returns: 26306 21141

When I tried to load the test.df with 10,000 fields I got the error 7996:
SYSTEM ERROR: Unable to update the Field Map for table 'test'. (7996)

Obviously the size of _File record exceeds 32,000 bytes.

But the "test" table with 5,000 fields also has a problem.
The following code crashes the session:
FIND FIRST test NO-LOCK NO-ERROR.

"Progress Client has stopped working"

10.2B on Windows

Regards,
George Potemkin
Progress Technologies
S.-Petersburg, Russia
Phone: +7 (812) 438 2757
http://www.progress-tech.ru


--------------------------------------------------
From: "Dmitri Levin" <d_l...@hotmail.com>
Sent: Saturday, February 28, 2015 12:34 AM
To: <d...@peg.com>
Subject: What is the max number of fields in a Progress table

[Dmitri Levin]

I was just curios. And I understand that the question is more theoretical then practical. Nobody is going to create table with 1505 fields in production. Especially on Friday :)
The docs are definitely wrong as my table with 1505 all character fields works fine. Create, For each, all works fine.

I repeated the tests with INTEGER and LOGICAL. Regardless of the type I was able to create a table in sports database with 1505 fields and was not able to create table with 1506 fields.

Everyone can repeat that very simple test on a copy of a sports database, 

/* code to create .df with 1505 logical fields */

def var i as i.
output to /tmp/test.df.
put unformatted "ADD TABLE ""test""" skip.
put unformatted "  AREA ""Info Area""" skip.
put unformatted "  DUMP-NAME ""test""" skip.
put unformatted "  " skip.
do i = 1 to 1505:
put unformatted "ADD FIELD ""field" + string(i) +

  """ OF ""test"" AS logical" skip.
put unformatted "  FORMAT ""yes/no""" skip.
put unformatted "  INITIAL ""no""" skip.

put unformatted "  POSITION " + string(i + 1) skip.

put unformatted "  MAX-WIDTH 1" skip.

put unformatted "  ORDER " + string(i * 10) skip.
put unformatted "  " skip.
end.
put unformatted "ADD INDEX ""Prim-idx"" ON ""test""" skip.
put unformatted "  AREA ""Info Area""" skip.
put unformatted "  PRIMARY" skip.
put unformatted "  INDEX-FIELD ""field1"" ASCENDING" skip.
put unformatted "  " skip.
put unformatted "." skip.
put unformatted "PSC" skip.
put unformatted "cpstream=ISO8859-1" skip.
put unformatted "." skip.
put unformatted "0000000405" skip.
output close.

Dmitri Levin
alphabroder Co.



----------------------------------------
> From: ro...@stratinfotech.com
> To: d_l...@hotmail.com; d...@peg.com
> Subject: RE: What is the max number of fields in a Progress table
> Date: Fri, 27 Feb 2015 21:52:59 +0000

[Dmitri Levin]

Thanks George. I did not think about -s. I was also able to create a table 5300 fields.

Dmitri Levin
alphabroder Co.



----------------------------------------

> From: gpot...@progress-tech.ru
> To: d_l...@hotmail.com; d...@peg.com
> Subject: Re: What is the max number of fields in a Progress table
> Date: Sat, 28 Feb 2015 02:30:09 +0300

[Chris Ruprecht]

yeah, it doesn't mean just because it's possible, it should be done :).
and with that i'm going back to watch some movies.

[George Potemkin (peg)]

> What is the max number of fields in a Progress table

The answer: approximately 6400.

http://knowledgebase.progress.com/articles/Article/000049586
"The Field map has the array for the initial value"

In fact the size of _Field-Map is a few times larger than the size of
template record (_File._Template).
I run the following code against sports db:

for each _File no-lock where _File._Field-Map ne ?:
display
_File._File-Name
_File._numfld
length(_File._Field-Map) / _File._numfld.
end.

The ration length(_File._Field-Map) to _File._numfld varies from 4.0 to 5.5.
The size of _File record can't exceed 32000 bytes.
Hence the size of _Field-Map should be less than 32000 bytes.
Hence the max number of the fields is less than 32000 / 5.

I wrote:
> But the "test" table with 5,000 fields also has a problem.
> The following code crashes the session:
> FIND FIRST test NO-LOCK NO-ERROR.
>
> "Progress Client has stopped working"

And dbanalys also failed with the error:

RECORD BLOCK SUMMARY
Internal error in printTableStats, errno 4. (11158)

The error has disappeared after deletion of the "test" table.

So probably the right answer to the question in the subject is "less than
5000".

Regards,
George Potemkin
Progress Technologies
S.-Petersburg, Russia
Phone: +7 (812) 438 2757
http://www.progress-tech.ru

--------------------------------------------------

From: "George Potemkin (peg)" <gpot...@progress-tech.ru>
Sent: Saturday, February 28, 2015 2:30 AM
To: "Dmitri Levin" <d_l...@hotmail.com>; <d...@peg.com>
Subject: Re: What is the max number of fields in a Progress table

[Dmitri Levin]

> So probably the right answer to the question in the subject is "less than
> 5000".

Thank you George. When users asking me to add fields to tables which already have close to 200 fields I was wondering how much more could we add fields. Are we close to the limit. And I remember the old days when Oracle limit was 256 columns. 
That theoretical 5000 completely answers the question. We are very far from the limit. Thanks.

Dmitri Levin
alphabroder Co.

[Thomas Mercer-Hursh, Ph.D.]

On 2/28/2015 9:31 AM, Dmitri Levin wrote:
> We are very far from the limit. Thanks.

And, ought to stay that way! :)

There is a persistent temptation in many OE shops to "just add a field
or two" over and over and over again without thinking about what one is
doing to the normalization of the database. If one adds those fields to
handle a special class of record ... a subclass in OO terms ... then the
properties are meaningless in the records not of that class and one has
violated normalization. If one does it again and thus ends up with two
sets of mutually exclusive properties it is even worse.

[Grant Maizels]

For the record QAD has some tables with more than 200 records (and there are a lot of QAD systems around).

The following is from 2009SE. I have listed tables with more than 150 records.

Obviously the design of these tables is stretched beyond what is optimal, but 241 seems pretty safe.

Grant

def temp-table tt
field tname as char
field nflds as int.

for each _file no-lock:
create tt.
tname = _file._file-name.
for each _field of _file no-lock:
nflds = nflds + 1.
end.
end.
for each tt by nflds desc:
display tt.
end.

┌───────────────────┐
│tname nflds│
│──────── ──────────│
│gl_ctrl 241│
│pt_mstr 227│
│sod_det 199│
│idh_hist 197│
│svc_ctrl 182│
│sa_mstr 176│
│so_mstr 168│
│ca_mstr 166│
│sah_hist 163│
│wr_route 158│
│pod_det 157│
│pl_mstr 155│
│chm_mstr 152│

Grant Maizels
Database Administrator

Orora Limited
109 Burwood Road
Hawthorn VIC 3122

E: Grant....@ororagroup.com
T: +61 3 9811 7376


CAUTION - This message may contain privileged and confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you have received this message in error please notify Orora immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of Orora.