Very Slow Scheduled Job Verses Manual Run

Toad Data Point & Toad Intelligence Central
1

Need some urgent help with this one.

I have a set of Automation tasks to process data and produce outputs. The Master automation task is made up of several smaller automation jobs. If I run this job manually via the applications it consistently takes ~22 seconds. If run via scheduled task (as I need it run every hour) it takes 2 minutes 2. This is with basic logging. If I compare the 2 log files they are identical EXCEPT for the time that it takes between lines.

Here is a snippet out of each.

If I run it via the command line using
"C:\Program Files\Quest Software\Toad Data Point 5.7\Toad.exe" -batch=true

Same outcome. In the task bar (next to the time) the toad icon appears. IF I click on it it takes a long time to load. Also the Job in task scheduler NEVER ENDS....

Is there anything that I can do to fix this. I need the job to run in 20 seconds. Not 2.20.

One more thing. If I turn on detailed logging the scheduled task then takes 40minutes....

Manual Run
-- 3/07/2022 11:05:34 AM: Automation-Master-PrisonerEmail.log: Script execution completed.
-- 3/07/2022 11:05:34 AM: Automation-Master-PrisonerEmail.log: Running embedded import template
-- 3/07/2022 11:05:35 AM: Automation-Master-PrisonerEmail.log: Beginning Import.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisAccess"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Attempted to import 14 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Successfully imported 14 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisAccess"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisAddExtContact"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Attempted to import 45 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Successfully imported 45 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisAddExtContact"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisBlockExtContact"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Attempted to import 7 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Successfully imported 7 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisBlockExtContact"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_ExtContactBlockPris"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Attempted to import 10 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Successfully imported 10 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_ExtContactBlockPris"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_CVSiteEmailAdd"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Attempted to import 512 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Successfully imported 512 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_CVSiteEmailAdd"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_CVRemoveUser"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Attempted to import 9 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Successfully imported 9 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_CVRemoveUser"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Start: "OSN-Prisoner-Details.csv" into "PRD_Prisoner_Details"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Attempted to import 6603 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Successfully imported 6603 rows.
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Done: "OSN-Prisoner-Details.csv" into "PRD_Prisoner_Details"
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Connection description = Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Connection node found
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Connection to: Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:05:48 AM: Automation-Master-PrisonerEmail.log: Executing script: ALTER TABLE PRD_PrisEml_CVRemoveUser

Scheduled Task Run
-- 3/07/2022 11:02:21 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Script execution completed.
-- 3/07/2022 11:02:22 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Running embedded import template
-- 3/07/2022 11:02:22 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Beginning Import.
-- 3/07/2022 11:02:36 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisAccess"
-- 3/07/2022 11:02:36 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:37 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Attempted to import 14 rows.
-- 3/07/2022 11:02:37 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Successfully imported 14 rows.
-- 3/07/2022 11:02:37 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisAccess"
-- 3/07/2022 11:02:37 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisAddExtContact"
-- 3/07/2022 11:02:37 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:38 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Attempted to import 45 rows.
-- 3/07/2022 11:02:38 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Successfully imported 45 rows.
-- 3/07/2022 11:02:38 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisAddExtContact"
-- 3/07/2022 11:02:38 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisBlockExtContact"
-- 3/07/2022 11:02:38 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:38 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Attempted to import 7 rows.
-- 3/07/2022 11:02:39 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Successfully imported 7 rows.
-- 3/07/2022 11:02:39 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_PrisBlockExtContact"
-- 3/07/2022 11:02:39 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_ExtContactBlockPris"
-- 3/07/2022 11:02:39 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:39 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Attempted to import 10 rows.
-- 3/07/2022 11:02:40 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Successfully imported 10 rows.
-- 3/07/2022 11:02:40 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_ExtContactBlockPris"
-- 3/07/2022 11:02:40 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_CVSiteEmailAdd"
-- 3/07/2022 11:02:40 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:40 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Attempted to import 512 rows.
-- 3/07/2022 11:02:41 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Successfully imported 512 rows.
-- 3/07/2022 11:02:41 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_CVSiteEmailAdd"
-- 3/07/2022 11:02:41 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Start: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_CVRemoveUser"
-- 3/07/2022 11:02:41 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:41 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Attempted to import 9 rows.
-- 3/07/2022 11:02:41 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Successfully imported 9 rows.
-- 3/07/2022 11:02:42 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Done: "Slave-PRD-Prisoner-Email-DataWarehouse.xlsx" into "PRD_PrisEml_CVRemoveUser"
-- 3/07/2022 11:02:42 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Start: "OSN-Prisoner-Details.csv" into "PRD_Prisoner_Details"
-- 3/07/2022 11:02:42 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Executing import using connection Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:42 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Attempted to import 6603 rows.
-- 3/07/2022 11:02:42 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Successfully imported 6603 rows.
-- 3/07/2022 11:02:43 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Done: "OSN-Prisoner-Details.csv" into "PRD_Prisoner_Details"
-- 3/07/2022 11:02:43 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Connection description = Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:43 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Connection node found
-- 3/07/2022 11:02:43 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Connection to: Slave-PRD-Prisoner-Email-DataWarehouse
-- 3/07/2022 11:02:43 AM: Automation-Master-PrisonerEmail_2022-07-03_11-01-58-619.log: Executing script: ALTER TABLE PRD_PrisEml_CVRemoveUser
ALTER COLUMN Eml-RemovePris-CV-RemoveDate datetime

2

Just guessing, but it seems that the launch latency for TDP when initiated via command line is possibly the culprit here. How much RAM do you have on your workstation?

Also, I've seen issues when kicking off multiple automation jobs, and having them run long or "hanging", mostly due to file system locking, or table locking on the database. Don't suppose any of that is happening?

3

Heaps of ram and CPU. It’s on a server. I have logged a ticket and it appears there is a .2 second delay between every command within the log. It looks like there is a delay built into the toad batch command.

No logging reduces the time to 2 minutes. Detailed logging switched on blows it out to 40 minutes.

I really think it’s a application defect.

Kind Regards

Duncan Blake
Offender Technology Manager

PRIVATE & CONFIDENTIAL The content of this e-mail and any attachments may be private and confidential, intended only for use of the individual or entity named. If you are not the intended recipient of this message you must not read, forward, print, copy, disclose, use or store in any way the information this e-mail or any attachment contains. If you are not the intended recipient, please notify the sender immediately and delete or destroy all copies of this e-mail and any attachments. Our organisation respects the privacy of individuals. For a copy of our privacy policy please go to our website or contact us.

4

OK, good to know... we will see what the Quest team discovers.

5

Just loaded up Version 6 that was released yesterday. Same result :frowning:

6

OK, I know you logged a Support ticket for this, but just curious, is this issue also happening on other Windows workstations?

Also, when the Toad icon appears in the task bar after command line launch, you don't have to click on it... Toad is running in the background.

7

The support guys get the same result with their testing too. Yes. Testing on my local machine had the same result.

I strongly think that the programmers have put a .2 of a second wait between every command that is run when in batch mode (the scheduled task).

The toad icon - it's interesting to see when you click on it. It's semi frozen and the application that is lunched doesn't really function well for those ~2 minutes. Plus it never "finishes" so the scheduled task stays in "running" mode. Even though my logging tells me that the job has finished. Only way to run the next one is kill the previous session.

PRIVATE & CONFIDENTIAL
The content of this e-mail and any attachments may be private and confidential, intended only for use of the individual or entity named. If you are not the intended recipient of this message you must not read, forward, print, copy, disclose, use or store in any way the information this e-mail or any attachment contains.
If you are not the intended recipient, please notify the sender immediately and delete or destroy all copies of this e-mail and any attachments.
Our organisation respects the privacy of individuals. For a copy of our privacy policy please go to our website or contact us.