Memory Usage Problem

SHardy

Hi,

I have written a function that works through a list of tables/fields updating user IDs with a new format. This is necessary as part of our migration to Active Directory, and also to use the opportunity to have a tidy up of historic data.

However, there appears to be a problem when running the code. The memory usage against the finsql.exe service keeps increasing and increasing. When run against a copy of the live database (having very large tables) this results in an error being thrown because the memory usage gets too high.

One thought I had was to maybe work through the tables "chunk" at a time (somehow) to avoid having so many records in a recordref variable. However, this still left me with the thought that "why does it not give the resources back when it moves onto the next table?".

As such, I have gone through the code and cleared all variables where possible. Having run this against a smaller copy of the database, it has not done anything to alleviate the problem. The memory usage still constantly rises as it goes from table to table. I did notice some "memory give back", but only while it was working through the bigger tables?

I have attached the code below and would appreciate any feedback regarding where I have gone wrong. Could it be related to the size of some of the tables?

Thanks,

Simon (clearly a newbie :? )

OnRun()
timProgress := TIME;
tblRcdCount := 0;
Window.OPEN(Text001,rTableList.TableName,rTableList.FieldName,tblRcdCount);
rTableList.RESET;
IF rTableList.FIND('-') THEN REPEAT
  tblRenameKey := FALSE;
  CLEAR(tblRcdCount);
  Window.UPDATE;
  CLEAR(rFieldList);
  rFieldList.SETRANGE(TableName,rTableList.TableName);
  rFieldList.SETRANGE(FieldName,rTableList.FieldName);
  IF rFieldList.FIND('-') THEN BEGIN
    tblTableRef.OPEN(rFieldList.TableNo,FALSE);
    tblKeyFields := tblTableRef.CURRENTKEY;
    IF STRPOS(tblKeyFields,rTableList.FieldName) > 0 THEN
      tblRenameKey := TRUE;
    CLEAR(tblKeyFields);
    IF tblTableRef.FIND('-') THEN REPEAT
      tblRcdCount := tblRcdCount + 1;
      tblFieldRef := tblTableRef.FIELD(rFieldList."No.");
      OldID := tblFieldRef.VALUE;
      rIDList.RESET;
      rIDList.SETCURRENTKEY("OLD User ID");
      rIDList.SETRANGE("OLD User ID",OldID);
      IF rIDList.FIND('-') THEN REPEAT
        IF OldID <> rIDList."New User ID" THEN
          IF NOT tblRenameKey THEN BEGIN
            tblTableRef2 := tblTableRef.DUPLICATE;
            tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
            tblFieldRef2.VALUE := rIDList."New User ID";
            tblTableRef2.MODIFY(FALSE);
          END ELSE BEGIN
            tblTableRef2 := tblTableRef.DUPLICATE;
            tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
            tblTableRef2.DELETE(FALSE);
            tblFieldRef2.VALUE := rIDList."New User ID";
            tblTableRef2.INSERT(FALSE);
          END;
        CLEAR(tblFieldRef2);
        CLEAR(tblTableRef2);
        IF timProgress < TIME - 3000 THEN BEGIN
          timProgress := TIME;
          Window.UPDATE;
          COMMIT;
        END;
      UNTIL rIDList.NEXT = 0;
      CLEAR(tblFieldRef);
      CLEAR(rIDList);
    UNTIL tblTableRef.NEXT = 0;
  END;
  CLEAR(tblTableRef);
UNTIL rTableList.NEXT = 0;
Window.CLOSE;

kriki

Lets first try to pinpoint what creates the problem. (My prime suspect is the DUPLICATE-statement.)
Lets first try to loop all records BUT WITHOUT making changes, so put the following code in comment:

IF NOT tblRenameKey THEN BEGIN
            tblTableRef2 := tblTableRef.DUPLICATE;
            tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
            tblFieldRef2.VALUE := rIDList."New User ID";
            tblTableRef2.MODIFY(FALSE);
          END ELSE BEGIN
            tblTableRef2 := tblTableRef.DUPLICATE;
            tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
            tblTableRef2.DELETE(FALSE);
            tblFieldRef2.VALUE := rIDList."New User ID";
            tblTableRef2.INSERT(FALSE);
          END;

If this works the problem is somewhere in here.
In this case : try to put the code in a function and make variables "tblTableRef2","tblFieldRef2" local to the function.

SHardy

Hi,

Thanks again.

I commented out those lines, as suggested. However, the memory usage still continued to grow and grow. I did notice more memory "fall back", but I don't know for sure if it did actually happen more often.

It clearly still wasn't giving back the resources. Once the function completed, the memory usage dropped down really low again.

Any other thoughts? Is there something that I am not releasing?

Thanks

ara3n

you can issue a commit, but put in right place. You can also try to run it two parts with filter.

[edit] NM I just saw the commit statement.

SHardy

Ok, I found an old post from some guy having a similar problem when he was trying to import a VERY large text file. Even when splitting it into lots of smaller text files, he still had the same problem.

This was apparently solved by explicitly setting the "Commit Cache" to "Yes" (it was previously the default "<Yes>").

However, I cannot see this setting either via the options or the File>Database>Information form.

Could this be because we are running Navision over SQL Server? If so, then presumably this is a "red herring"?

Thanks

ara3n

The commit cache can be changed from Tools->Option->Commit Cache. And it only for native version.
Try running this at Server. I know this won't help the memory issue, but it will improve performance.

kriki

Some other things to try:
-don't use the dialogbox

- put the following code in a function (or even a new codeunit)and use only local variables

tblRenameKey := FALSE;
  CLEAR(tblRcdCount);
  Window.UPDATE;
  CLEAR(rFieldList);
  rFieldList.SETRANGE(TableName,rTableList.TableName);
  rFieldList.SETRANGE(FieldName,rTableList.FieldName);
  IF rFieldList.FIND('-') THEN BEGIN
    tblTableRef.OPEN(rFieldList.TableNo,FALSE);
    tblKeyFields := tblTableRef.CURRENTKEY;
    IF STRPOS(tblKeyFields,rTableList.FieldName) > 0 THEN
      tblRenameKey := TRUE;
    CLEAR(tblKeyFields);
    IF tblTableRef.FIND('-') THEN REPEAT
      tblRcdCount := tblRcdCount + 1;
      tblFieldRef := tblTableRef.FIELD(rFieldList."No.");
      OldID := tblFieldRef.VALUE;
      rIDList.RESET;
      rIDList.SETCURRENTKEY("OLD User ID");
      rIDList.SETRANGE("OLD User ID",OldID);
      IF rIDList.FIND('-') THEN REPEAT
        IF OldID <> rIDList."New User ID" THEN
          IF NOT tblRenameKey THEN BEGIN
            tblTableRef2 := tblTableRef.DUPLICATE;
            tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
            tblFieldRef2.VALUE := rIDList."New User ID";
            tblTableRef2.MODIFY(FALSE);
          END ELSE BEGIN
            tblTableRef2 := tblTableRef.DUPLICATE;
            tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
            tblTableRef2.DELETE(FALSE);
            tblFieldRef2.VALUE := rIDList."New User ID";
            tblTableRef2.INSERT(FALSE);
          END;
        CLEAR(tblFieldRef2);
        CLEAR(tblTableRef2);
        IF timProgress < TIME - 3000 THEN BEGIN
          timProgress := TIME;
          Window.UPDATE;
          COMMIT;
        END;
      UNTIL rIDList.NEXT = 0;
      CLEAR(tblFieldRef);
      CLEAR(rIDList);
    UNTIL tblTableRef.NEXT = 0;
  END;
  CLEAR(tblTableRef);

SHardy

Removing the dialog didn't help, but...

\:D/ WOW! How strange. I moved all the code from within the first loop into a separate function with local variables, and it has made the world of difference.

So, even though I was performing a CLEAR on all variables where possible, it still wasn't letting them go completely? I assume that by putting the code into a function it forces it to kill them off each time.

Now to test it in the full size database on the server. Hopefully I will be able to do this tonight.


:-k Just a thought, but would I be any better off pulling out code from the other loops into separate functions? Or should this not be necessary?

Many Thanks
Simon

DenSter

well if you don't need the variables beyond the scope of the loop then it is something you may want to try. Apparently releasing the local variables in the other function cleans up memory, and CLEAR does not really clear the memory after all.

matteo_montanari

Hi

RecordRef datased must be closed after usage and before a new OPEN...

Call CLOSE method of recordref after:
UNTIL tblTableRef.NEXT = 0;

Using an external function resolve your issue because you have declared this recordset as local and navision automatically freeze a local variable when exiting from a function...

Matteo

SHardy

I suppose that makes sense. If you OPEN then you must CLOSE. These things become so obvious once you know.

So the CLOSE will free up the memory? Well, no point changing it now. But I will bear it in mind. Thank you.

Not yet been able to test on full size database server side. Will update as 'solved' once I have done so.


Many thanks,

ara3n

Is this mentioned anywhere in the manaul about memory issue and how close releases memory?

SHardy

Oh... I've had enough of this now!

Got to test it on a copy of the live system. Therefore against full size tables. Could see a change in memory usage, but was still experiencing problems. Could only assume that this was related to the size of the tables, as it worked fine on my local copy.

I made further changes to the code to try and deal with this problem. Could then see major changes to the memory usage on my local database. This amended code was then run against a fresh copy of the live system at the weekend. This ran through fine with no problems.

Did a data test this morning, and there are many IDs that have not been updated. There seems to be no pattern to how this has happened. I could have a table where x amount of records for a particular user have not been updated, but in the same table & for the same user y amount of records have been updated!?

I tried copying one of these tables over to my local database, and re-ran the code against that one table. All records were successfully updated!

I am now really confused as to what is happening, and I am getting very close to my deadline for when it is required.

Is the code skipping records for some reason?
If it is, then why is this not happening on the smaller, local database?

Please find a copy of the new code below. Does anyone have any ideas what might be going wrong?

Many Thanks,
Simon

OnRun()
timProgress := TIME;
tblRcdCount := 0;
Window.OPEN(Text001,rTableList.TableName,rTableList.FieldName,tblRcdCount);
rTableList.RESET;
IF rTableList.FIND('-') THEN REPEAT
  TheLoopyBit;
UNTIL rTableList.NEXT = 0;
Window.CLOSE;
COMMIT;
MESSAGE(FORMAT(TODAY) + ' @ ' + FORMAT(TIME));

TheLoopyBit()
tblRenameKey := FALSE;
CLEAR(tblRcdCount);
Window.UPDATE;
CLEAR(rFieldList);
rFieldList.SETRANGE(TableName,rTableList.TableName);
rFieldList.SETRANGE(FieldName,rTableList.FieldName);
IF rFieldList.FIND('-') THEN BEGIN
  tblTableRef.OPEN(rFieldList.TableNo,FALSE);
  tblKeyFields := tblTableRef.CURRENTKEY;
  IF STRPOS(tblKeyFields,rTableList.FieldName) > 0 THEN
    tblRenameKey := TRUE;
  CLEAR(tblKeyFields);
  tblPosition := GetFirstRecord(tblTableRef);
  tblCount1 := tblTableRef.COUNT;
  IF tblCount1 > 0 THEN
    FOR tblCount2 := 1 TO tblCount1 DO BEGIN
      IF tblPosition <> 'NO RECORDS' THEN
        tblPosition := ProcessCurrentRecord(tblTableRef,tblPosition,tblRcdCount,rFieldList,rIDList,tblRenameKey);
      IF timProgress < TIME - 3000 THEN BEGIN
        timProgress := TIME;
        Window.UPDATE;
        COMMIT;
      END;
    END;
END;
tblTableRef.CLOSE;

GetFirstRecord(VAR tblTableRef : RecordRef) tblPosition : Text[250]
IF tblTableRef.FIND('-') THEN
  tblPosition := tblTableRef.GETPOSITION
ELSE
  tblPosition := 'NO RECORDS';

ProcessCurrentRecord(VAR tblTableRefTmp : RecordRef;VAR tblPosition : Text[250];VAR tblRcdCount : Integer;VAR rFieldList : Record Field
tblTableRefTmp.OPEN(rFieldList.TableNo,FALSE);
tblTableRefTmp.SETPOSITION(tblPosition);
IF tblTableRefTmp.FIND('=') THEN BEGIN
  tblRcdCount := tblRcdCount + 1;
  tblFieldRef := tblTableRefTmp.FIELD(rFieldList."No.");
  OldID := tblFieldRef.VALUE;
  rIDList.RESET;
  IF rIDList.GET(OldID) THEN
    IF OldID <> rIDList."New User ID" THEN
      TheWritingBit(tblRenameKey,tblTableRefTmp,rFieldList,rIDList);
  CLEAR(tblFieldRef);
  CLEAR(rIDList);
END;
IF tblTableRefTmp.NEXT > 0 THEN
  tblPosition2 := tblTableRefTmp.GETPOSITION
ELSE
  tblPosition2 := 'NO RECORDS';
tblTableRefTmp.CLOSE;

TheWritingBit(VAR tblRenameKey : Boolean;VAR tblTableRef : RecordRef;VAR rFieldList : Record Field;VAR rIDList : Record "User ID Conver
IF NOT tblRenameKey THEN BEGIN
  tblTableRef2 := tblTableRef.DUPLICATE;
  tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
  tblFieldRef2.VALUE := rIDList."New User ID";
  tblTableRef2.MODIFY(FALSE);
END ELSE BEGIN
  tblTableRef2 := tblTableRef.DUPLICATE;
  tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
  tblTableRef2.DELETE(FALSE);
  tblFieldRef2.VALUE := rIDList."New User ID";
  tblTableRef2.INSERT(FALSE);
END;
CLEAR(tblFieldRef2);
tblTableRef2.CLOSE;

SHardy

Hmmm, I have now found a link. It appears that all of the records that have not been updated are at the end of the tables. Therefore, it would appear that the code is not working through all of the records. It is gettting to a point towards the end, and then stopping.

A couple of weird points though:

1) For one of the tables in question, I copied the table across to local DB and all were updated OK.
2) For some tables it misses a few records, for others it can miss a few hundred.

Still very, very confused! :-k

SHardy

OK, it seems that I panic too much. It would appear that the problem doesn't lie with my code, but with Navision's COUNT function.

On further investigation I found a link between the tables regarding the records that weren't updated... all of these records were at the end of the tables. Therefore the update was getting so far through the table, and then stopping.

Again, looking through this further, I ran a SQL query against one of the tables to get a record count, then I ran a COUNT against the table in Navision.... BINGO!!! Different results for some bizarre reason.

Sticking within Navision I then found the following:

1) The following code does not return the correct record count:

rBACSLedg.RESET;
MESSAGE('Records: ' + FORMAT(rBACSLedg.COUNT));

2) The following code does return the correct record count:

rBACSLedg.SETRANGE(rBACSLedg."Entry No.",0,9999999);
MESSAGE('Records: ' + FORMAT(rBACSLedg.COUNT));

So, in actual fact an unfiltered table has less records than a filtered table!

Am I alone in thinking that this is a tiny bit unusual?

Has anyone come across this before?
Do you know what causes this?

This does not appear to be an issue with every table in every database. Other tables within this database were fully updated. Also, my local database updated every table successfully.

Any further help would be greatly appreciated.

Thanks,
Simon

BTW, as this is actually a separate issue, I will post as a new topic, but maybe with a link to here for a bit of background.