Slow grid load and copy to Excel

[UMG Developer]

We are coming from SQL Navigator, and while I like a lot of things about PL/SQL Developer (7.1.5.1398) it seems considerably slower at some things.

For instance to run a query that returns 40484 rows:

SQL Navigator:
Initial query execution: 244 seconds
Load all records into on screen grid: 3 seconds
Export all records to Excel: 12 seconds
Total time: 259 seconds

PL/SQL Developer:
Initial query execution: 198 seconds
Load all records into on screen grid: 52 seconds
Copy all records to Excel: 87 seconds
Total time: 337 seconds

So PL/SQL Developer is about 30% slower overall, even though it is about 20% faster on the initial execution.

Are there default options that I can change to speed it up a little?

Also, how about adding an "Export to Excel" option that lets you pick the file name, and exports all the records like the other exports. (So you don't have to load the entire grid, and then select copy to Excel.)

Thanks!

 

[Marco van der Linden]

Make sure that the option'Tools/Preferences/Window Types/SQL Window/Show dictionary info in result grid' is un-checked. That should safe quite some time.

 

[UMG Developer]

That didn't seem to make much, if any, difference.

PL/SQL Developer takes about 49 seconds to load all the remaining rows after the initial query execution. (a total of 41,338 rows today.)

SQL Navigator takes about 5 seconds.

I ran the tests multiple times with both clients and got consistent results, any idea why PL/SQL Developer is almost 10 times slower downloading the data?

What about an option to export directly to Excel so we don't have to load the records and then copy to Excel? (Saving a step.)

 

[Marco Kalter]

It seems to me that they are equally fast executing and fetching the query. Both are about 250 seconds.

I'm not sure why the Copy to Excel function is that slow though. We'll look into this. I have also added your suggestion for a direct export to Excel to the list of enhancement requests.

 

[UMG Developer]

I think the time for initial execution was being skewed by other processes on the Oracle server, and I wasn't running it multiple times.

In running the query multiple times:

PL/SQL Developer pulls the first 12 rows back in 183, 47, 49, 49, and 53 seconds.
SQL Navigator pulls the first 250 rows back in 158, 49, 48, 50, and 59 seconds.

So if you through the first run from each out, they both normally bring initial results back in about 49 seconds. (Which is what I would expect.)

Then loading the remaining rows (a total of 41,480 today) took PL/SQL Developer about 50 seconds, while SQL Navigator took about 5.

 

[UMG Developer]

I'm wondering if part of the slowness is the little buttons that get added in the grid results for the "Large Data Editor", the date picker tool, etc. Is there a way to turn those off? (They aren't really valuable in a select only query; though I can see where they could be useful when editing data.)

 

[Marco Kalter]

I don't think they are the cause. To obtain some more diagnostic information, can you modify the shortcut and add the debugsql parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" debugsql

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.

 

[ScottMattes]

UMG Developer,
When you say that it took 5 seconds for JDev to load all the rows to the grid, were you then able to scroll to the last row of the result set, or was there some delay? I have seen other tools where they seemed fast, but scrolling from page to page of the result set was so slow.

 

[UMG Developer]

Scott,

It takes SQL Navigator 5 seconds to load all the records into the grid, and you can then instantly scroll to anywhere in the results.

 

[UMG Developer]

Marco,

I sent an email to the support address with the debug log attached.

 

[UMG Developer]

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.

Marco, I sent the debug log about 3 weeks ago, and I haven't heard anything back, did you get the log? Where you able to identify any potential causes?

 

[Marco Kalter]

Sorry, I don't think we have received it. Can you resend it, and make sure that the file is zipped?

 

[UMG Developer]

I just resent it zipped this time, let me know if you don't get it. (The file was only 340k, so I wouldn't think size was a problem.)

 

[Marco Kalter]

I have received it. We'll check it out.

 

[kriszo]

Is there anything with this problem? Sometimes I have to deal with large riports (500k-1M records), and copy to excel takes around 1 hour.