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 [Duh!] ) 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;
You need to put the recordref code into a function and declare the recordref variable there locally. 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 // Call a function in here instead PerformRecRefAction(rFieldList); END; UNTIL rTableList.NEXT = 0; Window.CLOSE; and the code in the new function 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; CLEAR(tblTableRef); Make sure that you declare ALL variables (especially fieldref,recref,keyref) in the function LOCALLY !!! There is a known bug in ALL Navision versions where the recref does not clear its memory usage. A side effect is that the code will get slower and slower and s l o w e r. You can overcome this issue !!
Blimey! It has ben a struggle to get back onto this forum without errors. But today (for now anyway) I can read and reply. Hooray! Thanks for the advice. I have (under similar advice) moved the whole of the code within the first loop to a separate function, with all variables declared locally. As you suggested, this seems to have cleared the problem. I have not yet been able to test it on a full size copy of our live system, but it certainly has solved it when running locally on a smaller database. I will update as “solved” once I have fully tested. Once again, many thanks.
I have a similar code running on a 30GB DB with 100000 item records I’m looping through and some other tables. The scanned records all together might be around 6 Million each run (several runs a day). So I promise this problem is solved [:D]
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;
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! [Sigh…]
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.
Sorry - saw the last part of this post in another post - have replied to that one regarding COUNT that should explain it.
You should try to avoid looping through records using for/next anyway. Try to modify your code to use REPEAT/UNTIL instead.