Memory resources (XP SP2)

Topic

New Reply

Does anyone know of any memory leaks in the XP reporting
– I’ve added a bit of code to the report Detail_Format event to give me grey and white alternate shading, which worked fine in my A97 databases.

I’ve now moved to XP and VSS. I tried a couple of the converted databases and each time I run the report the amount of memory used just goes up and up, e.g just run one report a few time, and it basically locked the machine up.
Task Manager was showing about 400 Mb, with another 900 Mb in virtual memory
And this is on an XP Pro machine with 512 Mb memory and 768 virtual memory

Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.Section(0).BackColor = IIf(gfGray, cColorWhite, cColorGray)
    ' Next time, do it the opposite of the way you did it this time.
    gfGray = Not gfGray
End Sub 

Reply | Quote

Replies

I don’t have information about potential memory leaks.

I notice that you use IIf in your code. This function is useful for queries and expressions (e.g. the control source of a text box on a form or report). Although it’s available as a function in VBA, you should use If … Then … [Else …] End If in module code – it takes up more space, but it’s easier to read and to debug. Here is some code from one of mey reports – it’s an event procedure for the OnPrint event of the OnFormat event, but it does the same:

Private fGrey As Boolean
Private Const vbGrey = &HE0E0E0

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
fGrey = Not fGrey
If fGrey Then
Detail.BackColor = vbWhite
Else
Detail.BackColor = vbGrey
End If
End Sub

Reply | Quote

You need to test for FormatCount=1 or you’ll be running this code at least twice as often as needed.

Reply | Quote

Thanks Charlotte and Hans, for the good suggestions

I’ve tracked back through the report and underlying query.
The problem is in a function that the query uses, not the actual report.
Run the query and watch Task Manager, , there goes 1/2 Gb, yes Gb!, of memory, for only 60 records returned from the Oracle database
That’ll teach me to use someone elses code without proper checking, previously I’d been testing against an Oracle database with only a few record in, now there’s a lot more and it’s showing up the inefficency of the code
– time to see what I can do to improve it.

Reply | Quote

More on the memory leak. It’s not the code, it’s something even more basic than that.
If I ODBC link an Oracle table with approx. 10,000 records, create a simple query on it, no criteria, just select a few of the fields.
Watch the Msaccess.exe memory in TaskManager, open the query, and it increases by about 300K, but when I close the query, only about 1/2 of the memory comes back
Even worse, if I open the query, then use the CTRL + HOME / END to move forwards and backwards through the records, the memory use just keeps on climbing

And the memory use increases with more tables, e.g. single table 10K records, about 500K, two joined tables, both about 10K records, and memory use is 900K

The actual query that ends up using 1/2 Gb has 5 checks against settings, e.g. like a match for officer, case type, etc
– it’s almost as if it’s doing a cross join, even though the query definitely has a join in it

Reply | Quote

What ODBC driver are you using to link to the Oracle tables? Microsoft has one, and I believe there are several available from Oracle. It’s entirely possible that the driver itself has a memory leak. It also sounds like the queries are bringing back the complete table and doing the work in Access rather than on the server. I’ve not tried to do a pass-through query using Oracle, but that might give you a major performance boost and reduce your memory usage dramatically, presuming that the result set returned to you should be relatively small.

Reply | Quote

Wendell, I’m using the Oracle driver, version 8.01.78.00
– will give the Microsoft driver a go and see if it it’s any better
So far I’ve tried creating a view in Oracle, and that seems to have greatly improved the situation, like my memory usage is about 1/20th of the previous

Reply | Quote

Yes, further playing with basing the report on the Oracle view, rather than doing the join in Access, shows a much reduced, BUT NOT eliminated, memory usage
The memory is still not released though, until I exit Access

Reply | Quote

And just to keep this topic open

One query through to Oracle I’ve got works fine, and perhaps more importantly, hardly using any memory resources
– returns about 100 records

SELECT UNIFORM_CPINFO.REFVAL, 
UNIFORM_CPINFO.ADDRESS, 
UNIFORM_CPRISKSA.PROPNO, 
UNIFORM_CPRISKSA.PRIORITY, 
tblLookupI1FoodSafety.Description, 
UNIFORM_CPRISKSA.EHINSTYPE, 
[qryMapEHSPI EHINSTYPE].MAPVALUE, 
UNIFORM_CPRISKSA.SCHEDDATE, 
UNIFORM_CPRISKSA.ACTDATE, 
DateDiff("d",[SCHEDDATE],[ACTDATE]) AS DaysTaken, 
IIf([DaysTaken]<=14,1,0) AS VisitWithinTime
FROM UNIFORM_CPINFO 
INNER JOIN (((qryI1_Premises_FoodSafety INNER JOIN ([qryMapEHSPI EHINSTYPE] 
INNER JOIN UNIFORM_CPRISKSA ON [qryMapEHSPI EHINSTYPE].CODEVALUE = UNIFORM_CPRISKSA.EHINSTYPE) ON qryI1_Premises_FoodSafety.PROPNO = UNIFORM_CPRISKSA.PROPNO) 
INNER JOIN tblLookupI1FoodSafety ON UNIFORM_CPRISKSA.PRIORITY = tblLookupI1FoodSafety.PRIORITY) 
INNER JOIN UNIFORM_CPINSPEC ON (UNIFORM_CPRISKSA.PROPNO = UNIFORM_CPINSPEC.PROPNO) 
AND (UNIFORM_CPRISKSA.CASSYS = UNIFORM_CPINSPEC.CASSYS) 
AND (UNIFORM_CPRISKSA.EHINSTYPE = UNIFORM_CPINSPEC.EHINSTYPE)) ON UNIFORM_CPINFO.PROPNO = UNIFORM_CPINSPEC.PROPNO
WHERE ((([qryMapEHSPI EHINSTYPE].MAPVALUE)="INDIC1") 
AND ((UNIFORM_CPRISKSA.SCHEDDATE) Between [forms]![frmMainMenu]![DateFrom] And CDate(Format([forms]![frmMainMenu]![DateUntil],"dd/mm/yyyy") & " 23:59:59")));

Yet when I copy the query and change for the other inspection type, (using two other tables + INDIC2 rather than INDIC1), I get 35 records, but the memory is eaten up

SELECT UNIFORM_CPINFO.REFVAL, 
UNIFORM_CPINFO.ADDRESS, 
UNIFORM_CPRISKSA.PROPNO, 
UNIFORM_CPRISKSA.PRIORITY, 
tblLookupI2WorkplaceSafety.Description, 
UNIFORM_CPRISKSA.EHINSTYPE, 
[qryMapEHSPI EHINSTYPE].MAPVALUE, 
UNIFORM_CPRISKSA.SCHEDDATE, 
UNIFORM_CPRISKSA.ACTDATE, 
DateDiff("d",[SCHEDDATE],[ACTDATE]) AS DaysTaken, 
IIf([DaysTaken]<=14,1,0) AS VisitWithinTime
FROM (((UNIFORM_CPINFO INNER JOIN (UNIFORM_CPRISKSA 
INNER JOIN UNIFORM_CPINSPEC ON (UNIFORM_CPINSPEC.EHINSTYPE = UNIFORM_CPRISKSA.EHINSTYPE) AND (UNIFORM_CPINSPEC.CASSYS = UNIFORM_CPRISKSA.CASSYS) 
AND (UNIFORM_CPRISKSA.PROPNO = UNIFORM_CPINSPEC.PROPNO)) ON UNIFORM_CPINFO.PROPNO = UNIFORM_CPINSPEC.PROPNO) 
INNER JOIN tblLookupI2WorkplaceSafety ON UNIFORM_CPRISKSA.PRIORITY = tblLookupI2WorkplaceSafety.PRIORITY) 
INNER JOIN qryI2_Premises_WorkSafety ON UNIFORM_CPRISKSA.PROPNO = qryI2_Premises_WorkSafety.PROPNO) 
INNER JOIN [qryMapEHSPI EHINSTYPE] ON UNIFORM_CPRISKSA.EHINSTYPE = [qryMapEHSPI EHINSTYPE].CODEVALUE
WHERE ((([qryMapEHSPI EHINSTYPE].MAPVALUE)="INDIC2") 
AND ((UNIFORM_CPRISKSA.SCHEDDATE) Between [forms]![frmMainMenu]![DateFrom] And CDate(Format([forms]![frmMainMenu]![DateUntil],"dd/mm/yyyy") & " 23:59:59")));

Sorry about the SQL, but the only differences, that I at least think I’ve done, are using a couple of different lookup tables. I’ve checked these and they work fine.

So why such a difference in memory usage. Now looking at the SQL, it’s different. How has the Access “SQL compiler” or whatever it’s called that converts QBE grid to SQL, changed the bracketing ?

And I really am going to switch to Oracle view (when I find the time)

Reply | Quote

Problem solved
– there was a memory leak in the Oracle 8.01.78.00 ODBC driver
– they’ve issued a new 78b driver and this works much better, well instead of using about >1Gb of memory, it now uses a Mb or two

Reply | Quote