MS Access Disk Buffer Size (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:35
Joined
Feb 28, 2001
Messages
27,001
Researching a question for another thread, I came across an interesting article. All this time, I thought that an Access disk buffer was 2048 bytes. But that appears to be wrong.

https://msdn.microsoft.com/en-us/library/windows/desktop/dn170506(v=vs.85).aspx

Read the description for MaxBufferSize and see if that matches what you remember about it. It is not clear from the discussion that this applies to Access BE files because it is under an ODBC heading. However, the mere fact that Access CAN vary this buffer size is something I had not seen before.

So I did some more digging and here is how you can change buffer size on a "native" Access BE file.

http://www.utteraccess.com/forum/index.php?showtopic=1982916

Don't know if anyone even cares, but I just found it interesting.
 

isladogs

MVP / VIP
Local time
Today, 13:35
Joined
Jan 14, 2017
Messages
18,186
If that will help prevent a system resource exceeded error when handling huge external files e.g. JSON file of 152MB, I would be very interested.

I will look into this further. Thanks
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:35
Joined
Sep 12, 2006
Messages
15,614
Offhand, I thought the buffer/page size was related to the size of the hard disk.

ie, any hard disk had a limit of n pages, so as the disk got bigger, the page size increased as well.

I may be wrong with this, though, but on that basis, it may well have been 2K at some point in time.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:35
Joined
Feb 28, 2001
Messages
27,001
Dave, to my (sometimes limited) understanding, the buffer size is often tied to the memory management granularity in some bizarre way, not to the disk size. However, I believe that originally, on smaller (and older) systems, a memory page and a disk block were the same size (512 bytes or 1/2 KB). As disks got larger and memory got larger, the page size grew and it became possible to make disk buffers bigger (large enough to fill a page in a single disk transfer).

The part that got me searching was the simple fact that if Access could modify its disk buffer size for an ODBC driver, it was POSSIBLE that it could modify its disk buffer size for a native Access BE file. Hence the reference to that Utter Access article.

Colin, if you use this, note that the Utter Access article discusses issues when you make the buffer too big. So if you play around with it, take baby steps.
 

isladogs

MVP / VIP
Local time
Today, 13:35
Joined
Jan 14, 2017
Messages
18,186
Thanks for warning.
I was going to try small changes anyway as I did for MaxLocksPerFile settings

I have one monster JSON file that always creates errors when I try to load into Access. Either 'out of memory' or 'system resource exceeded'. I think it's a badly designed file but as it's so big I can't load it so I can fix it.

It's worth a try anyway.
 

isladogs

MVP / VIP
Local time
Today, 13:35
Joined
Jan 14, 2017
Messages
18,186
I've just looked at the registry setting I had for MaxBufferSize
I got a surprise - it was zero!



I also found the MaxLocksPerFile had reverted from 15000 that I set a few months ago back to the default 9500. I've switched this back again

I've now set the MaxBufferSize to 4096 as suggested in the link given by the Doc
However, it hasn't solved my issue with reading a massive JSON file into Access. I'm now getting error 7 (Out of memory) instead

There a useful link on the FMS site. Does anyone have any useful advice on fixing my out of memory error (Access 2010 & 2016).

I've already tried all the usual methods of checking databases and its only a problem for the one huge JSON import file
 

Attachments

  • MaxBufferSize.PNG
    MaxBufferSize.PNG
    40.5 KB · Views: 4,056

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:35
Joined
Feb 28, 2001
Messages
27,001
Out of memory? I sincerely hope it didn't mean "Out of physical memory." It is possible that you ran out of virtual memory, which might mean that your swap file isn't big enough.

Here is an article for possible causes of "Out of Memory" error:

https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/out-of-memory-error-7

IF the problem is virtual memory, you fix that here:

Start >> Control Panel >> System >> Performance >> (Tab) Advanced >> Performance >> Advanced >> Virtual Memory >> Change ... and then adjust the amount of virtual memory you will allow.

However, if that isn't it, there is the issue that no memory segment can exceed 64 KB, so do you have big arrays in memory or do you have a subroutine in any module where the size of the array might exceed 64 KB? Do you have a few large modules or a lot of smaller ones? If you have exceptionally large modules with lots of piddly-squat small subs and functions, supposedly that can be bad (though to be honest, unless you are dinking around with arrays, that would be hard to reach).

As to having registry entries reset, for Win 10 there is chatter regarding Windows Update services doing this. No guaranteed fix that doesn't involve physical violence against the Windows Product Manager.
 

isladogs

MVP / VIP
Local time
Today, 13:35
Joined
Jan 14, 2017
Messages
18,186
Doc
Thanks for the link - I don't think any of the points raised apply in this case

Error 7 - Out of memory
It's a 6 year old PC but I'd be VERY surprised if its an issue with physical memory (4GB) or virtual memory - range 4096kB to 8192kB

The ONLY time I see this error is with this one file where it almost always occurs.
However occasionally I get error 3055/3058 - System resource exceeded & yesterday I got a new one (to me) - error 14 - out of string space

At least I no longer get error 3052 since increasing max locks per file



I think the issue is probably with the file structure rather than my machine.
Having said that I just tested the same file on my equally old & less powerful laptop - it opened without error - took almost 100 seconds but it worked!
 

Attachments

  • ErrorCodes.PNG
    ErrorCodes.PNG
    36.3 KB · Views: 3,666

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:35
Joined
Feb 28, 2001
Messages
27,001
error 14 - out of string space

Colin, this indicates a really complex problem that may be due to a badly chosen string manipulation pattern. Here is what I understand about "string space" - and it will probably be about as clear as space-time string theory. I hope it is better than that...

String space is part of a process memory that is pre-allocated as a working area. When you create and dissolve strings in VBA, the intermediates have to go somewhere, and this "somewhere" is part of a program structure known as the heap. The string VARIABLE actually doesn't change places for the life of the string - but a component of that variable DOES change. You see, a string variable is actually a BSTR structure, which you can look up online.

http://bytecomb.com/vba-internals-string-variables-and-pointers-in-depth/

There is a string count and a string location in the actual structure. The important thing to know about this is, you can completely replace the value of a string without moving the string variable - which is good, because for subroutines, almost all variables (excluding those declared as Static) are on the stack.

The way that VBA (and many other languages) optimize string usage is that there is this area where if you have a string, the variable has a string length and a string "actual location," and that actual location is a working memory area that is part of your process virtual memory.

If you modify a string, rather than diddle the bytes, VBA makes a new copy of the modified string elsewhere and then change the length and location fields. Then VBA releases the previous set of bytes for possible re-use. Possible - but due to potential size mismatches, unlikely. Look up "memory + garbage collection" for a discussion on the perils of that particular procedure.

This working area in the heap cannot be infinite in size, obviously. A certain amount of memory space has to be set aside for it - and the starting size of course is some obscure tuning parameter. When you create and destroy strings A LOT, particularly with size changes, you run into the problem of "thrashing" the working area and causing it to be badly fragmented. Once it gets fragmented badly enough, you cannot create new strings of any appreciable length because you can't allocate contiguous bytes for it.

THEREFORE, if you see the "out of string space" message, you have an issue with the way strings are being created and destroyed in your program. Note also that the stack depth IS NOT an issue because all routines in your app share the same heap area. When the total of all functions and subs "blows out" your heap space, you get that error.

https://support.microsoft.com/en-us...f-memory-error-message-because-of-the-desktop - is an article on how to change the size of your program heap. Note also that an x32 system and an x64 system will have vastly different heap sizes because x64 systems have more room to "play with" in terms of physical memory allocation.
 

InstructionWhich7142

Registered User.
Local time
Today, 13:35
Joined
Feb 24, 2010
Messages
199
I went through this thread and some others, for reasons of ODBC compatibility with Foxpro i'm stuck on 32bit, and Access 2016/office365 has only recently been made Large Address Aware, so you may find your issues have resolved themselves, if you're still on 2010 it turns out this simple utility lets you change it to 4GB without issue, I've been running like that for a year or two on 2010 without issues,

Code:
Large Address Aware.exe

you can check your memory usage with

Code:
vmmap

amusingly I just found this as well

 
Last edited:

Users who are viewing this thread

Top Bottom