Advice on a VERY long process

[rocketman]

I am upgrading my main system for a number of reasons I. I am creating Monthly Period ends and monthly statements for all members. All fine for all my customers except my own club which has 9 years worth of data to upgrade.

I created a log file and put some code in to write an entry on completion of certain stages. The problem is it doesn't seem to write the entries (or I can't see them) until the very end of the process. So I'm wondering if putting an "IN BATCHES OF 1 somewhere might force a rewrite to disk so I could monitor progress. Maybe in process one ?

The whole process takes about 3 hour per year of data. and the problem is, once I get timed out, even if I log back in again, I can see any output from the processes in the log viewer.

I say process - it's several processes called from the main process.

A previous set of process has already created teh period ends and linked all members and their transactions to the period end record

Process 1 finds all the period ends I want to create members statements for sorted in date order ascending
Process 2 (Called from 1) finds any old statements for this period and deletes them
Process 3 (Called from 2) does FIND Members WHERE (Members IN Period_Ends.Members_This_Period) and calls process 4.
Process 4 (Called from 3 )lFinds all that members transactions Passes to process 5 one by one
Process 5 inserts the transaction into the statement and calculates the running totals for charges, credits, payments and opening and closing balances (this could probable be done once at the end of each member's processing but hey - it works) - then everything goes back to process 3

There are log creation entries at the end of process 3 which should alert me that one complete period end has completed before it goes back to 1 and starts the next period end ( CREATE Admin_Log WITH Admin_Log.Log_Date=CURRENT_TIMESTAMP,Admin_Log.Job='Statement Regeneration',Admin_Log.Log_Entry='Recreated statements for period '+ Period_Ends.Start_Date )... BUT IT DOESN'T

[rocketman]

Sorted. In Batches of 1 worked a treat.

Final question(I forgot to ask.) If I run the process overnight, Will the FULL backup (Mysqldump) which kicks in at 3:00am clobber the running processes or will they just carry on?

[Jaymer]

you said you're only trying to "monitor progress", so I think the IN BATCHES OF 1 is slowing things down, first of all.
If it ran before without it, then I think its def. slower now.

But, you could stick a COMMIT TRANSACTION in there somewhere - that might help the logging.
Also, why not do an export to a physical file on the file system - then its not reliant on SQL Transactions.
Or just make a EXECUTE_PROGRAM do a DOS "echo" of a string to a physical file, showing your tracking info.

examples:
EXECUTE PROGRAM 'c:/myFiles/go.bat'
- or -
tempCurl.theCommand = REPLACE_PATTERN( 'curl ~http://www.site.com/test.php~' ,'~', SystemSettings.DQ )
NOTE: Above results in this string in theCommand: curl "http://www.site.com/test.php"
May take some fiddling, because you would want something like: echo "Customer 123 Finished" >> c:\mylog.txt
EXECUTE PROGRAM tempCurl.theCommand

[rocketman]

I get what you are saying, but in this instance, I'm only doing a year at a time and checking the year end balances as I go, so the first "Find" is only fetching 12 records and I've put the In Batches of on that line.

The rest of it - which is the time consuming but - just goes ahead and gets written back to the DB after each period (month) The biggest time saver was moving the balance calculations out of the transaction processing bit out one level so it only does the calc for the whole statement at the end instead of TX by TX (there could be hundreds on a dedicated flying members account. That's more than halved the processing time for a period.

Happy bunny .... cracking on!!