Access Cose Slows way down Second time it is run

[Daveyk01]

It never was on Microsoft's site. The runtime for every version up UNTIL 2007 was something you had to purchase by buying the Developer Version of Access (and it was a pretty penny at that). You might be able to find someone with a copy you can purchase on Ebay or something but you need to be careful as there is a lot of counterfeit merchandise there as well and you can get burned.

Ah, okay, thank you. I did not know that. I remember when we first got our PCs, Runtime was on them. Then they were re-imaged with the full version of Access.

I just wanted to try it. I may have un-covered something else that may be causing this..... I should know in the next couple of hours.

 

[gemma-the-husky]

not sure but would putting all your array handling stuff in a separate code module help with managing the stack/releasing space etc

also, rather than disposing of the array, jusdt have a procedure to clear all the elements of the array, ready for the next use

that way, you wont get the stack disintegration previously referred to

 

[Daveyk01]

Hmmm, yea, Labview would have been the way to go, I guess. Getting hardware, such as Agilent test equipment, is much easier than getting software, even if the hardware is much, much more expensive.

Why Access?? ...cause it is what I was familiar with with. I have VB6, but don't know how to do databaseing with it or report generation. VBA is also, to me, easier, an Access is a natural for Databasing and Reports (within its limits). I knew Access rather well from managing our Service database since 1999, but I keep learning more and more every day.

Can you please explain what you mean by "re-entrant"? I think I know what you mean, but I would rather hear it from you. One of the pains, sometimes, is the multi-threading. I am meaning the ability of a user to click a button to do something else on a form while another operation is running. Yes, that is a pain to stop, if you have a large form with a lot of task capabilities.

Last night (this morning around 2am) I discoverd that my module that controls the Agilent 11713B, Attenuator Controller, had numberous highly used routines that would assign a resource, such as this:

Set ioMgr = New VisaComLib.ResourceManager
Set instrument = New VisaComLib.FormattedIO488

without doing the following:
instrument.FlushRead
instrument.IO.Close
Set instrument.IO = Nothing
Set instrument = Nothing

Is this not similar to opening a table and database and exiting a function without closing them and equating them to nothing?

I don't hold out a lot of hope for that. I looked throught my O-Scope and Generator code and that code always closed out the resource, although the slow down usally occures after a heavy use of the attenuators.

BTW, I am fairly sure that I am using the Tektronic Visa Library and not the Agilent, if that means anything. It shouldn't. I used VisaComm instead of several other options because I fould it the easier to understand and work with, although it is probably not the newest communications method.

After extensive testing late last night/early this morning, I have ruled out the MSCOMM control slowing down. My serial operations to our instrument test as fast as they always have even after the memory bloats.

I am almost convinced that memory hogging is not causing the slow down. It slows down if I am running the MDB version or the MDE version, with the MDE version using 30 megs less memory. All string useage is fixed length and I use LSET equating any string data. I dimension arrays and the redim them as needed and redim them to (0) when exiting the routines. All data is held static on the forms and then only saved to a table when hitting the "SAVE/EXIT" button. So rs and dbs are not held open. Forms are not based on queries or recordsets. I removed excess/unused routines from modules. I've decompiled, compressed, etc. So now I am looking in the VisaComm I/O as the slow-down.

I've lost a lot of sleep over this the last month, and I don't feel great right now after only 4.5 hours sack time. What-ever the cause, if I abort the test. Exit Access, Reload Access and continue the test from where I left off (pain in the ass) the test continues at full speed (and quite fast I must add).

Take care,

Dave

Just getting in on the tail end here but a couple of observations:
I'm curious why you're not using LabView instead of VBA to interface to the machine. LabView is made to handle large amounts of data from a GPIB.

But, like you, I opted to use VB instead of LabView because we didn't want to purchase the LabView/G object for writing to Oracle. And I learned quite a bit.

In my experience, I found that we could make code re-entrant and that could cause some problems with bloating after several runs. We also had some problems with fragmenting memory (similar to fragmenting a disk drive) as already alluded to and I believe it was directly caused by the re-entrant code.

In order to fix the re-entrant problem, you can create a static variable at the front of each subroutine/function that won't allow you to be "inside" any sub/function more than once at a time (you have to code this, it is not automatic). With the information gained from disallowing re-entrant code, you could more easily troubleshoot where in the code you are having problems.

Of course, recursive code can cause similar problems but that is not what I'm (necessarily) talking about.

This is in addition to the already great advice you've receieved from Doc, wazz, and Wayne.

 

[georgedwilkinson]

Hmmm, yea, Labview would have been the way to go, I guess. Getting hardware, such as Agilent test equipment, is much easier than getting software, even if the hardware is much, much more expensive.

You are so right. We used to buy equipment that costs in excess of $100K per piece and wouldn't spring $1K for an Oracle connector for LabView.

Can you please explain what you mean by "re-entrant"? I think I know what you mean, but I would rather hear it from you. One of the pains, sometimes, is the multi-threading. I am meaning the ability of a user to click a button to do something else on a form while another operation is running. Yes, that is a pain to stop, if you have a large form with a lot of task capabilities.

Re-entrant code is code that hasn't stopped executing before another process starts it executing again. It is (frequently) caused by VB's attempt to be threaded without being thread safe. This is very common in timers that have too short of an interval but can also happen in other code.

Last night (this morning around 2am) I discoverd that my module that controls the Agilent 11713B, Attenuator Controller, had numberous highly used routines that would assign a resource, such as this:

Set ioMgr = New VisaComLib.ResourceManager
Set instrument = New VisaComLib.FormattedIO488

without doing the following:
instrument.FlushRead
instrument.IO.Close
Set instrument.IO = Nothing
Set instrument = Nothing

Is this not similar to opening a table and database and exiting a function without closing them and equating them to nothing?

I'd be suspicious. You may be able to put some troubleshooting code in to see if this is what's causing your trouble.

I am almost convinced that memory hogging is not causing the slow down. It slows down if I am running the MDB version or the MDE version, with the MDE version using 30 megs less memory. All string useage is fixed length and I use LSET equating any string data. I dimension arrays and the redim them as needed and redim them to (0) when exiting the routines. All data is held static on the forms and then only saved to a table when hitting the "SAVE/EXIT" button. So rs and dbs are not held open. Forms are not based on queries or recordsets. I removed excess/unused routines from modules. I've decompiled, compressed, etc. So now I am looking in the VisaComm I/O as the slow-down.

But still, memory fragmentation can cause this type of behavior.

Have you stepped through (F8) your code in both circumstances? Does it do anything different the second time that it does not do the first?

 

[Daveyk01]

I'd be suspicious. You may be able to put some troubleshooting code in to see if this is what's causing your trouble.

As far as I can tell, properly closing out the IO resourced and equating them to nothing also did nothing. It still slows down. I am looking at the application using 65,400K right now in the Windows Task manager.

In my serial communications routines, there is one receive while/wend loop that reads the port and can wait up to 30 seconds for complete responce. Typical responce time is in ms. Inside that loop, at the top, the is a command "DoEvents". Last night, it didn't seem to make any difference with it remarked out. I had such high hopes.

This morning with is rem'd out, the serial communications to our instrument was too fast and a function that automatically sets a signal height in the instrument screws up royally. Un-remarking it and the routine works very good again.

This may lead credance to your idea that a process may be running twice at the same time. I don't know how at this time. The only thing I can think of is maybe there is code in there that calls a function but continue to run without the original call finishing. I don't think that's possible though. If I call a function, the code from the calling function should not continue until the called fucntion completes, should it not?

Do I need to declare a global boolean variable that each routine checks to see if something else is already running? If not, continue with code. If so, exit or loop until this variable clears. Right before exiting the function clear this global variable? I don't know if that would work or not.

I use a few global variables now and I find, and I don't know why, they loose there value, or the routine looking at them sees it as null. So in those occations, I make sure the calling routine resets the global variable. This has worked well, but once set it should never null out...but it does occationally.

 

[georgedwilkinson]

Try this:

Sub MySub()
Static bRunning as Integer
 
If bRunning Then
Debug.Print "MySub is already running" 'You could potentially use a counter here, too.
Exit Sub
End If
 
bRunning = 1
 
'Do your stuff you normally do.
 
bRunning = 0
End Sub

This will give you a visual indication and will probably change the behavior if it is involved in the problem.

 

[Daveyk01]

Debug.Print "MySub is already running" 'You could potentially use a counter here, too.

Excellent idea. By Counter, what are you suggesting? I would not want control to exit back to the calling code, if possible.

I am not sure if that is happening, but if it is, that is why I ask.

Not ask to my last post stating that my repairs last night didn't seem to do anything, I may take that back. i am sitting here looking at the test completed, Access having sucked up 69,348K of memory, but the test routines with the attenuator, o-scope and function generator did not appear to slow down now, however, serial communication may have. The routines that dealt strickly with talking to the instrument with a non-active dummy signal on them that did not require any GPIB equipment to seem to have slowed down conciderable. The GPIB routines that were horribly slow,, do seem to be running okay now.

 

[Daveyk01]

Try this:

Sub MySub()
Static bRunning as Integer
 
If bRunning Then
Debug.Print "MySub is already running" 'You could potentially use a counter here, too.
Exit Sub
End If...[/quote]
 
I have put this in to a lot of functions and routines with noting trigger so far (I used msgbox instead of debug).
 
I did see something.  If I exit the first page, which stays loaded the whole time because of the comm control, and restart it, that initally appears to do the same thing as exiting the database and restarting.  I need to test that some more.  The first page also contains a SN, Test Date and Testime that all other pages reference, so it stays loaded.
 
I am looking at the comm control now and some other code behind that first page. ??  I don't know, it seems I've gone around in this same circle before..  I am getting dizzy ;)

 

[georgedwilkinson]

Excellent idea. By Counter, what are you suggesting? I would not want control to exit back to the calling code, if possible.

By counter, I mean add another static variable and increment it every time the first static detects re-entrantcy (is that a real word?).

I also noticed in your latest post that you're using the comm control. Naturally, that's what I used when I decided LabView wouldn't work (lack of Oracle connection, you know). I had intermittent problems and eventually sat and watched the data flowing into Oracle while I watched the users using the test equipment.

Sure enough, any time any user opened Excel or used Excel in any way, it would pump corrupt data into Oracle. I never figured out the solution because the company (Uniroyal Optoelectronics) went bankrupt. The solution we used until the end was to not open Excel on those machines. This resolved all the problems we were having.

So, that leads me to ask if someone is running some other program on the computer attached to the GPIB, especially a program in the Office suite or with VB. Are they?

 

[georgedwilkinson]

Something else just came to mind. I recall having variables in VBA re-initialize when I...did something (code reset--hitting the little square button in VBE). I also recall having read that Access 2007 has fixed that problem. I just bring that up because of your statement about exiting the form with your comm control. I'm just grasping at straws here.

I believe Allen Browne articulates that here:
http://www.allenbrowne.com/Access2007.html
TempVarsMacros, queries, code, controlsCollection of variables that can be used application-wide and survive code resets. Example: In AutoExec macro, SetTempVar named LicensedTo to "Acme Corporation", and add this text box to each report header: =[TempVars]![LicensedTo]

 

[The_Doc_Man]

Didn't mean to abandon you, but our rule out here at work is that if we are busy with a "real" project, we cannot use the web very much. I couldn't get back right away. We were having our "flap of the week" or so it seems.

Here is something that I think is crucial.

When you write calls to VBA (or any other BASIC flavor, and several non-BASIC languages do this too), you have a choice of passing arguments by reference or by value. (Some languages offer special structural pointers called descriptors as a third option, but we'll skip that one 'cause VBA doesn't.)

Passing by reference means the argument actually passed is a pointer to the thing being referenced and your subroutine/function uses the only extant copy of same. Passing by value, on the other hand, means something must be copied and then a pointer to the COPY is passed as the thing being referenced. Check for ANY array reference BY VALUE. That will be GUARANTEED to eat your socks, fragment memory, and generally make you miserable.

As to an earlier question: Re-entrant code is something that can be called from multiple places at the same time due to interrupt processing or time-sliced time-sharing. The characteristics of such code include that it contains no "local" variables that could be overwritten at interrupt level, and that all the data it needs will be passed as formal/actual parameters in the call. Any internal variables required for action are defined in a way to reside on the call stack, i.e. part of the call instantiation.

However, I'm also thinking that it is not possible for that to make a terribly big difference for VBA in the Access environment because events aren't interrupts - and aren't interruptable. The sole exception that I can think of is when you call the DoEvents routine to allow events to occur while processing other events. Any other time, event code is single-threaded for any given user because events are actually just subroutine calls triggered by the Access event dispatcher, and it can't trigger another event until you return control to it.

I noticed that one of your posted statistics (.BMP files) showed something like 26 MB but another showed something over 55 MB. That is a bit much in variation. Like,... 29 Mb difference. Over 100% of your smaller memory size. Yet you don't describe the need to frequently compact and repair the DB, i.e. no obvious "permanent" bloating. This makes me think it is memory dynamics that are somehow eating your lunch.

One of your questions in passing reminded me of that book, "Everything I Needed To Know I Learned In Kindergarten." Here's a couple of the rules that seem significantly applicable: If you opened it, close it. If you take it out, put it back. That might be constructive here, though I'm sure you're aware of it. After all, YOU asked the question first. I'm just agreeing with you.

If you use an application object, just remember that it is a pointer to a structure that controls the application. Just 'cause you closed it doesn't mean you released the structure. If you did, say, SET LABXYZ = {a new application object} - this is really just a pointer to the object structure. Doing LABXYZ.CLOSE isn't enough to finish the cleanup. You must also remember to do SET LABXYZ = NOTHING. (That's a keyword that means "null pointer".) Doing that SET operation forces a release of the memory involved in the application object.

 

[Daveyk01]

Thank you very much for that information. I checked through all the code and the only "ByVal" is at the top of one support module and it is:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

It is what provide that nice sleep function needed when dealing with test equipment through I/O ports. For instance, it is nice to wait 200ms after asking the oscilloscope to change a setting. Sleep(200). I picked that sub up somewhere and I use it A LOT, everywhere! Could that be doing something?

Byref is VBA default method of transferring data in a function command line. Is there any way, or setting that could have screwed that up and made ByVal the default?

I have been leaning the program down by removing charts from active forms (they were cool but not needed). That seems to free up a lot of memory use, but I still feel a slow-down. I am beginning to wonder if it is my developement computer screwed up. I am going to have to install it on another computer, my personal laptop and try it on that one.

There is also an instructional image on one of the test pages. It is a BMP. I know it make the program bigger but should mess two much up.

I am cleaning code and leaning everything out to prepare for a release to a test station that I willbe setting up in Coventry, UK in December. For now, I have the only test station and am the only user and am not using it on the network and am not using an OCDB links or anything complicated. That Kerner32 reference is the most complex thing in this program (hah hah). i am thinking about splitting the report module to a second front-end, that was I could remove the MSCHART reference from the testing module. I guess I could have the database start the report database up when the user wants to print the data booklet out, but that would be awkward. It would definitely lean the testing database.

I am up for trying anything at this point. I think the GPIB communication slowdown with the Agilent attenuators is squash and appears to be fine, but I am still seeing general slowdowns after the third test page (form). I can exit the database and restart picking up where I left off and that fourth page is much, much, much fater; if fact it flies!. For example a singal jitter test takes 2 seconds to complere when the program is flying. In the general slow-down mode, that same test takes 7 seconds. That bugs me. Evenm with the slow-down, the automated testing is still about 20 times faster that I can do all those tests manually, but I want the program not to slow down.

 

[Daveyk01]

Another question with Memory Fragmentation..

It is worse to declare these at the start of a module, or pass them on (byref) to every sub or function that touches the data?

Option Compare Database
Private LinLargeData() As Single, dBerror() As Single, Expected() As Single, LinSmallData() As Single
Private Ratio() As Single, Okay() As Boolean, Z As Integer
Private Status As Variant, AmpCntrlData() As Single, AmpCntrlExpData() As Single
Private LinFailureNotes As String, BeamFailureNote(16) As String * 256, FastRead As Boolean

 

[The_Doc_Man]

None of those represent enough memory to account for the observed growth and shrinkage. I'm talking dynamic, heap-based string allocation or structure allocation of something that could eventually account for 29 MB (the difference between the high and low memories shown in your performance captures.)

Trust me on this one... a 1 to 2 Mb fluctuation for most PCs is small beans. You've got over double the program's base size for this one. We ain't looking at scalars, even if some of them are long-string scalars. You've got one piddlin' array there, 16 * 256 bytes, which is 4 Kb.

But your question is valid in this sense. Making those variables private to the module but in a declaration area makes them non-dynamic within that module. If that is a general module, they are static-per-session. If a class module, static only while the class's parent is open. In either case, they won't be created and destroyed on a per-call level.

As to passing ByRef, ALWAYS use that when it makes sense programmatically. Use ByVal when necessary. The need of the problem always is the final arbiter of what you can do, not my preferences on one method or the other.

I'm hoping my fellow forumites can come up with some other suggestions because I'm running out of ideas on where to look. You've got some detective work left, I'm afraid.

Oh, don't worry about Sleep. It is generally benign and absolutely has never been known to make a memory hiccup of any magnitude that I've ever seen. And I've seen it since Ac97. I'd say it is stable.

 

[Daveyk01]

Thanks, I am in Minapolis Airport this morning and taking a break from this for a day. I will be back home tomorrow night.

Our service database which I have adding too since 1999 has a BE that is 200 megs, shared by at least 30 people, a huge front end with OCDB links to out data 3 system, that is at least 35 Megs in size (decompiled and compressed), but doesn't use very much memory. It has 4-10 times the queries and reports defined in this smaller project. I probably do use a lot more arrays in this project than in the service database. What I do notice with this test project is that every form that opens takes memory, makes sense, but doesn't give it all back when the form closes. With each form that opens more and more memory is used.

The big difference between the two projects is the references (attached image of references).

Maybe there is a reason the MS didn't include MSCOMM with VBA <grin>.

 

[Daveyk01]

I have been doing more browsing of example and may just have found something I am doing wrong with my GPIB programming. Here is an example of two functions for the o-scope:

<code>
'===================================================================================
Function tdsSetMeasMode(Mode As Integer)
Dim ioMgr As VisaComLib.ResourceManager
Dim instrument As VisaComLib.FormattedIO488
Dim idn As Variant
'
' Mode = 0 then Percent
' Mode = 1 then Aboslute
'
Set ioMgr = New VisaComLib.ResourceManager
Set instrument = New VisaComLib.FormattedIO488
' Address
Set instrument.IO = ioMgr.Open(TDSAdr)
'
If Mode = 1 Then
instrument.WriteString "MEASU:REFL:METH ABS"
Else
instrument.WriteString "MEASU:REFL:METH PERC"
End If
'
instrument.FlushRead
instrument.IO.Close
Set ioMgr = Nothing
Set instrument = Nothing
'
Exit Function
End Function
'===================================================================================
Function tdsSetMeasAbsMid1(Volts As Single)
Dim ioMgr As VisaComLib.ResourceManager
Dim instrument As VisaComLib.FormattedIO488
Dim idn As Variant
'
If tdsGetMeasMode <> 1 Then Exit Function
'
Set ioMgr = New VisaComLib.ResourceManager
Set instrument = New VisaComLib.FormattedIO488
' Address
Set instrument.IO = ioMgr.Open(TDSAdr)
'
instrument.WriteString "MEASU:REFL:ABS:MID " & Trim(Str(Volts))
'
instrument.FlushRead
instrument.IO.Close
Set ioMgr = Nothing
Set instrument = Nothing
Exit Function
End Function
/<code>

I found examples where the library is opened once per program per instrument and never closed. I wonder if I should just initialize it in a public function of the instrument code module and then don't close it again? Hmmm.... Maybe every instrument function is opening a new occurance of the library? I have to do more research on this when I get back from this quick business trip to South Dakota. This could be the whole problem.

 

[Daveyk01]

Okay, I think I have this slow down fixed now. I had used the method of controlling the test equipment as I showed in my last post. They were based on examples from Agilent. Those examples were very minimal. I kept thinking about memory fragmentation.

I now dimension the IO libraries globally in a small declaration module, like this:
Global ioMgr As VisaComLib.ResourceManager
Global Scope As VisaComLib.FormattedIO488
Global Attenuator As VisaComLib.FormattedIO488
Global Generator As VisaComLib.FormattedIO488
Global DVM As VisaComLib.FormattedIO488
Global PowerSupply As VisaComLib.FormattedIO488
-

Now when a form module wants to change a oscillocope instrument setting, it calls a tdsOpenScope routine that simply does a:
Function tdsOpenScope()
Set ioMgr = New VisaComLib.ResourceManager
Set Scope = New VisaComLib.FormattedIO488
' Address
Set Scope.IO = ioMgr.Open(TDSAdr)
Exit Function
End Function
-
When the calling form is finished using the Scope it calls a tdsCloseScope:
Function tdsCloseScope()
Scope.FlushRead
Scope.IO.Close
Set Scope.IO = Nothing
Set Scope = Nothing
Exit Function
End Function
-

The actual scope routines just send a command to the instrument.

This put a stop to the constant dimensioning/declaring of the visaComLib.ResourceManager. I starting treating like the MSCOMM.OCX control on the main menu that handles the serial communications. It is opened and not closed until all testing is done.

This, apparently, is setting up ioMgr for use similar to Opening the seial port with the MSCOMM control.

The Access program gobbles a large amount of memory right off the bad now (somewhere around 55-60 megs) but it doesn't constantly grow and the program does not slow down now. In fact, I had to use a few sleep commands where I never needed them before!

It took a long while but the discussions of fragmenting memory finally crept in to my thoughts (must be the mad cow). I had this idea, out of the blue, on a plane flight to Minapplois this week. I am now fininshing the implimentation of all the changes.

There will probalby a few glitches I have to work out, but the program is now flying alone at a much, much faster pace. The tests are finishing probably 30 minutes sooner. (lots and lots of tests)