Hello Readers. Almost all of us use many different types of software each day for various purposes. Some of this software could be open source, but most are closed source or proprietary.
By closed source, we mean that the source code is not shared, and its license may not allow us to meddle with its code and arrive at its derivatives. We have to optimize the performance of this software through its settings/configurations, and take some help from our underlying operating system.
In this post we use a very popular and versatile tool that most experienced developers must have used some time or other. The tool is called SQLDeveloper from Oracle. Oracle SQLDeveloper is a free, integrated development environment (IDE) used for developing and managing databases.
It provides a graphical user interface (GUI) that allows users to perform various database tasks more efficiently, including writing and executing SQL, debugging PL/SQL code, and managing database objects. The best part is SQL Developer can be used for connecting to, querying, and managing data: not only with Oracle, but also with IBM Db2, Microsoft Access, Microsoft SQL Server, MySQL, Sybase Adaptive Server, Amazon Redshift, and Teradata.
Case Study: Improving Performance – Closed Source/Proprietary Software
SQLDeveloper is a powerful SQL tool used to query, analyze and modify databases. As SQLDeveloper runs, it keeps acquiring more and more memory and could make the system slow.
We take the example of SQLDeveloper running on Windows OS. Most of the other applications we run with it would be GUI based. In Windows, this would need a bit more memory and CPU than some other operating systems.
Fig: Windows Task Manager Showing Memory and CPU Usage
If there are multiple database connections and data-heavy tables being queried, the memory footprint can become unnerving, and make other processes slow or unresponsive.
Hence we could follow the following steps to optimize it.
Steps to Find and Improve Performance Bottlenecks
1. Settings, Properties and Switches
The first step, as always, is to look for settings, properties and switches within the software itself, and fine-tune them for your current needs. We can turn off tools we don’t need. E.g. migration is needed only sometimes, so we might want to disable it.
Fig: Turning Off Migration in SQL Developer
If the current project doesn’t need “External Tools”, we can remove them. We can always add them later.
Fig: Removing External Tools in SQL Developer
2. Disable Extensions We Don’t Need
By default, most products are shipped with “Everything On” , just to highlight the tool’s power and versatility.
Here, we would enable only those that we need, and disable or remove all other extensions.
In our IDEs also, we can remove all extensions and plugins we don’t need for our current projects. They can be easily re-installed or enabled later, as and when the need arises.
Fig: Disabling Extensions
3. “Deselect” All the Features We Don’t Need
Below is a screenshot from an older version of SQLDeveloper, in case you’re not using a recent version.
Fig: Deselecting Unnecessary Features
4. Any Extension Not Related to the Database We’re Using Can be Disabled
For example, use the Teradata extension only if you’re running SQLDeveloper over Teradata.
Features like Application Express are often only needed by a small subset of users.
5. Use Specific Migration Tools Only
Fig: SQL Developer Preferences for Migration Tools
Extensions related to migrating databases from a specific database, like Teradata to Oracle, are specialized and not relevant for most users. We can disable these extensions if we don’t work with those particular databases. Use only those extensions that are relevant to your project.
6. Disable Other Third-Party Drivers
Extensions for other third-party database drivers (e.g., MySQL, PostgreSQL) are generally not needed unless you specifically work with those databases.
7. Use Only Relevant Specialized features
For example, APEX is a web application framework for Oracle, and the APEX extension is only useful if you’re actively developing APEX applications.
8. OLAP Option and Analytic Views
These are Oracle features related to online analytical processing. They may not be required for most users.
Fig: Disabling Online Analytical Processing in SQL Developer
10. Disable Other Non-Essential Features
To manage features and updates, you can go to Tools >> Features
Fig: Disabling Non-Essential Features in SQL Developer
We discussed only a few examples here. You should check your local SQLDeveloper installation for things you do not need. These may include:
- Data Modeling: If you primarily use SQL Developer for querying and development, and don’t need to create or modify data models, you may not need the data modeling extensions.
- In-Memory Pane: This pane is related to Oracle’s In-Memory database feature, which is not used by everyone.
- Partitioning Pane: This pane is related to database partitioning only, which is not essential for general development tasks.
11. Use History Sparingly
I always keep my “Max rows to print” and Mmax lines in script output” to a few hundred lines only. My sql history limit is also always set to a low value.
Unless your project requirements demand a huge volume of history to be remembered or printed, keep these values low. SQLDeveloper will tend to allocate separate memory pockets for each of these requirements. These will always be active, eating up your computing resources.
12. Do Not Keep Many Connections Open/active
Keep only those connections that you’re actually working on live, otherwise your internet bandwidth will be unnecessarily occupied.
13 . Use the Lite/Simplified UI
It’s a good idea to let your operating system handle the look and feel. This could make things leaner and quicker. Java, being platform agnostic, defaults to certain behavior, whereas your OS can optimize a few things on its own.
Fig: Changing the Look and Feel
14. Work smart
Close all SQL editor files, data grids and files that you don’t need in the next few hours.
This will be kinder to the garbage collector, as it has fewer things to handle 🙂
You will need to restart SQL Developer to make the changes take effect.
Conclusion
The above steps will not only make the software faster to run, but also faster to load the next time, as all unnecessary extensions/features have been disabled.
Your GUI as well as SQL queries will get nimbler.
More computing resources will be available for your other processes, making your system more responsive with higher throughput.
Please let us know your comments, suggestions and experiences using similar performance tuning techniques.