We have a large number of objects and whenever I try to use the Compare function in Schema Browser, it is very slow to load the list of objects...even when I right-click and select Compare on a specific package. Is there anything that can be done to speed this up?
Wondering if defining a filter on the Schema Browser (by object type, of course) would help here?
e.g. so that Toad doesn't have to list out so many objects in the Schema Browser list.
I did add some filtering in Schema Browser which did help its performance. But doesn't seem to impact the Compare form. I can see it counting on the bottom left as it loads and it gets over 80,000 before finishing. Maybe there is somewhere else I need to apply a filter?
Hi David,
Sorry for the delay in getting back to you on this. Yes, there is a way to speed this up in the "Single Object Compare" window.
Go to the Options tab and see if you have this
If you have that option, uncheck it. It was added in version 16.3 for this very reason.
If the problem is something else, please post a screen shot.
Thanks
-John
Sweet news, John! I will upgrade...am currently on 16.2 and do not have the option you display.
Hi David,
Great. If anything is still slow in there, let me know.
That option will prevent the query to load object types in your schema, but when you click the dropdown for object names, that query will still run. So there may still be more room for improvement.
-John
Yeah, the dropdowns seem to be the bottleneck. Very slow to populate and allow me to select from. We do have thousands of items in the list for "Package Spec and Body". If I could apply a filter to that list, it would help quite a bit.
Especially since I launched the compare by right-clicking on a single package name...does it really need to load every single package in the list again?
Especially since I launched the compare by right-clicking on a single package name...does it really need to load every single package in the list again?
Yeah, I can stop the loading of the list in that case and just add the one object that you selected. And if you right-click TWO packages in the schema browser to compare them to each other, I could easily just put those in the dropdowns w/o having to run any queries at all.
But...if you only rt-click one package in the schema browser, then you select a different schema (or database, or both) from the right-hand side, I'll need to come up with a list for your to pick from. Or I suppose I could just give an option to let you type it in yourself.
I'm scratching my head a bit on how to make a filter in there that would look nice.
1 Like
This thread fell off my radar screen until today. I am still faced with achingly slow performance for these dropdowns to load. I was hoping they would be faster in 17 Patch 1, but I'm thinking you never got enough feedback to make a change. (Overall though SB seems much faster so thanks!!)
The most common related operation I perform is to start with a package I want to promote to test from dev. In Dev, I right-click on the package name in Sch Browser (SB) and choose compare. This screen pops up and takes a good 10-15 seconds to fill in the Object Name dropdown:
I then click the Connection dropdown on the right side and choose our test instance. Again, it takes at least 15 seconds or more to load the three subsequent dropdowns (Schema, Object Type, Object Name) to match what I have selected on the left side.
Any improvements here would be fantastic! Or, maybe there is a better way to compare a package between two separate instances?
I did make a change here for 17.
Go to options tab and make sure "Limit Object Type dropdown to available objects" is unchecked. It is faster with that box unchecked.
If it is still too slow, turn on spool SQL so you can get timings of the background queries and post them here. Maybe there is something else we can do.
I guess I could just make an option where Toad doesn't give you any object names and you just have to type them in.
I DID have that box checked - sorry - it is now Unchecked. It is faster and bearable - about 10 seconds for each side.
Thanks!
1 Like
Attaching spool output. Looks like it shows 3-4 seconds, but I'm seeing a good 10 seconds before the cursor changes from a spinner and the UI gives me back control.
It will also be faster if you are logged in as the schema names that you are comparing.
So, in your screenshot...if you log in as APPS, then we can use USER_ views instead of ALL_ or DBA_. USER_ is generally faster than the other two.
1 Like
but I'm seeing a good 10 seconds before the cursor changes from a spinner and the UI gives me back control.
How many packages are in that schema?
Edit: Also, I don't see your spool SQL.
sb compare.txt (4.6 KB)
Being that we are an Oracle apps client -- 41,461 packages.
You're going to tell me I should be happy with 10 seconds, right?
I'll have to look at the code to be sure, but I think I see a query in there that I can get rid of with the option checked.
But yeah - 40,000 rows, 10 seconds, not terrible.
I think there is some room for improvement though.
I know you aren't a beta user, but next beta I'll have most of that 10 seconds eliminated too, when you select a target connection which has the same object.
That's great! I look forward to a future release of it.