How to free RAM-mem in between coded query-steps (qdf) 2

[Checking]

Hi there; after searching related threads (& having a headache now), I'm ending up in starting this thread.
During running code (see below), I see my 'available RAM Memory' shrinking from > 300MB (physical 512 installed)to a very low level (< 30MB) and not freeing-up between steps. The thing that worries me is, at end of code, and after 'dbs.Close' there is no way I can free-up RAM without closing the database first.
The code is running through a lot of dynamic Qdf's (SQL's stored in tbl_SQL. Between each Qdf (intensive SQL-step), I want to free RAM in order not to exhaust the program and being able to run the next step smoothly. I want to free it similar to as when you close the database manually, reopen and proceed next qdf. Here's the code 'as is':
-----------
Private Sub cmdLoadData_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim rstLoadDB2Data, rstTskAuth, rstCountLines As DAO.Recordset
Dim ctl As Control
Set ctl = Me.IndProgress
ctl.Max = 100 '= max % of progressbar
Dim qdf As QueryDef
Dim idx As Integer
Dim strOvw, strCPU, strLst, strArea, strSqlLines, strStart, strEnd, strLaps, strSequence, strRptD2R, strRptR2D, strRptPND, strRptPth, strFilCrDtDiscr, strReport, strReportObj As String
Dim fs, F, d, n
Dim sql, sql2 As String
Dim WMI
Dim wmiWin32Objects
Dim wmiWin32Object
strLst = Now() & " § Starting Refresh Procedure "
strStart = Now()
strLaps = Now()
DoCmd.SetWarnings False
ctl = 0
ctl.Max = 100 'Set max count for the progressbar
DoCmd.RunCommand acCmdRefreshPage
Set rstTskAuth = dbs.OpenRecordset("tblTaskAuthLists", dbOpenDynaset) 'User authorized ?
rstTskAuth.FindFirst "[tskName] = '" & "DataLoad" & "' and [tskNetUser] = '" & Form_frmLogin!LogonNetUser & "'"
If rstTskAuth.NoMatch Then
MsgBox (Form_frmLogin!LogonNetUser & " not authorized")
Else 'User = authorized
strArea = rstTskAuth!tskArea '= User's BusinessArea
rstTskAuth.Close
Set rstTskAuth = Nothing
Set rstCountLines = dbs.OpenRecordset("tbl_SQL", dbOpenDynaset) 'Counting sql lines User's BusinessArea
Do Until rstCountLines.EOF
With rstCountLines
If [rstCountLines]![SqlCat] = "RMS_DataRefresh" And [rstCountLines]![SqlBusinessDivision] = strArea Then
strSqlLines = strSqlLines + 1
End If
End With
rstCountLines.MoveNext
Loop
rstCountLines.Close
Set rstCountLines = Nothing
DoCmd.RunCommand acCmdRefreshPage
With Me.lstOvw
lstOvw.RowSourceType = "Value List"
lstOvw.RowSource = strLst
End With
DoCmd.RunCommand acCmdRefreshPage
For idx = 1 To strSqlLines Step 1
Set rstLoadDB2Data = dbs.OpenRecordset("tbl_SQL", dbOpenDynaset)
DoCmd.RunCommand acCmdRefreshPage
rstLoadDB2Data.FindFirst "[SqlCat] = 'RMS_DataRefresh' and [SqlBusinessDivision] = '" & strArea & "' and int([SqlSequence]) = '" & idx & "' and int([SqlActiv]) = '-1'"
If rstLoadDB2Data.NoMatch Then
Else
Set qdf = dbs.CreateQueryDef(idx, rstLoadDB2Data!SqlString)
DoCmd.RunCommand acCmdRefreshPage
DoCmd.OpenQuery idx, acViewNormal, acEdit
Set WMI = GetObject("WinMgmts://" & ComputerName)
Set wmiWin32Objects = WMI.InstancesOf("Win32_Processor")
With wmiWin32Object
For Each wmiWin32Object In wmiWin32Objects
strCPU = .loadpercentage
Next
End With
Set WMI = Nothing
Set wmiWin32Objects = Nothing
Set wmiWin32Object = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set F = fso.GetFile(CurrentProject.Path & "\" & CurrentProject.name)
strLst = strLst & ";" & Now() & " § " & rstLoadDB2Data!SqlObject & rstLoadDB2Data!SqlSequence & " " & rstLoadDB2Data!SqlNaming & ", Time elapsed: " & CVDate(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())) - TimeSerial(Hour(strLaps), Minute(strLaps), Second(strLaps))) & ", Curr dB-Size: " & ((F.Size / 1024000) & " MB") & ", RAM-AVA: " & ((atGetMem(2) / 1024000) & " MB") & " or " & Int((atGetMem(2) / atGetMem(1) * 1000)) / 10 & " %" & ", CPU dropped to: " & strCPU & " %"
DoCmd.RunCommand acCmdRefreshPage
With Me.lstOvw
lstOvw.RowSourceType = "Value List"
lstOvw.RowSource = strLst
End With
DoCmd.RunCommand acCmdRefreshPage
rstLoadDB2Data.Edit
rstLoadDB2Data!SqlRunDate = Now()
ctl = Int((idx / strSqlLines) * 100)
rstLoadDB2Data!SqlRunComment = "Refreshed successfully"
rstLoadDB2Data!SqlTimLoad = CVDate(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())) - TimeSerial(Hour(strLaps), Minute(strLaps), Second(strLaps)))
rstLoadDB2Data.Update
DoCmd.RunCommand acCmdRefreshPage
DoCmd.RunCommand acCmdRefreshPage
DoCmd.DeleteObject acQuery, idx
End If
rstLoadDB2Data.Close
Set rstLoadDB2Data = Nothing
strLaps = Now()

Next idx
Set qdf = Nothing
Let idx = 0
dbs.Close
Set dbs = Nothing
End If
Let ctl = 0

strLst = strLst & ";-----;" & Now() & " § Update Procedure Successfull " & ";-----;Total Time elapsed: " & CVDate(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())) - TimeSerial(Hour(strStart), Minute(strStart), Second(strStart)))
With Me.lstOvw
lstOvw.RowSourceType = "Value List"
lstOvw.RowSource = strLst
End With


DoCmd.SetWarnings True
ErrorHandler:
Exit Sub
End Sub
---------------
Probably overlooked something important. Can anybody suggest ? Thks in advance !!

 

[LittleSmudge]

Well for a start, this line

Dim rstLoadDB2Data, rstTskAuth, rstCountLines As DAO.Recordset

will soak up lots of unnecessary memory

As will

Dim strOvw, strCPU, strLst, strArea, strSqlLines, strStart, strEnd, strLaps, strSequence, strRptD2R, strRptR2D, strRptPND, strRptPth, strFilCrDtDiscr, strReport, strReportObj As String
Dim fs, F, d, n
Dim sql, sql2 As String

The first lot is actually the same as

Dim rstLoadDB2Data As Varient
Dim rstTskAuth As Varient
Dim rstCountLines As DAO.Recordset

And as Varients take up 'gobbs' of memory you are setting up for a problem to start with.

You are using Set rstxxx = Nothing which should relinquish the memory allocation.
However, I'm not so sure that that will work when they are Dimmed as Varients.

And you have no way of relinquishing all that Varient memory that you have soaked up with all the other multi Dim lines.


Only use Varients where you MUST be able to hold Null values or where you are unsure of the data type you'll need to hold.


'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Checking]

Thks LittleSmudge; I'll take the usefull information into account. Actually, I'm in the middle of reviewing an existing process (using traditional queries) to a more dynamic automated process. The whole process retreives lots of info from-out DB2 tables.
Also within this process I marked heavy memory drops to aprox the same level as in the new code-automated process.
It never recovers without closing the database itself.
=> My question is actually how memory can be freed before the next SQLquery runs. I'd like to become the same as closing-the-database-reopen-and-continue-next SQLquery.
Anybody an idea ? / B. Rgds



Belgium

 

[Checking]

Herewith a processreport generated by above code to indicate the symptom:
9/02/2005 6:41:41 § Starting RMS-Refresh Procedure
9/02/2005 6:41:50 § Qdf1 Clearing tbl_Repairs, Time elapsed: 0:00:09, Curr dB-Size: 168.776 MB, RAM-AVA: 233.964 MB or 44.6 %, CPU Load returned to: 1 %
9/02/2005 6:45:31 § Qdf2 Loading tbl_Repairs, Time elapsed: 0:03:41, Curr dB-Size: 168.776 MB, RAM-AVA: 52.468 MB or 10 %, CPU Load returned to: 68 %
9/02/2005 6:45:32 § Procedure Successfull

Using: XP-Pro / Access2K



Belgium

 

[VBslammer]

If you want to write your code like ASP script using all variants, then you'll need to run it on a Web server with plenty of resources .

Here's the MSDN comment:

Consider Alternatives to Variant Data Types

The Variant data type is extremely flexible, but it is also larger than any of the other data types. When you must squeeze every last byte out of your application, consider replacing Variant variables, and especially arrays of Variant variables, with other data types.

Each Variant takes 16 bytes, compared to 2 for an Integer or 8 for a Double. Variable-length String variables use 4 bytes plus 1 byte per character in the string, but each Variant containing a string takes 16 bytes plus 1 byte per character in the string. Because they are so large, Variant variables are particularly troublesome when used as local variables or arguments to procedures, because they quickly consume stack space.


VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Checking]

Thks so much LittleSmudge & VBslammer; altered Variants to more apropriate Dim's & removed obsolete Dim's and consequentially doubled avalable RAM after running procedure.
At the end of the procedure, RAM however remains at the lowered level; not freeing upwards. Only closing database will free all mem. For the time being, I think I can live with it. Wonder if there's a solution for that.
Grtz from

Belgium

 

[LittleSmudge]

Yes, I understand your issue about not releasing memory after the objects are released.

I'd certainly expect to see the memory come back, especially now you're working with correctly Dimmed objects.

I'll have to think on that one some more.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[VBslammer]

Access has a major flaw in that all code that gets called in standard modules at runtime remains in memory until Access shuts down. In older versions of Access (before Access 95), all standard modules were fully loaded during startup. Now, whenever you call a routine or reference a variable in a standard module, the entire module loads and remains resident for the remainder of the Access session.

Another hidden gotcha for developers: whenever you use the [tt]Debug -> Complie Project[/tt] menu option, all modules in the project are loaded into memory. For some databases that may not be too bad, but I have some that include dozens of modules so that gets to be alot of overhead.

One method for increasing efficiency is to use each standard module for a very specific purpose, with only a couple of routines or declarations in it. That way only a small amount of memory is loaded each time someting is referenced at runtime. I've seen some modules that had 8-10 thousand lines of code in them, and I've seen others with a single function in them.

Another method is to put as much of your re-useable code into class modules, which can be created and destroyed at runtime to free up resources.

Either way, database systems use huge amounts of resources when processing huge amounts of data - and there isn't much you can do about it. Have you ever looked at the size of SQL Server logs?

I don't think it's uncommon to run out of RAM completely and start caching data to disk when you're doing large numbers of transactions. The problems start when you run out of disk space.

One other note, garbage collection isn't an instantaneous process. When you set an object to nothing, that doesn't mean its address space is immediately available to your application, especially if you're performing a cpu-intensive process that is hogging all the processing power. Garbage collection is normally a background process that kicks in when cpu usage is low.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Checking]

Thks again both of you & Tek-Tips !
Yr detailed info helped me a lot... & clears quiet some questions.

.... The more you know, the more you know what you don't know ....




Belgium