Size Limit

[Tom Cone Jr]

RE: Size Limit

Rick,

Ok, I'm beginning to see the problem. Pardon me for being slow on the uptake. You'll hit the file size limit in Access before you hit the 2 gig record count limit in a DBF table, and you're wanting to know if there's an outside physical limit to the overall "size" of a single DBF table.

I think another useful question for you to explore is how well Alpha Five (or any other similarly priced package) will function with tables that approach 2 gigs in size. I don't have first hand experience with anything nearly that big.

-- tom

[Peter.Wayne]

RE: Size Limit

I don't think the 2 gig file size limit is really an issue. You can always partition the records into distinct groups. For example, if your table had a size limit of 2 billion records and you needed to index the entire population of the world, you could divide the database by surnames and have a table for A-E, one for F-M, N-R, and S-Z. With a little effort in coding your users would not even be aware that they were switching tables. Performance, in fact, might be superior to keeping them all in one place.
And A5v6 supposedly will let us use MySQL tables as a native format. That should make the issue moot.

[George Pelkofer]

RE: Size Limit

Hi Rick:

At its core, I believe that Alpha utilizes the Codebase engine from Sequiter Software. Thus, I would assume that the limits that apply to Codebase apply to Alpha as well.

Further, Alpha apparently supports FoxPro 2.6 type table and index formats (one of several data format options available in Codebase - with the others including Clipper 5.x & dBase IV).

See: http://www.codebase.com/support/kb/?article=C01089.

This page describes the various limits of the Codebase product.

Good Luck,

George

[Stan Mathews]

RE: Size Limit

Well, back into the fray....

I do deal with tables over one gig that would be larger if I hadn't taken steps to reduce their size. I see the issue with large tables being one of network and drive speed rather than one of Alpha capabilities.

When you start pushing data around in that size chunks, you see performance issues that vary with the overall "busy ness" of the network and what other tasks the servers might be performing.

When certain Alpha operations are run, temporary tables are used to insure the completion of the operation before the original table is overwritten. If you are dealing with a one gig table, you may have the data written once to a temp table, once back to the original, then the temp table deleted, effectively handling three gigabytes of data.

[Rick Neifeld]

RE: Size Limit

This has been a fascinating discussion both from a review of Alpha and human interactions. From a review of Alpha, it shows that there are a lot of people interested in this product and that like the product.

From a human interaction standpoint, it shows that programmers are very intuitive. One aspect of intuitive thinking is to rapidly draw conclusions. One aspect of rapidly drawing conclusions is that responses to questions often do NOT address the actual question asked. That happened in this case. Despite a multitude of emails, no one actually answered my core question: Is the 2 GIG limit in Alpha a limit on the number of addressable records or a limit on the volume of data stored in a table?

I am a scientist by initial training, where intuitive thinking is key. I am an attorney by secondary training, where intuitive thinking is key, but logical and responsive written expression is paramount. I conclude (intuitively) that programmers would all make great scientists but lousy witnesses and lousy attorneys. :) thanks, RICK

[Blake]

RE: Size Limit

If you're actually moving that much data around, you might want to consider a less traditional approach for storage.

Most DBMSes use a fixed-length file format. All the DBF based tools (like Alpha, Fox, etc.) and even Access (which simply hides its fixed files in a single OS file).

There's a whole lot of air in these formats.

I once built a custom DBMS for storing voter information. State of California, about 15 million voters who would've taken about 1,000 bytes per using a fixed file format (15G total). I got it down to under a gigabyte, but only after really understanding the data and how it was going to be used.

When you push the boundaries of what standard hardware and software will do, it might be useful to consider unusual approaches.

Or if you plan a 2-3 year development cycle, you can start now and trust that by the time you're done, 64-bit processors and software will be the norm.

[Blake]

RE: Size Limit

Rick Neifeld wrote:
-------------------------------
Is the 2 GIG limit in Alpha a limit on the number of addressable records or a limit on the volume of data stored in a table?
-------------------------------

Assuming a 32-bit addressing scheme, which I think is safe, it'd be a definite limit on the number of records.

Data size? Very probably. At some point, some math is going to be done in the indexes to jump to a record--probably its fixed byte position--and that's most likely done using the machine's word size--again, 32 bits.

However, rather than guess, I just created a new database with 10 character records and am adding 5 billion records to it. I'll let you know what happens.

It may take a while.

By the way, if this is really a pressing issue, you can do this experiment yourself. Create a simple table, one field, however long (I used 10 chars because I only have about 70-80Gs available locally and I didn't want to do this over the network) and execute the following commands in A5's interactive window:

tbl = table.open("table") 'or whatever you named it
tbl.add_blank_records(5000000000) 'nine zeros.

I find that experiments like this are FAR more informative than speculation. A lot of practical problems tend to arise before the theoretical ones are reached.

[Blake]

RE: Size Limit

Result #1:

Trying to add more than 2 billion records results in A5 instantly crashing.

Theory: It converts the number to a 4-byte signed integer and ends up trying to add negative records or address negative/out-of-bounds space.

Result #2 is pending:

Adding 2 billion records seems to speed along. This will give me a 20GB table if it works.

[Blake]

RE: Size Limit

Survey sez!

2 Gig file size limit.

Alpha stopped with:

ERROR: The system cannot find the file specified.

This is on a Win2K system with NTFS as a file system.

For giggles, I copied the table over and then concatenated it with the original, the idea being to see if Win2K and NTFS could handle a larger than 2G file. The console copy command resulted in...a 2GB file.

Everyone tells me that NTFS can handle 16, uh, petabytes. (The one after terabytes.) But you would have to very careful which system calls you use or you'll end up with a situation like the above. Even MS wasn't careful enough. Heh.

Note that if A5 supported large than 2GB files, it would have to have separate versions for systems running Fat (2GB), Fat32 (4GB, although they could skip this one, I guess) and NTFS (16PB).

I wouldn't imagine this is high on their to-do list.

[Rick Neifeld]

RE: Size Limit

Blake - Thank you for conducting the definitive test. So Alpha five can not handle more than 2 GIG of data in any table. Too bad. You seem to think this is not a big priority for Alpha. I disagree. Primary reasons people use MS Access, as I understand it, are (1) it comes with MS Office Pro so most business people arleady have it, (2) it includes VBA, and (3) it can be migrated and or used to upscale to SQL Server. Alpha is plugged as a head on competitor to Access. However, unless it can upscale to SQL Server level applications, which in my mind equates to handling huge amounts of data, Alpha will be at a competitive disadvantage. I for one, cannot use Alpha because of its apparent size limitation. I am stuck upscaling to from MS Access to some other database. thanks, RICK

[DaveM]

RE: Size Limit

Rick,

I believe you can by breaking up your data into more than one table as per Peter Wayne?

Dave

[CALocklin]

RE: Size Limit

Rick,

I'm surprised nobody mentioned A5v6 which should be coming out "soon" (whenever that is) with a "client server version" that will work with client server type databases such as MySQL. (Although it is briefly addressed in the link provided by Paul French.)

The "native" format will remain .dbf but it will have the ability to work as a front end for the other databases.

Of course, there are still a number of issues to be answered such as the speed and stability when working with those client server systems. And, as others alluded to, database programs like Alpha and Access really aren't intended to handle huge amounts of data - they just don't run that efficiently. You might want to consider using something else altogether - yes, it will cost more money up front but it may be worthwhile.

NOTE: As I understand it, the initial release of v6 will not include the "client server" capability but my guess is that it shouldn't be far behind.

[Blake]

RE: Size Limit

"You seem to think this is not a big priority for Alpha. I disagree."

I can only guess at their priorities.

Whether I think it should be a big priority is another issue. Though, I would guess not. 32-bit addressing is fine for the vast majority of apps. 64-bit addressing exists, but in high-end products.

"Primary reasons people use MS Access, as I understand it, are (1) it comes with MS Office Pro so most business people arleady have it, (2) it includes VBA, and (3) it can be migrated and or used to upscale to SQL Server."

Heh, I would say the primary reason people use Access is that they don't know any better.

"However, unless it can upscale to SQL Server level applications,"

I don't know if it can or not. It's supposed to be able to. If not this version then the next. The more critical aspect of that is the Client/Server aspect, though, not the size.

""which in my mind equates to handling huge amounts of data,

I believe I mentioned State of California voter rolls. Los Angeles county (by far the largest) would fit into a 2GB file. Those divided up naturally by county.

That approach wouldn't work with most DBMSes, however. For A5 what I'd do is split the data out into tables and provide 1-to-1 links. The set would be virtually identical to a giant file in practical respects, but would also give me the option of working with smaller, more manipulable files.

"Alpha will be at a competitive disadvantage."

Alpha is at an enormous competitive disadvantage on much larger grounds. There may be others who look at it and think, "Well, I'd use it, but I need "2GB files" but not many.

"I for one, cannot use Alpha because of its apparent size limitation."

There are ways to get around the limitation.

Believe me, I'm not trying to talk you into using A5. I would have grave reservations about using A5, Access, or any of these personal databases on information that size. They'd probably store it all right, but actually working with it--especially if you needed big chunks at a time--would be torture. (For that matter, Oracle, Sybase, SQL Server, MySQL, Interbase--they wouldn't be greased lightning either, unless you were ready to shell out the big bucks on the hardware and people to maintain it.)

"I am stuck upscaling to from MS Access to some other database."

That's a state of mind, really. If you really wanted to use A5 to do it, you probably could.