Errol Farro
asked on
Run programs sequentially from coldfusion
I need to run some programs sequentially from one CFM.
DailyTask.CFM must call pgmA.cfm which does some database updating.
As soon as pgmA.cfm is done, pgmB.cfm must be called. PgmB.cfm inserts records into a table.
Once PgmB.cfm is finished, pgmC.cfm must be called. PgmC.cfm also updates a table.
pgmA.cfm should not have <cflocation url="pgmB.cfm" > and pgmB.cfm should not have <cflocation url="pgmC.cfm" >
So from with dailytaks.cfm, pgmA.cfm, pgmB.cfm, and pgmc.cfm must be called sequentially.
Is this possible ?
DailyTask.CFM must call pgmA.cfm which does some database updating.
As soon as pgmA.cfm is done, pgmB.cfm must be called. PgmB.cfm inserts records into a table.
Once PgmB.cfm is finished, pgmC.cfm must be called. PgmC.cfm also updates a table.
pgmA.cfm should not have <cflocation url="pgmB.cfm" > and pgmB.cfm should not have <cflocation url="pgmC.cfm" >
So from with dailytaks.cfm, pgmA.cfm, pgmB.cfm, and pgmc.cfm must be called sequentially.
Is this possible ?
ASKER
Problem is that there are some occasion that my code would look as
<cfinclude template="pgmA.cfm">
<cfinclude template="pgmA.cfm">
<cfinclude template="pgmC.cfm">
<cfinclude template="pgmB.cfm">
<cfinclude template="pgmB.cfm">
So when I have two rows that are equal, I get the wrong disk operations. Seems that proram conflicts
<cfinclude template="pgmA.cfm">
<cfinclude template="pgmA.cfm">
<cfinclude template="pgmC.cfm">
<cfinclude template="pgmB.cfm">
<cfinclude template="pgmB.cfm">
So when I have two rows that are equal, I get the wrong disk operations. Seems that proram conflicts
>> Problem is that there are some occasion that my code would look as
>> So when I have two rows that are equal,
Not sure I follow. Could you elaborate?
Additional questions:
1. Is this an automated task?
2. How long do each of the processes take, approximately?
3. Do they processes run more than once per day?
4. Which dbms (SQL Server, MySQL, ...)?
5. Have you considered scheduled tasks?
>> So when I have two rows that are equal,
Not sure I follow. Could you elaborate?
Additional questions:
1. Is this an automated task?
2. How long do each of the processes take, approximately?
3. Do they processes run more than once per day?
4. Which dbms (SQL Server, MySQL, ...)?
5. Have you considered scheduled tasks?
ASKER
I tried to embed the problem in the above example but seems to have caused more confusion.
This is the actual challenge.
PgmA reads a master table and than validates against several other tables to create an output file.
The master file has grown to approximately 50,000 records and because of inefficiencies in the CFM it started throwing the message "The request has exceeded the allowable time limit Tag:CFQUERY"
Since the developer of PGMA is not available anymore, we were thinking of having a solution as follows
pgmA reads 2000 records and generate the 1st output file, close all connection and have pgm A read the following 2000 records, create output file, close all connections etc...
That was the solution we are considering, unless there is a better one.
These are the answers to your question
1. Is this an automated task? YES
2. How long do each of the processes take, approximately? 25 minutes
3. Do they processes run more than once per day? Once per day - it is a scheduled job
4. Which dbms (SQL Server, MySQL, ...)? SQL Server
5. Have you considered scheduled tasks? Looking in that direction
This is the actual challenge.
PgmA reads a master table and than validates against several other tables to create an output file.
The master file has grown to approximately 50,000 records and because of inefficiencies in the CFM it started throwing the message "The request has exceeded the allowable time limit Tag:CFQUERY"
Since the developer of PGMA is not available anymore, we were thinking of having a solution as follows
pgmA reads 2000 records and generate the 1st output file, close all connection and have pgm A read the following 2000 records, create output file, close all connections etc...
That was the solution we are considering, unless there is a better one.
These are the answers to your question
1. Is this an automated task? YES
2. How long do each of the processes take, approximately? 25 minutes
3. Do they processes run more than once per day? Once per day - it is a scheduled job
4. Which dbms (SQL Server, MySQL, ...)? SQL Server
5. Have you considered scheduled tasks? Looking in that direction
1. Where do "B" and "C" come in?
A - reads a master table, runs a bunch of queries and produces an output file
B - Does... ?
C - Does... ?
2. Also, how complex is the "A" process? Reason for asking is it sounds like the cfm scripts are querying within loops (which is slow, especially the more rows involved) and that's why it times out. Usually, I'd suggest getting rid of the loops, and using a work table instead. But I don't know how much work is involved or if refactoring is an option.
A - reads a master table, runs a bunch of queries and produces an output file
B - Does... ?
C - Does... ?
2. Also, how complex is the "A" process? Reason for asking is it sounds like the cfm scripts are querying within loops (which is slow, especially the more rows involved) and that's why it times out. Usually, I'd suggest getting rid of the loops, and using a work table instead. But I don't know how much work is involved or if refactoring is an option.
3. Also, why does "A" need to generate an output file. Is the process transferring data between different sources or database servers?
Edit: Again, thinking about whether or not it's possible to simplify the process. i.e. Is there a specific reason the output files are necessary or is it possible to streamline things.
Edit: Again, thinking about whether or not it's possible to simplify the process. i.e. Is there a specific reason the output files are necessary or is it possible to streamline things.
ASKER
Ignore "B" and "C". Those were examples. Please refer to "THE ACTUAL CHALLENGE" writing.
PGMA is very complex and refactoring is not an option for now. Hopefully you will have a solution.
Please let me know if you need further exaplanation on "THE ACTUAL CHALLENGE"
PGMA is very complex and refactoring is not an option for now. Hopefully you will have a solution.
Please let me know if you need further exaplanation on "THE ACTUAL CHALLENGE"
ASKER
What we would like to do, unless there is a better solution, is to read the master file in small junks of 2000 records every time and subsequntly close all the connections to prevent "The request has exceeded the allowable time limit Tag:CFQUERY"
Okay, then yes setting up the script to process "batches" of 2000 records at a time is feasible. The key is to create a scheduled task that runs once a day, then calls itself again - as long as there's work to process.
1. Set up a scheduled task that runs your cfm script once per day.
2. Inside the script, check how many records need to be processed.
If records > 0
a. Process the records
b. When finished, check if there are still records to be processed
c. If yes, have call the task again. Otherwise, do nothing (let the scheduled task end)
If no records
Do nothing (let the scheduled task end)
Just be sure to wrap the code in an exclusive CFLOCK (to ensure it only runs one batch at a time).
Let me see if I can throw together a quick example.
1. Set up a scheduled task that runs your cfm script once per day.
2. Inside the script, check how many records need to be processed.
If records > 0
a. Process the records
b. When finished, check if there are still records to be processed
c. If yes, have call the task again. Otherwise, do nothing (let the scheduled task end)
If no records
Do nothing (let the scheduled task end)
Just be sure to wrap the code in an exclusive CFLOCK (to ensure it only runs one batch at a time).
Let me see if I can throw together a quick example.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> Ignore "B" and "C". Those were examples. Please refer to "THE ACTUAL CHALLENGE" writing.
Yeah, sorry the "B" and "C" threw me off ;-) It took a bit for me to realize you were talking about a single script - processing data in batches.
(Though if you actually did have 3 separate scripts to run - one after the other - you could use a "Chained Task", supported in CF11+)
Yeah, sorry the "B" and "C" threw me off ;-) It took a bit for me to realize you were talking about a single script - processing data in batches.
(Though if you actually did have 3 separate scripts to run - one after the other - you could use a "Chained Task", supported in CF11+)
ASKER
After I got your solution working on my PC which is CF11 and loading it on the live server (CF 2018) I get the below message. Is there anyway to circumvent this that you know about ?
Advance Scheduling support is not available in this edition of ColdFusion server.
Advance Scheduling support is not available in this edition of ColdFusion server.
Hmm... it's hard to imagine what's NOT being allowed, since it's just a simple scheduled task and a call to <cfschedule action=run>, nothing fancy. Where does the error occur, when scheduling it or actually running task?
ASKER
When running the task
Advance Scheduling support is not available in this edition of ColdFusion server.
The error occurred in C:/ColdFusion2018/cfusion/ wwwroot/TF /Mortgage/ x1.cfm: line 99
99 : <cfschedule action="Update" task="TestSchedule" operation="HTTPRequest" mode="application" url="http://pgm1.cfm">
Advance Scheduling support is not available in this edition of ColdFusion server.
The error occurred in C:/ColdFusion2018/cfusion/
99 : <cfschedule action="Update" task="TestSchedule" operation="HTTPRequest" mode="application" url="http://pgm1.cfm">
Update:
Oh ... that code isn't from my example. What does the update do in the overall task flow? Apparently "mode" may not be supported under CF Standard. Does it work without the "mode"?
<cfschedule action="Update" task="TestSchedule" operation="HTTPRequest" mode="application" url="http://pgm1.cfm">
Oh ... that code isn't from my example. What does the update do in the overall task flow? Apparently "mode" may not be supported under CF Standard. Does it work without the "mode"?
ASKER
It does work without mode - thanks a million _agx
You're welcome. I guess "application" level tasks (vs server level tasks) are only supported in Enterprise.
<cfinclude template="pgmA.cfm">
<cfinclude template="pgmB.cfm">
<cfinclude template="pgmC.cfm">