Toad World® Forums

Toad Automation Export Speed - Very Slow?


#1

I don’t suppose you get paid by the hour?

Can you take this over to the TDA community/message board? The team for that product lives over there vs here…


From: toad@yahoogroups.com [toad@yahoogroups.com] On Behalf Of Ulmet, Darlene [Darlene.Ulmet@baylorhealth.edu]

Sent: Thursday, April 16, 2009 5:47 PM

To: toad@yahoogroups.com

Subject: [toad] RE: Output Issue

Hi all,

First the good news…I created the 8 TAS projects to run the 11 facility scripts and I also created the 88 Export Templates directing the output of the each of the 88 queries to 8 Excel workbooks with 11 tabs each! Cool beans! Yeah, TOAD for Data Analyst!

Now for the bad news…it used to take me a little over an hour to run these manually and then “right click…save as” paste into the blasted workbooks. The first project just completed and it took a whopping 2 hours to run approximately an eighth of the job that I could perform manually in a little over an hour! The export counts are really small so I am not sure what is taking so long. Also, I ran the exact same scripts during the manual kick off process. Ideas anyone???

Automation Logging 4/16/2009 2:22:49 PM Build started

Automation Logging 4/16/2009 2:23:03 PM Warning: No Exception Handler. To send an Email when there is an error, change the value of Email on Error to true and enter email values.

Automation Logging 4/16/2009 2:23:03 PM Build Completed

Automation Logging 4/16/2009 2:23:05 PM Connecting to XXXXXXXX

Automation Logging 4/16/2009 2:23:05 PM Connection successful.

Automation Logging 4/16/2009 2:25:02 PM Script execution complete

Automation Logging 4/16/2009 2:25:10 PM Script execution complete

Automation Logging 4/16/2009 2:29:55 PM Script execution complete

Automation Logging 4/16/2009 2:30:01 PM Script execution complete

Automation Logging 4/16/2009 2:32:01 PM Script execution complete

Automation Logging 4/16/2009 2:34:12 PM Script execution complete

Automation Logging 4/16/2009 2:36:00 PM Script execution complete

Automation Logging 4/16/2009 2:38:10 PM Script execution complete

Automation Logging 4/16/2009 2:38:43 PM Script execution complete

Automation Logging 4/16/2009 2:38:50 PM Script execution complete

Automation Logging 4/16/2009 2:40:04 PM Script execution complete

Automation Logging 4/16/2009 2:40:04 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\BAS Export Template File.txp

Automation Logging 4/16/2009 2:40:35 PM Attempted export 55 rows.

Automation Logging 4/16/2009 2:40:35 PM Successfully exported 55 rows.

Automation Logging 4/16/2009 2:40:35 PM Export Finished, click to view file.

Automation Logging 4/16/2009 2:40:35 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\BIR Export Template File.txp

Automation Logging 4/16/2009 2:52:45 PM Attempted export 9 rows.

Automation Logging 4/16/2009 2:52:45 PM Successfully exported 9 rows.

Automation Logging 4/16/2009 2:52:45 PM Export Finished, click to view file.

Automation Logging 4/16/2009 2:52:45 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\BMD Export Template File.txp

Automation Logging 4/16/2009 3:07:15 PM Attempted export 73 rows.

Automation Logging 4/16/2009 3:07:15 PM Successfully exported 73 rows.

Automation Logging 4/16/2009 3:07:15 PM Export Finished, click to view file.

Automation Logging 4/16/2009 3:07:15 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\BSH Export Template File.txp

Automation Logging 4/16/2009 3:20:13 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\GAR Export Template File.txp

Automation Logging 4/16/2009 3:37:31 PM Attempted export 22 rows.

Automation Logging 4/16/2009 3:37:31 PM Successfully exported 22 rows.

Automation Logging 4/16/2009 3:37:31 PM Export Finished, click to view file.

Automation Logging 4/16/2009 3:37:31 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\GRP Export Template File.txp

Automation Logging 4/16/2009 3:53:43 PM Attempted export 57 rows.

Automation Logging 4/16/2009 3:53:43 PM Successfully exported 57 rows.

Automation Logging 4/16/2009 3:53:43 PM Export Finished, click to view file.

Automation Logging 4/16/2009 3:53:43 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\IRV Export Template File.txp

Automation Logging 4/16/2009 3:56:42 PM Attempted export 9 rows.

Automation Logging 4/16/2009 3:56:42 PM Successfully exported 9 rows.

Automation Logging 4/16/2009 3:56:42 PM Export Finished, click to view file.

Automation Logging 4/16/2009 3:56:42 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\IRV Export Template File.txp

Automation Logging 4/16/2009 3:57:32 PM Attempted export 9 rows.

Automation Logging 4/16/2009 3:57:32 PM Successfully exported 9 rows.

Automation Logging 4/16/2009 3:57:32 PM Export Finished, click to view file.

Automation Logging 4/16/2009 3:57:32 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\PLA Export Template File.txp

Automation Logging 4/16/2009 4:14:34 PM Attempted export 10 rows.

Automation Logging 4/16/2009 4:14:34 PM Successfully exported 10 rows.

Automation Logging 4/16/2009 4:14:34 PM Export Finished, click to view file.

Automation Logging 4/16/2009 4:14:34 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\PLH Export Template File.txp

Automation Logging 4/16/2009 4:28:17 PM Attempted export 4 rows.

Automation Logging 4/16/2009 4:28:17 PM Successfully exported 4 rows.

Automation Logging 4/16/2009 4:28:17 PM Export Finished, click to view file.

Automation Logging 4/16/2009 4:28:17 PM Running export template W:\Marcy - Ins Payor Files\200812 Output Test\WAX Export Template File.txp

Automation Logging 4/16/2009 4:36:31 PM Attempted export 105 rows.

Automation Logging 4/16/2009 4:36:31 PM Successfully exported 105 rows.

Automation Logging 4/16/2009 4:36:31 PM Export Finished, click to view file.

Automation Logging 4/16/2009 4:36:35 PM Done

Darlene R. Ulmet

Financial Analyst II

Decision Support Services

Baylor Health Care System

Phone: (214) 820-1936

Fax: (214)820-8284

Email: Darlene.Ulmet@BaylorHealth.edumailto:Darlene.Ulmet@BaylorHealth.edu


Sent: Friday, January 09, 2009 11:24 AM

To: toad@yahoogroups.com

Subject: [toad] RE: Output Issue

You can do that with Toad for Data Analysts (which comes with your license of Toad for Oracle)! Split your 88 queries into 8 scripts with 11 SQL queries, separated by a semi-colon, and save them. Then use the Automation tool to create an automation script that will execute each of the 8 scripts, saving the results of each script to 8 XLS files. (You can use the “Select to File” operation to accomplish this.) The automation tool will execute each script and place the results of each individual SQL to its own worksheet. You’ll have your 8 files with 11 worksheets each! Now you can schedule this with the Windows Scheduler to happen as often as you want without having to do a thing.

Let me know if you have questions on how to do it and I’ll send over some screenshots.

Daniel Norwood

Quest Software, Inc.

Product Manager, Database Development Tools

858-605-1369

Sent: Friday, January 09, 2009 9:07 AM

To: toad@yahoogroups.com

Subject: [toad] RE: Output Issue

It’s possible to export 88 queries into 88 excel files, but there is not a way to combine them automatically at this point. It was discussed a few months ago and is on the to do list.

Sent: Friday, January 09, 2009 11:06 AM

To: toad@yahoogroups.com

Subject: [toad] RE: Output Issue

app designer? mark - is that doable?


Sent: Friday, January 09, 2009 10:47 AM

To: toad@yahoogroups.com

Subject: [toad] RE: Output Issue

Hmmm, I’m not sure there’s a way to automate Excel exports to create new workbooks in an existing Excel file, so don’t feel bad at all about asking this question!

Sent: Friday, January 09, 2009 11:28 AM

To: toad@yahoogroups.com

Subject: [toad] Output Issue

I have 88 queries to run that I want to write into 8 Excel workbooks of 11 tabs each. I plan on using the TOAD Script Manager to schedule the monthly run. I can use the save as on the Grid to write a single query into a single tab of the workbook but when I go to the next query it changes the previous one to the current tab name also. I am very new at this and am wondering if there is an easier way to accomplish this task. Any suggestions? Please be gentle – I have read the post for quite some time now and I know this is a very beginner level question to ask but we all have to start somewhere. Thanks!

From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Daniel NorwoodFrom: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John DorlonFrom: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert ScalzoFrom: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Jeff SmithFrom: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Ulmet, Darlene


#2

This is hard to determine. I see that the number of rows retrieved are few but that does not tell me now long is takes to retrieve the initial row.

Your template scripts show the following elapsed times.
1 = 31 sec
2 = 12 mins
3 = 15 mins
4 = 17 mins
5 = 16 mins
6 = 3 mins
7 = 1 min
8 = 9 mins
9 = 14 mins
10 = 8 mins

I do know that each automation action is treated as a separate module. This means that each export template is opening a new connection. Depending on your connect time, this may add some time.

Can you do a little timestamp checking?

  1. Execute one of the longer scripts in TDA (not toad for Oracle) and get just the execution time of the retrieval of all the rows.

  2. Right click on the result set and get the time it takes to export the data using the export wizard.

  3. Run the automation script again, but turn on verbose logging. To do this, select the Script Settings node and choose the verbose from the Logging dropdown. Send me the script and above details in private email. dpeabody@quest.com
    VerboseLogging.bmp