Ever wondered how long your SR's / Workflow(s) took to execute?
Here is a little SQL example to pull this information out of DB.
The current output of below code produces this:
+-------+-------------+
| SR_ID | Time_in_Sec |
+-------+-------------+
| 9307 | 26.7270 |
+-------+-------------+
1 row in set (0.01 sec)
+-------+-------------+
| SR_ID | Time_in_Sec |
+-------+-------------+
| 9308 | 552.6320 |
+-------+-------------+
1 row in set (0.01 sec)
SSH to the UCSD instance and log onto mysql:
mysql -u admin --password=xxxxx db_private_admin -A
Cut and paste the below code into mysql (change the SR numbers for your application 9300/9330):
DELIMITER $$
DROP PROCEDURE IF EXISTS test_MY_LOOP$$
CREATE PROCEDURE test_MY_LOOP()
BEGIN
DECLARE SR_ID int;
SET SR_ID=9300;
WHILE SR_ID <= 9330 DO
select SR_ID,(select ( select MAx(TIMESTAMP) from db_private_admin.SERVICE_REQUEST_LOG WHERE SRID = SR_ID) - (select MIN(TIMESTAMP) from db_private_admin.SERVICE_REQUEST_LOG WHERE SRID = SR_ID)) / 1000 AS Time_in_Sec;
set SR_ID = SR_ID + 1;
END WHILE;
END $$
DELIMITER ;
Run the stored procedure:
Call test_MY_LOOP();
