Hi,

we are currently evaluating other PL/SQL development tools than the one we are currently using. So far we are quite impressed by the functionality of the PL/SQL Developer, but there is one important issue:

Using the 'Find database objects - Full text search' is showinq a very bad performance. E.g. searching for a developer name in all the packages of a schema (approx. 2500 packages) takes about 15 minutes. Doing the same with Quest SQL Navigator takes about 60 seconds. The same applies if the query is done manually on sys.user_sources.

Are there any hints for improving the performance in this area? I've already tried single/double/multi session settings in the preferences, but I can not see any difference.

Searching in the database is an important item for us.

Please let me know if there is anything we/you can do regarding this performance issue.

Thanks in advance
Michael

The search function in PL/SQL Developer may be a little bit more generic than in SQL Navigator. Perhaps SQL Navigator also performs a search by querying sys.user_source.

To speed this up, you can limit the search by specifying object criteria, such as the object type, owner, name, and so on. You could also create a little report to perform an unrestricted search.

Thanks for your reply:

If you compare the search masks of PL/SQL Devloper and SQL Navigator, you have more DB object types for selection/restriction. The rest looks similiar.

Anyway, if I restrict the search criterias on package specification/body and on one user, the search takes about 15 minutes compared to less than 1 minute with the SQL Navigator.

If you need to do impact analysis, a very common task of application developers, this will increase your development time dramatically.

Is there no way to speed this up?

Marco,

The search works, but it is really slow. Do you mind publishing the find, and see what the community can suggest?

Jason

Further analysis has shown:

...
The following statements are executed many times against the database, even if you are not specifying an object name:

select text from sys.all_source
where owner = :owner and name = :name and type = :type
order by line

select text from sys.all_source where owner = :owner and name =
:name and type = :type order by line
...

This might not be a very efficient way to search in the data dictionary and explains for me the very poor performance of this part of your development tool.

Apart from that, you have created a quite impressive PL/SQL development tool. Further test are outstanding, and if there is significant progress in the database source code search issue, you might convince us...

Regards,
Michael

I agree - it would be nice if the search tool was faster. Anything to speed it up would be good news. If I may take a bit of your time, here are some things to consider though.

It looks to me from the output like SQL Navigator just does a simple select statement against the DBA_USER view or a similar view. Anyone can run a similar query in the SQL Window or a Report Window really easy. I would expect the performance to be the same.

Since I can just open up a saved query using one view and run it, SQL Navigator's speed advantage here is gone, because it's basically doing the same thing.

PL/SQL Developer's search tool has some advantages over SQL Navigator that is harder to overlook.

For example, it's been noted here that PL/SQL Developer has more search options.

Another thing I like about PL/SQL Developer's search tool is that I can double click on the results and the object opens up where the text is first found. I use that quite a bit. SQL Navigator doesn't do that as far as I know.

As Marco said, I can speed it up a lot by including the object name. The vast majority of my searches are just for custom code. For example all of our custom code starts with a "Z". I just put "Z%" in the name and the speed is acceptable.

Mike

Mike,

I agree on the benefits of PL/SQL Developer over SQL Navigator, especially the possibility to see and edit found search results already during run of the search. The feature to double click on the results of the search and jump to the occurrence in the database object, however is also covered by the SQL Navigator.
Restricting the search on DB object names depends on the purpose of the development/analysis task.
I don't know coding details of the search functionality of PL/SQL Developer, but I think, that it could be worth the effort to improve the performance without loosing functionality.
It is sometimes difficult to judge implementations without knowing details and purpose...

Michael,

You are right. SQL Navigator can show the object by double clicking. I apologize for my ignorance there.

Have a good day,

Mike

Marco,

is there a chance, that the performance of the 'Find Database Object' functionality will be improved in the next release?

Thanks for your support.

We are looking into this, so there is indeed a chance.

I agree that the find database object is quite slow, actually useless in my opinion. I keep the old copy of SQL Navigator and use it whenever I need to search database source. This definitely needs improvements. Otherwise, excellent tool.