I am having a problem understanding how many records can be in a table with different Block sizes for the Memo/Blob.

I see the limit is 4 GB x block size

So if i am using a value of 8 for block size that would be 4GB x 8 = 32 GB How does that translate into number of records in table?

If we are storing in table rows just 1 or 2 spaces in the Memo does that factor in.

We were also debating putting our JPG/Images into a Blob but worried about number of records to size of JPG.

Is there a norm that should be used for Block Size to save space in the ADM.

Also if we stored both Blob and Memo does that cut the max size in half?

Martin Elder martin@mmmicro.com

asked 27 Jun '15, 05:54

Martin%20Elder's gravatar image

Martin Elder
16113
accept rate: 0%


The number of records in a table does not necessarily correlate to the Memo Block Size.

The Maximum number of Records in an ADT is 2 Billion. Even at 1-2 spaces you could in theory hit the maximum records before you hit the maximum memo file size. On the flip size if you are storing a 1MB jpg I think you could only have 32K records that had Memos, but then as many more (up to 2 billion) that have a NULL memo.

I normally recommend the memo block size to be a factor of the typical file size you plan to insert. For example, 8 may work well for a small JPEG although 32GB isn't that large so 1024 may be the better choice in the long run.

For Memo / Blobs that may get updated frequently I also recommend the larger block size 1024. With 8, updates will likely cross the boundary causing the data to be moved to the end of the file and leaving the "empty" space for re-use. Problem is that it may not be applicable to other inserts/updates and suddenly the file is bloating very quickly requiring a PACK to clean up the space. This still could happen at 1024, but it is much less likely.

link

answered 29 Jun '15, 09:48

Edgar%20Sherman's gravatar image

Edgar Sherman
5.4k13185
accept rate: 25%

We are trying to consolidate our tables over a 25 year project into fewer smaller tables. This Memo Table we are using has One Memo and One Blob field for storing text as "plain text" and the other with the formatted "pretty" version for printing and display. Currently one of our tables has 24mil records our concern is being able to have a table with 100 Mil records we would be limited by 32GBIt seems from your reply we might consider at lest moving to 512K or 1024 K block size. Does that sound correct? Thanks for you response.

(30 Jun '15, 13:25) Martin Elder

I think that sounds correct. Most often I would recommend the higher size, the likelyhood that you would "waste" space is very minimal with real world applications I've found.

(30 Jun '15, 14:12) Edgar Sherman
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×8

Asked: 27 Jun '15, 05:54

Seen: 4,946 times

Last updated: 30 Jun '15, 14:12

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.