OT: System Resources Exceeded

[stanl]

I have used WB regularly to work with MsAccess tables, and have a variety of UDF's to create and execute queries to Excel/SQL Server/MySQL or to Compact and Repair internally. After moving from Win7 to Win10 I began to notice errors "System Resources Exceeded" and scripts would fail. I attributed this to db size nearing 2gig or other corruption issues. Ran across a post that mentioned the error could be eliminated in Win10 by opening task manager, right-clicking on MSAccess.exe and clicking on Set Process Affinity and unchecking any processes > 4.  I tested this by with a db I got the error with when compacting and it worked.


The author suggested code that used dll calls for get/set processaffinity. Might make a useful UDF. The code references a struct, so have to review how structs are used in WB.

[stanl]

Thought I would take a stab with the CLR. I loaded my access file and in studio tried some CLR code. The failure is subscript out of range, and I tried two methods for the process name. Could have trapped for arrinfo(arr,1) >0, but don't understand why it returns 0. I also tried with 'Winbatch Studio.exe' from studio with similar results.


EDIT:  Changed MSACCESS.EXE to MSACCESS - returned 15 on  my laptop which is 1111 or 4 processes (I assume)


EDIT: code modified below

Code (WINBATCH) Select Expand



IntControl(73,1,0,0,0)
gosub udfs
friendlyname = "MSACCESS"
ObjectClrOption("useany", "System")
oDiag = ObjectClrNew('System.Diagnostics.Process')
arr = oDiag.GetProcessesByName(friendlyname)
If arrinfo(arr,1)==0 Then goto xit
proc = arr[0]
proc = ObjectClrType("System.Diagnostics.Process",proc)
nProc = proc.ProcessorAffinity
;nProc = ObjectClrType('System.Int32',nProc)
Message("Process Affinity",nproc)
;If nProc>0 Then nproc = Dec2Hex(nproc)
;Message("Process Affinity",nproc)
oDiag=0




Exit


:xit
Display(2,"Could Not Obtain Affinity",friendlyname:" not loaded")
Exit




:WBERRORHANDLER
oDiag=0
geterror()
Message("Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineFunction Dec2Binary(number)
   binarray = ArrDimension(10)
   ArrInitialize (binarray, "")
   count = 0
   While(number!=0) ; keep looping until we cannot do any more calculations
      binarray[count] = number mod 2
      number=number/2
      count=count+1
   EndWhile
   binary = ""
   size = ArrInfo(binarray, 1)-1
   For x = size To 0 By -1
      binary = binary : binarray[x]
   Next
   Return binary
#EndFunction


#DefineFunction Dec2Hex(Dec)
IsZero=@TRUE
str="0123456789ABCDEF"
hex=""


For x=7 To 0 By -1
nibble= (dec >> (x*4)) & 15
If nibble==0 && IsZero==@TRUE Then Continue
IsZero=@FALSE
hex=StrCat(hex,StrSub(str,nibble+1,1))
Next
Return(hex)
#EndFunction
Return

[td]

ProcessorAffinity returns the number of processors (not processes) a running process can execute on as a bitmask - one-bit pure processor. For example, my workstation has 6 hyperthreaded cores which translate into 12 processors.  On my system, ProcessorAffinity by default returns 4095 which is binary 111111111111.   In other words, the targeted process will execute on any or all of the 12 processors.

It is interesting that the System resource exceeded issue occurs in the first place.  I need to remember that for future reference.

[stanl]

It is interesting that the System resource exceeded issue occurs in the first place.  I need to remember that for future reference.

I guess it's a WIN10 thing. As for Access, I've had issues with speed, 'not responding' ever since moving up from Office 2013. With regards to my code, ProcessAffinity returned 15 (it would do the same for notepad as friendly name). However if I uncomment the next line

Code (WINBATCH) Select Expand



nProc = ObjectClrType('System.Int32',nProc)
Message("Process Affinity",nproc)



it returns a huge number - 7552664.   If I uncomment

Code (WINBATCH) Select Expand



If nProc>0 Then nproc = Dec2Hex(nproc)
Message("Process Affinity",nproc)



it errors with 'variable could not be converted to valid number'. I clipped the dec2hex() udf from tech db, but I don't think it works with small numbers.


Anyway, If nProc = proc.ProcessorAffinity gives a number > 15 I would want to set affinity to 15 [1111] or 7 [111]. Would appreciate a CLR type tip before extending the snippet.

[td]

I guess it's a WIN10 thing. As for Access, I've had issues with speed, 'not responding' ever since moving up from Office 2013. With regards to my code, ProcessAffinity returned 15 (it would do the same for notepad as friendly name). However if I uncomment the next line

Code (WINBATCH) Select Expand



nProc = ObjectClrType('System.Int32',nProc)
Message("Process Affinity",nproc)



it returns a huge number - 7552664.   

ObjectClrType returns a special object that carries both a type and a value.  The object is only useful as a parameter to a dotNet constructors, method, or property value.  It has no special meaning to non-dotNet WIL functions or operators, and is treated as a variant of type VT_UNKOWN.

If I uncomment

Code (WINBATCH) Select Expand



If nProc>0 Then nproc = Dec2Hex(nproc)
Message("Process Affinity",nproc)



it errors with 'variable could not be converted to valid number'. I clipped the dec2hex() udf from tech db, but I don't think it works with small numbers.


Anyway, If nProc = proc.ProcessorAffinity gives a number > 15 I would want to set affinity to 15 [1111] or 7 [111]. Would appreciate a CLR type tip before extending the snippet.

Get rid of the "nProc = ObjectClrType('System.Int32',nProc)" for the reason stated above and you will find that Dec2Hex works just fine.

[kdmoyers]

ObjectClrType returns a special object that carrys both a type and a value.  The object is only useful as a parameter to a dotNet constructors, method, or property value.  It has no special meaning to non-dotNet WIL functions or operators, and is treated as a variant of type VT_UNKOWN.

Plink! That sound was the penny dropping in my brain. I think I get the ObjectClrType function much better now.
Thanks guys!

[stanl]

Had to replace


If nProc>0 Then nProc = Dec2Hex(nProc)


with


If isnumber(nProc) Then nProc = Dec2Hex(nProc)


in order to remove the error I was receiving.

[stanl]

I'll finish this off with a working script. Used an excel worksheetfunction hex2bin() for final display [displays 4 processors as 00001111). Thought Detlev had posted a useful hex2bin() but couldn't find it. Using xhex() requires loading an extender and only converts hex[F] back to Dec[15].

Code (WINBATCH) Select Expand



IntControl(73,1,0,0,0)
gosub udfs
;friendlyname = "MSACCESS"
friendlyname = "notepad"
ObjectClrOption("useany", "System")
oDiag = ObjectClrNew('System.Diagnostics.Process')
arr = oDiag.GetProcessesByName(friendlyname)
If arrinfo(arr,1)==0 Then goto xit
proc = arr[0]
proc = ObjectClrType("System.Diagnostics.Process",proc)
nProc = proc.ProcessorAffinity
If isnumber(nProc)
   nProc = Dec2Hex(nProc)
Else
   goto xit
Endif
nProc=hex2bin(nProc,8) ;uses WorksheetFunction
Message("Process Affinity",nProc)
oDiag=0




Exit


:xit
Display(2,"Could Not Obtain Affinity",friendlyname)
Exit




:WBERRORHANDLER
oDiag=0
geterror()
Message("Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineFunction Dec2Binary(number)
   binarray = ArrDimension(10)
   ArrInitialize (binarray, "")
   count = 0
   While(number!=0) ; keep looping until we cannot do any more calculations
      binarray[count] = number mod 2
      number=number/2
      count=count+1
   EndWhile
   binary = ""
   size = ArrInfo(binarray, 1)-1
   For x = size To 0 By -1
      binary = binary : binarray[x]
   Next
   Return binary
#EndFunction


#DefineFunction Dec2Hex(Dec)
IsZero=@TRUE
str="0123456789ABCDEF"
hex=""


For x=7 To 0 By -1
nibble= (dec >> (x*4)) & 15
If nibble==0 && IsZero==@TRUE Then Continue
IsZero=@FALSE
hex=StrCat(hex,StrSub(str,nibble+1,1))
Next
Return(hex)
#EndFunction




#DefineFunction hex2bin(n,d)
   oXL = CreateObject("Excel.Application")
   oXL.Visible = 0 ;change to 0 after testing that it works
   retval=oXL.WorksheetFunction.Hex2Bin(n,d)
oXL=0
Return(retval)
#EndFunction


Return


[td]

You don't need the line "proc = ObjectClrType("System.Diagnostics.Process",proc)".  The CLR already knows what the object is.  Your script works fine without it.

The usual approach to hex to bin conversion is to use a decimal integer as an intermediate step.  Since you already have a decimal integer it would be more efficient to convert from it to binary.  But just for fun here is a UDF to convert from hex to binary.  There are likely more efficient approaches but this UDF shows off a few of WIL's newer features. For simplicity's sake, it does not have any error handling or range checking.

Code (winbatch) Select Expand

;;; Accepts a hexidecimal representation of a number.
;;; Returns a binary representation of the hex number.
#DefineFunction Hex2Bin(_Hex)
   
   ; Convert to decimal
   mConvert = MapCreate("0=0,1=1,2=2,3=3,4=4,5=5,6=6,7=7,8=8,9=9,A=10,B=11,C=12,D=13, E=14,F=15",'=',',')
   sHex = StrTrim(StrUpper(_Hex))
   aHex = ArrayFromStr(sHex)
   nHexlen = ArrInfo(aHex, 1) - 1
   nDec=0
   for x=0 to nHexlen
       nDec=(nDec*16) +  mConvert[aHex[x]]
   next

   ; Convert to binary (one of many ways.)
   sBin = ''
   while nDec
      if nDec Mod 2 then sBin := '1'
      else sBin := '0'
      nDec /= 2
   endwhile

   ; Assume 32 bit integer.
   sBin = StrFixLeft(sBin,'0',32)
   return sBin
#EndFunction

strHex = 'fff'
strBin = Hex2Bin(strHex)

Message('Convert Hexidecimal to Binary', 'Hex: ':strHex:@lf:'Bin: ':strBin)
exit


[stanl]

Actually there is a Dec2Binary udf() in my code. Your Comment
[size=0px]You don't need the line "proc = ObjectClrType("System.Diagnostics.Process",proc)".  The CLR already knows what the object is.  Your script works fine without it.[/size]
[/size]
Makes some sense, since when I called Dec2Binary(), I got the variable cannot be converted error. I corrected this by calling nProc = ObjectType("I4",nProc). commented out the hex conversion and Dec2Binary() returned 1111. Oh, and the line you said I didn't need - it is used in the example for ObjectClrType() in Consolidated Help.

Code (WINBATCH) Select Expand



IntControl(73,1,0,0,0)
gosub udfs
;friendlyname = "MSACCESS"
friendlyname = "notepad"
ObjectClrOption("useany", "System")
oDiag = ObjectClrNew('System.Diagnostics.Process')
arr = oDiag.GetProcessesByName(friendlyname)
If arrinfo(arr,1)==0 Then goto xit
proc = arr[0]
proc = ObjectClrType("System.Diagnostics.Process",proc)
nProc = proc.ProcessorAffinity
nProc=objecttype("I4",nProc)
nProc = Dec2Binary(nProc)
Message("Process Affinity",nProc)
oDiag=0
Exit


:xit
Display(2,"Could Not Obtain Affinity",friendlyname)
Exit


:WBERRORHANDLER
oDiag=0
geterror()
Message("Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineFunction Dec2Binary(number)
   ;message("",Objecttypeget(number))
   binarray = ArrDimension(10)
   ArrInitialize (binarray, "")
   count = 0
   While(number!=0) ; keep looping until we cannot do any more calculations
      binarray[count] = number mod 2
      number=number/2
      count=count+1
   EndWhile
   binary = ""
   size = ArrInfo(binarray, 1)-1
   For x = size To 0 By -1
      binary = binary : binarray[x]
   Next
   Return binary
#EndFunction


#DefineFunction Dec2Hex(Dec)
IsZero=@TRUE
str="0123456789ABCDEF"
hex=""


For x=7 To 0 By -1
nibble= (dec >> (x*4)) & 15
If nibble==0 && IsZero==@TRUE Then Continue
IsZero=@FALSE
hex=StrCat(hex,StrSub(str,nibble+1,1))
Next
Return(hex)
#EndFunction


Return


[td]

The line you do not need is still in your script and you still don't need it.  Regardless of the fact that it is in a poorly written help file example.  There are thousands of examples in the Consolidated WIL Help file and occasionally one slips by without proper vetting. 

Yes, your script had a Decimal to binary UDF.  However, as stated, the script I posted was "just for fun" and it does show how to do the conversion without having to call into Excel as your script did.  I also wanted to illustrate the use of WIL maps in the hope that more users are made aware of and use them.  It's a very powerful but underutilized feature of WIL.

The reason you need to use ObjectType is that the returned number variant is type VT_INT which is a valid variant type but it is usually not considered a valid COM Automation variant type. So it does not always get converted to a WIL integer automagically.  Since you have pointed out a case where WIL does encounter a VT_INT we will have to add that conversion to the todo list for a future release. 

[td]

FWIW, you can eliminate the call to ObjectType by changing the line "While(number!=0)"  to "While(number)".

[td]

Updated the ObjectClrType function's example on the documentation site:

https://docs.winbatch.com/mergedProjects/WindowsInterfaceLanguage/html/ObjectClrType.htm

Thanks for pointing out the error of our ways.

[stanl]

I have placed your 'map' code in my ToLearn List. And to echo Kirby's comments some of the fog has lifted regarding proper use of ObjectClrType() and for that matter type conversions in general. This leaves the final step. As my use of any UDF I eventually come up with will be part of a compiled exe and right now specific to MsAccess and the exceeds resources error.


My UDF returns 11111 [31] and I want to set affinity to 1111 [15].  Should I wrap 15 as ObjectType()?

[ChuckC]

I didn't see any reference to a specific version of MS Access mentioned in this thread, but, out of curiosity, I did a bit of digging around on the ol' interwebs and ran across a nice technical discussion regarding the problem described in the start of the thread.

This discussion pertains to the underlying cause of the problem and a possible modification of the application code to make a settings change that may alleviate the problem without having to go the route of fiddling with processor affinity settings for the "msaccess.exe" process.

https://www.experts-exchange.com/questions/29134355/Set-Affinity-for-Access-2016-in-Windows-10-Command-Prompt.html

Also, there was another reference to a registry-based hot fix specific to MS Access 2013, as follows:

https://support.microsoft.com/en-us/help/2760362/description-of-the-access-2013-hotfix-package-ace-x-none-msp-february

[td]

I have placed your 'map' code in my ToLearn List. And to echo Kirby's comments some of the fog has lifted regarding proper use of ObjectClrType() and for that matter type conversions in general. This leaves the final step. As my use of any UDF I eventually come up with will be part of a compiled exe and right now specific to MsAccess and the exceeds resources error.


My UDF returns 11111 [31] and I want to set affinity to 1111 [15].  Should I wrap 15 as ObjectType()?

I think I owe an apology here.  I got so sidetracked by my own agenda that I failed to consider the main point of your script.  The ProcessAffinity property gets and accepts a structure of type System.IntPtr.  While the CLR happily converts this to an VT_INT variant when you get the property's value it is not so generous when attempting to set the property.  I suspect this is because MSFT and therefore the CLR considers "unmanaged" pointers "unsafe".  The one way that I am fairly sure of working would be to write a small C# class with a set method that could be compiled into memory on the fly.  There is also the VB .Net approach of using reflection's marshaling classes to create a pointer value that the CLR will accept but I have not tried this.

I believe Chuck is suggesting that there may be an alternative to changing process affinity and that may be the best way to go.  If you do want to pursue the dotNet process affinity tack further, I am more than willing to attempt to put an example together that may allow you to set the property.  However, I think it would be easier to just use the Win32 API.  The following Tech Database article has a UDF for setting the process affinity of the current process.

https://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/UDF~-~UDS~Library/Process~UDFs+Set~Current~Process~Affinity.txt

This script could be modified to set the affinity of another process by passing the process id to the OpenProcess to obtain the process handle

Code (winbatch) Select Expand

; Need both query(1024) and sync(1048576) access
hProcess = DllCall(hKernel32,long:"OpenProcess",long:1024|1048576,long:0,long:Pid)

Again I apologize for hijacking your topic.

[td]

Here's a Win32 DllCall based example:

Code (winbatch) Select Expand

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; Reduces process affinity of indicated process
; by passed in percent.
; return - @True on success, otherwise, @False
#DefineFunction SetProcessorsAffinity(_Pid, _Affin)
   bReturn   = @FALSE
   hKernel   = DllLoad(DirWindows(1):"Kernel32.dll")
   hProcMask = BinaryAlloc(4)
   hSysMask  = BinaryAlloc(4)
   hProcess  = 0
   
   ; Need query(1024), PROCESS_SET_INFORMATION (512),  and sync(1048576) access.
   While @TRUE
      hProcess = DllCall(hKernel,long:"OpenProcess",long:512|1024|1048576,long:0,long:_Pid)
      if !hProcess then break
      If !DllCall(hKernel, long:"GetProcessAffinityMask", long: hProcess, lpbinary:hProcMask, lpbinary:hSysMask) Then Break
      BinaryEodSet(hSysMask, 4)
      dwSysMask = BinaryPeek4(hSysMask, 0)
      dwSysMask = _Affin
      bReturn   = DllCall(hKernel, long:"SetProcessAffinityMask", long: hProcess, long:dwSysMask)
      ;;;if !bReturn then Message( 'Last Error', DllLastError())
      Break
   EndWhile

   if hProcess then DllCall(hKernel, long:"CloseHandle", long: hProcess)
   DllFree(hKernel)
   BinaryFree(hSysMask)
   BinaryFree(hProcMask)

   Return bReturn
#EndFunction


; Test (Set to 4 processors.)
nPid = RunShell( "notepad.exe", "", "", @NORMAL, @GETPROCID)
bResult = SetProcessorsAffinity(nPid, 15)

Message("Affinite Set", bResult)

Edit: I simplified the original script to using a supplied affinity setting instead of a calculating one.

[stanl]

Let' wrap this up
Chuck:

   
• MsAcess at work - just upgraded to 2016, 32 bit on 64 bit Win10; home 64 bit Office 2019
• I have a batch file that compacts/repairs over 30 Access dbs on network. Most of the work I do at work as my rights are restricted is done manually with VBA modules or some Powershell. I would like to automate most of the VBA into WB exe, but the issue with resources exceeded is why I started this thread.

Tony:

   
• I was going to bring up the IntPtr as a type in the initial thread but since my first efforts returned a value I didn't.
• I will try a test exe with the dll call at work to see if it gets past my limited restrictions.

[ChuckC]

About halfway down the discussion thread referred to by the 1st of the 2 links that I posted, the topic turns to a "MaxBuffers" setting in MSAccess.  It definitely appears to apply to MSAccess 2016, too.  If you haven't run down that entire thread, here are some of the salient points from it by way of a couple of messages copied from there and pasted here:


I detailed the issue in my reply to McKnife (above), but bottom line: We want to avoid Access shredding backends while doing compacts/repairs on Windows 10 systems with 12-core CPUs.

We ran into the "System Resource Exceeded" error straight out of the box, on a simple operation to add less than 2,000 records to a large Table Definition (using the AddNew method of a DAO Recordset inside a loop).

We found literature detailing this issue, and found a workaround by setting the Affinity of the Access thread to only four processors, using the Task Manager.   Additional literature suggests this same Affinity issue can corrupt a database during compact/repair, and that REALLY got our attention.

We have code ready to deploy, which limits Affinity to four processors at startup, but in the link you cite,  Jim defines the issue as occurring when Access runs "on multi-core CPUs under a 64 bit OS," and suggests the fix is to set the "MaxBuffers" to 2^16.  His post is from 2013, addresses Access 2010, and gives a link to KB2726928, which suggests a hotfix package.

Questions:

1. Is the solution Jim gives still relevant?  One would hope that MS has fixed this issue in Access 2016, but our experience (with brand new, fully patched, 12-core systems running Windows 10) suggests otherwise.

2. If his advice still holds, then does it substitute for limiting Affinity to four cores (i.e. setting "MaxBuffers" to 2^16 allows one to run worry-free on all 12 cores)?

3. We use Access 2016 to run MDE/MDB files.  Does that matter to either of the above questions (i.e. is our scenario ACE, Jet, or both)?




1. It's my understanding that it has been long since fixed, although this may be a closely related issue.  Could also be something entirely different.

  That said, setting MaxBuffers to 65535 is not going to hurt.  You can use dbEngine.SetOption at the start of the app to set it.

2.  Not exactly.   What the setting does is makes the cache large enough to avoid the garbage clean-up issue.  JET/ACE is still running on multiple cores.

3.  No.  JET and ACE are one in the same thing (more or less).  ACE has had some improvements, but it's basically JET 4.0 and works fundamentally the same way in terms of database structure, page handling, and the cache.




Thanks for the insight.

What [setting MaxBuffers to 65535] does is makes the cache large enough to avoid the garbage clean-up issue.  JET/ACE is still running on multiple cores.

So, apparently, the original issue (back in Access 2010) was some sort of JET/ACE "garbage clean-up issue" that only occurred on systems with more than four cores?




Yes.   It was related to allocating pages in the working set for the process   If I understood correctly (not a lot of detail was available on exactly what was going on), allocation of new pages would occur across multiple threads (JET/ACE has three by default), and they would block one another if each thread was on a different core. 

Why that came into play under a 64 bit OS and not 32's I don't know.

My memory also tells me that when I tested setting the affinity, I found that Access would not honor it, and yet several people said it solved their problem. Not sure if that was a manual vs a shortcut thing or what.  I've never went back to it because it's such an obscure issue. 

Windows does a pretty good job of scheduling multiple threads and I'm not aware of too many cases where having them caused issues (some games run better when set to run on less CPUs).

I think this is probably a problem Microsoft should look at (too much guess work on our parts) and I believe gustav has already passed it along to them.

[stanl]

Chuck;


Should have mentioned I never had the problem with Office 2013. As for the thread you mentioned, they kept referring to .mdb not .accdb, though to be honest compact/repair on .accdb creates .mdb backup. Personally think Microsoft is gung-ho on Excel and Dax, Power BI, Tableau and leaving Access to it's pathetic 2 gig limit.


My main use of Access at work, currently, is to process text/excel data into suitable table structures and create ^ delimited exports for uploading to SAP Hana CDL.

[td]

Tony:

   
• I was going to bring up the IntPtr as a type in the initial thread but since my first efforts returned a value I didn't.
• I will try a test exe with the dll call at work to see if it gets past my limited restrictions.

You should be able to use the DllCalls to GetProcessAffinityMask and SetProcessAffinityMask if you can run with default elevated admin privileges and the process you are targeting is running at the same level.  If you can't run that way, I am not sure how much you will be able to do. You would have the same issue with a dotNet based script.  If you don't want to validate the input affinity or calculate an affinity on the fly, you could remove the  GetProcessAffinityMask call from the UDF.  It doesn' hurt anything to leave it in but it is extraneous since the value it provides is not used.