# Board index » delphi » Limit of records per block in Paradox?

## Limit of records per block in Paradox?

BDE 5.11
If I set blocksize in BDE-Config / Paradox to 16384 and I create a table
in the DBD with the following fields...
- Nr     Numeric            * (Primary Key)
- Bez    Alpha        30

and then simply fill the table from Delphi 6 like this:

i := 0;
while i < 100000 do
begin
table1.Insert;
i := i + 1;
table1.FieldByName('nr').AsInteger := i;
table1.FieldByName('bez').AsString := 'Teststring' + IntToStr(i);
table1.Post;
end;

then, when open the filled table in the DBD again and set the position
to a record higher than about 62000 (with the slider - not scrolling
with PageUp or the Up/Down-Keys) the contents of the fields are wrong.
While writing the records no errors occured.

When setting the blocksize (before creating the table) to 4096 or less
all seems to be right.
I think the index runs out of bounds while changing to the next block
and there are too many records in one block.

Can anyone say if there is a limit of records for one block (depends on
blocksize)?

with friendly regards
Wolfgang

## Re:Limit of records per block in Paradox?

##### Quote
Wolfgang Letz wrote:
> Can anyone say if there is a limit of records for one block (depends on
> blocksize)?

in theory, the limit is the number of records that fit.. in your example,
you can get exactly 431 records per 16k block, with a perfect 6 bytes left
for the key.. 100% fill..

--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
-------------------------------------------------------------------------

## Re:Limit of records per block in Paradox?

There is no upper limit to the number records in a block. In your case
the record size is 38 bytes. To determine the number of records per
block use (block size - 6) / record size truncated to the nearest
whole number because records cannot span blocks. In your case 431
records fit in a 16k block with no wasted space.

I am not sure what is causing the problem you described. Did you try
packing the table after you loaded it?

On Mon, 01 Jul 2002 19:13:53 +0200, Wolfgang Letz <wolfg...@letz.de>
wrote:

##### Quote
>BDE 5.11
>If I set blocksize in BDE-Config / Paradox to 16384 and I create a table
>in the DBD with the following fields...
>- Nr     Numeric            * (Primary Key)
>- Bez    Alpha        30

>and then simply fill the table from Delphi 6 like this:

>   i := 0;
>   while i < 100000 do
>   begin
>     table1.Insert;
>     i := i + 1;
>     table1.FieldByName('nr').AsInteger := i;
>     table1.FieldByName('bez').AsString := 'Teststring' + IntToStr(i);
>     table1.Post;
>   end;

>then, when open the filled table in the DBD again and set the position
>to a record higher than about 62000 (with the slider - not scrolling
>with PageUp or the Up/Down-Keys) the contents of the fields are wrong.
>While writing the records no errors occured.

>When setting the blocksize (before creating the table) to 4096 or less
>all seems to be right.
>I think the index runs out of bounds while changing to the next block
>and there are too many records in one block.

>Can anyone say if there is a limit of records for one block (depends on
>blocksize)?

>with friendly regards
>Wolfgang

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

## Re:Limit of records per block in Paradox?

##### Quote
Bill Todd wrote:
> In your case 431 records fit in a 16k block with no wasted space. Did you
> try
> packing the table after you loaded it?

what happens when you pack a table with 0 dead space, that was sequentially
filled? <g>

--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
-------------------------------------------------------------------------

## Re:Limit of records per block in Paradox?

On Mon, 01 Jul 2002 17:32:36 -0400, Steven Green

##### Quote
<gre...@diamondsg.com> wrote:
>Bill Todd wrote:

>> In your case 431 records fit in a 16k block with no wasted space. Did you
>> try
>> packing the table after you loaded it?

>what happens when you pack a table with 0 dead space, that was sequentially
>filled? <g>

The primary index gets rebuilt and a corrupt primary index _might_
cause the table to appear corrupt when browsing in primary key order.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

## Re:Limit of records per block in Paradox?

##### Quote
Bill Todd wrote:
> >what happens when you pack a table with 0 dead space, that was sequentially
> >filled? <g>

> The primary index gets rebuilt and a corrupt primary index _might_
> cause the table to appear corrupt when browsing in primary key order.

good point.. but it's sad to think that filling a table in that manner would

--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
-------------------------------------------------------------------------

## Re:Limit of records per block in Paradox?

I packed the table and all records were displayed with the correct data.
But in the real program data (about 80000 records) has to be converted
to such a table and this should run without packing the table afterwards.
I did another test:
First I created 61919 records with the insert-loop.
Then, without packing the table,  I displayed the data in a Delphi grid
and added 1 record - all went fine. But when adding one more record I
got an index error. And that's exactly the same position from this on
the data seems to be corrupted when creating the records automatically.
And this strange behaviour is reproducable on different machines. I
think it's no solution to pack a table everytime after inserting some
records.

Wolfgang

##### Quote
Bill Todd wrote:
> There is no upper limit to the number records in a block. In your case
> the record size is 38 bytes. To determine the number of records per
> block use (block size - 6) / record size truncated to the nearest
> whole number because records cannot span blocks. In your case 431
> records fit in a 16k block with no wasted space.

> I am not sure what is causing the problem you described. Did you try
> packing the table after you loaded it?

## Re:Limit of records per block in Paradox?

##### Quote
Wolfgang Letz wrote:
> I did another test:
> And this strange behaviour is reproducable on different machines.

there have been occasional instances, over the years, where a specific
combination of fields types and table size creates problems that are only
reproducable with that exact combination.. it's very rare, but it has
happened before.. I guess you just found another one <sigh>

I'd suggest changing the size of the alpha field.. make it a couple of bytes
bigger, so it changes the number of records in a block, leaves a little dead
space, etc.. odds are, it'll cure the problem..

--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
-------------------------------------------------------------------------

## Re:Limit of records per block in Paradox?

What is the size of the .DB file when the error occurs? Have you tried
the same test with an 8k or 32k block size?

On Wed, 03 Jul 2002 21:58:42 +0200, Wolfgang Letz <wolfg...@letz.de>
wrote:

##### Quote
>I packed the table and all records were displayed with the correct data.
>But in the real program data (about 80000 records) has to be converted
>to such a table and this should run without packing the table afterwards.
>I did another test:
>First I created 61919 records with the insert-loop.
>Then, without packing the table,  I displayed the data in a Delphi grid
>and added 1 record - all went fine. But when adding one more record I
>got an index error. And that's exactly the same position from this on
>the data seems to be corrupted when creating the records automatically.
>And this strange behaviour is reproducable on different machines. I
>think it's no solution to pack a table everytime after inserting some
>records.

>Wolfgang

>Bill Todd wrote:

>> There is no upper limit to the number records in a block. In your case
>> the record size is 38 bytes. To determine the number of records per
>> block use (block size - 6) / record size truncated to the nearest
>> whole number because records cannot span blocks. In your case 431
>> records fit in a 16k block with no wasted space.

>> I am not sure what is causing the problem you described. Did you try
>> packing the table after you loaded it?

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

## Re:Limit of records per block in Paradox?

Bill,

8K isn't an option, remember? :-)

Liz

##### Quote
Bill Todd wrote:

> What is the size of the .DB file when the error occurs? Have you tried
> the same test with an 8k or 32k block size?

## Re:Limit of records per block in Paradox?

The .DB file has a size of 2.361.344 bytes when the error occurs.
I tried it with 32k block size - the error occurs, too.
I just set the length of the string field to 40 bytes (old length 30
bytes) - the error occurs at record number 48961 (at pos 61920 with old
length 30)
##### Quote
Bill Todd wrote:
> What is the size of the .DB file when the error occurs? Have you tried
> the same test with an 8k or 32k block size?

## Re:Limit of records per block in Paradox?

Is the primary index the only index on the table?

One possible solution is to drop the index, import the data then
recreate the index. This would probably be faster than inserting a
large number of records with the index in place.

On Wed, 03 Jul 2002 23:04:28 +0200, Wolfgang Letz <wolfg...@letz.de>
wrote:

##### Quote
>The .DB file has a size of 2.361.344 bytes when the error occurs.
>I tried it with 32k block size - the error occurs, too.
>I just set the length of the string field to 40 bytes (old length 30
>bytes) - the error occurs at record number 48961 (at pos 61920 with old
>length 30)

>Bill Todd wrote:

>> What is the size of the .DB file when the error occurs? Have you tried
>> the same test with an 8k or 32k block size?

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

## Re:Limit of records per block in Paradox?

On Wed, 03 Jul 2002 14:58:48 -0600, Liz <l...@paradoxcommunity.com>
wrote:

##### Quote
>Bill,

>8K isn't an option, remember? :-)

No I don't (but that does not surprise me). Why? If the table is only
2 mb he could use a 2k block size.

##### Quote

>Liz

>Bill Todd wrote:

>> What is the size of the .DB file when the error occurs? Have you tried
>> the same test with an 8k or 32k block size?

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

## Re:Limit of records per block in Paradox?

##### Quote
Wolfgang Letz wrote:
> I just set the length of the string field to 40 bytes (old length 30
> bytes) - the error occurs at record number 48961 (at pos 61920 with old
> length 30)

now it's getting wierd.. different block size and/or different record size,
and it still bombs.. does it only bomb in Delphi?.. can you zip up the table
and email it to me?.. I want to try the same thing, and have your table
*and* my table to compare..

--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
-------------------------------------------------------------------------

## Re:Limit of records per block in Paradox?

I mentioned this once before here - I see the 8K limit, but
neither help files nor documentation list that as a valid option
(in places where they list valid block sizes) and if you drop
down the block size list, 8K isn't there.  I tried typing in 8K,
and made a new table, and tutility said it had a 16K block
size...

FWIW,

Liz

##### Quote
Bill Todd wrote:

> On Wed, 03 Jul 2002 14:58:48 -0600, Liz <l...@paradoxcommunity.com>
> wrote:

> >Bill,

> >8K isn't an option, remember? :-)

> No I don't (but that does not surprise me). Why? If the table is only
> 2 mb he could use a 2k block size.

Go to page: [1] [2]