Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Memory resources (XP SP2)

    Does anyone know of any memory leaks in the XP reporting <img src=/S/question.gif border=0 alt=question width=15 height=15>
    - 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 <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    <pre>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 </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Memory resources (XP SP2)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memory resources (XP SP2)

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

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory resources (XP SP2)

    Thanks Charlotte and Hans, for the good suggestions <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    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, <img src=/S/woops.gif border=0 alt=woops width=58 height=36>, there goes 1/2 Gb, yes Gb!, of memory, for only 60 records returned from the Oracle database <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    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.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory resources (XP SP2)

    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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    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 <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memory resources (XP SP2)

    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.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory resources (XP SP2)

    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

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory resources (XP SP2)

    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

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory resources (XP SP2)

    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
    <pre>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")));</pre>

    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 <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    <pre>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")));</pre>


    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)

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memory resources (XP SP2)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •