I am wanting to run a stored prcedure with no inputs / outputs on a 1 minute period starting at a specific number of seconds of each minute. The stored procedure is doing some intense house keeping tasks, and I actually have 5 different ones, so I start them at different numbers of seconds to equalize the load on the CPU.
I created an expression action item with this:
[color=#BF4040]IF(ExtractSecond(CurrentDateTime())>10,IF(ExtractSecond(CurrentDateTime())<15,1,0),0)[/color]
and set the Result Datatype to Boolean (also tried int).
I am using a Stored Procedure group with an update rate of 1 sec, and the trigger settings are set to look at the action item and “only execute group once when trigger is active”.
However, when I start the group, the value field always says “Bad-Unknown” and the Status tab just shows 1/1/0001… as the last attempted execution and the last successful execution.
What am I doing wrong?
Thanks!
Jim
It appears that everything your doing is correct (in fact, I just mocked up a situation like you described, using your expression, and everything was fine).
The fact that you’re never getting an attempted execution is a bit strange… out of curiosity, if you go to Connection->System Status in the frontend, and then “Statistics”, what do you have for “Avg. Group execution cost”, “Avg Exec Delay” and “Queue Length”?
Regards,
OK - looks like I have a bigger problem… all those were zero, so I started looking at all my other groups and everything has the same attempted and successful times. I tried stopping all goups and starting them, but it is still the same, even on groups that execute every second or two. I even stopped them all and tried just starting a couple groups.
Reboot the server?
Yeah, I would definitely reboot.
Then I would watch those parameters- the execution queue should almost always be 0, and the average execution cost should be <100ms.
Anything higher could indicate that groups are taking a long time to run and are backing up. A common cause of this is an inefficiency in the database, like update rows using a column in the where clause that isn’t properly indexed or something. If things still seem strange after a reboot, perhaps it would be helpful if you could email a project back to support AT inductiveautomation DOT com, and we can take a look at how things are set up.
Also, what version of FactorySQL are you using?
Regards,
I think I am 2 updates behind - 4.2.9
Avg. Group execution cost: 56 (and going down due to a couple long executes I did)
Avg. Exec. Delay: 9.08ms
Avg. Group Efficiency: 0%
Exec. Queue Length: 0
But, right now the machines aren’t running, so only periodic tasks are running. My tasks look ok, too.
Thanks!
Yeah, those numbers are good. If you’re using a lot of stored procedure groups or SQL action items, I would take a look at what they’re doing and how long they’re taking to run. If the stored procedures are taking a long time to execute, it’s going to back up the groups and cause delays.
I mention this because you say that the machines currently aren’t running. Ideally you could start them up gradually and see how that affects the groups’ performance (each group will show it’s execution time on the status tab as well).
If the stored procedures are maintenance tasks that take a long time and can’t be avoided or optimized, it might be a better idea to try to run them from some sort of task scheduler in the db, or windows, etc.
Regards,