In-Depth
IT's Challenge: Systemwide Optimization or Bust
Empowered with the right tools, IT can optimize application and database performance.
by Greg Nerpouni
The development of database-backed applications continues to accelerate despite shrinking IT budgets, and the distinct separation of duties between database professionals and application developers continues to blur. With DBAs becoming more focused on meeting production service level agreements (SLAs), application developers -- who are fluent in languages such as Java and C++, but admittedly not in SQL -- are being forced to pick up the slack and write their own SQL.
Given the knowledge vacuum created when a DBA’s attention is fixed on production, how do you, as an application developer concerned with productivity, remain agile and ensure your code is optimized system wide? With high performance top of mind, are you struggling to own the performance of your code from application to database and back? If so, you are not alone. As this trend grows, there is a driving need for system-wide profiling and optimization. We will address the shifting roles of database administrators and application developers and outline essential tools for building, fine tuning, and running high-performance applications.
Specialization Going the Way of the Dodo
About eight years ago, I was part of a data integration team working on a data warehouse project. The project team consisted mostly of consultants, including data architects, data modelers, database developers, DBAs, integration specialists, application developers, and business intelligence specialists. Each group of specialists had their tool of choice and never ventured out of the established division of labor. It was a costly initiative, and once the data warehouse was in production, the intentions of the company were clear: dump all unnecessary costs by transferring knowledge, excusing the consultants and consolidating duties amongst their own small team.
In today’s constricting economy, employing large number of project specialists to work on a single initiative makes no business sense. No IT budget on earth would support it. However, that does not mean that businesses are willing to slow down -- a "do more with less" mentality reigns now more than ever, and the most sought-after human resources have an adaptive mindset and a less specialty-oriented resume.
This goes for DBAs as well. Even though they tend to focus on availability and performance of production systems, they need to be able to do so across multiple database platforms. With the number of database-backed production systems growing, DBAs have less time to assist during the development lifecycle. A new level of self-sufficiency is necessary among the ranks of application developers who interact with the database and ensure poor-performing code does not leak into production (where it is significantly more expensive to fix).
Optimizing the Application Stack
When building systems in an agile development environment, you need the right tools so you are not spinning your wheels chasing red herrings. You need tools that will discover bottlenecks and help you focus your time on the code that matters (not to mention sift out dead code, reducing the overall footprint of your application). For instance, Java developers need visibility into a depth of layers within the application stack during development, quality assurance (QA), and production. During the development phase, the static software metrics measure the complexity of code and static software audits ensure the best code possible is being created and maintained. It also reduces overall rework by eliminating the problem before the next layer becomes the focus.
Other specialized tooling includes memory and CPU profiling, advanced threading analysis, in-depth code coverage reporting, and distributed Java component profiling so application developers can hone their code. Each of these layers deserves attention to ensure high performance, especially when applications move into QA and load testing
Depending on the size and complexity of the development shop, certain tools and pass/fail criteria are applied at different points of the development lifecycle. Often times, the tools that are used for detecting thread contentions, excessive locking, memory leaks, inefficient temporary storage issues, and CPU bottlenecks are leveraged in QA. In turn, QA shares these thread-, CPU-, and memory-related snapshots back to development for a more focused effort and a quick resolution, creating a collaborative and synergistic relationship between development and QA that serves the overall business very well.
Another tool that should be applied as you test and tune your application is code coverage. Code coverage tells you what code is being executed, including the frequency of each class and method, down to the very line of code itself, allowing you to easily root out dead code from your application and reduce its overall footprint. You can also discover what JUnit test cases are being leveraged during QA so you can ensure you have appropriate coverage from a regression perspective. If unit test cases are built in development and never get executed in QA, the overall quality of the code could be at risk, making such visibility fundamental.
Finally, there are the distributed components that make up a Java application, including EJB, JSP, JMS, and JDBC (among others), that typically need tuning. In the case of JDBC and database-backed applications, many tools let you drill down to view the SQL statement and executions details, including elapsed time and frequency.
Although this information is useful at a high level for application developers, it is limited in terms of assessing bottlenecks on the database or tuning the SQL for high performance, resulting in a black box effect that leaves you with no visibility or course of action.
Breaking Into the Black Box
In the past, there has been no easy-to-use tool for non-expert SQL developers to tune their SQL or assess performance bottlenecks on the database, leaving these developers handcuffed to the availability of an expert DBA. There are two simple ways to approach the issue. The first is by tuning SQL as you develop it or after you have discovered JDBC as a bottleneck. The second is by profiling the database as you test your application to discover poor-performing SQL and batch tune it for performance.
If you prefer to tune one statement at a time (whether as you write it or after a distributed Java component is analyzed), you can simply drop the SQL into a tuner.
The tuner suggests hints, SQL rewrites, and indexes to speed up your SQL code at the click of a button. Hints act as a GPS for your code by telling the database which turns to take in order to stay on the quickest path. SQL rewrites actually adjust your code to avoid common mistakes such as a Cartesian product and a suppressed index that can bring a database to its knees. Index analysis dives into the database and looks for performance opportunities that could speed up the retrieval of result sets.
As mentioned above, tools for profiling and optimizing Java code often reside in the QA department. In the same vein, tools that profile and optimize a database can now easily reside in development where bottlenecks can be discovered and analyzed before the snapshots are shared with DBAs, subsequently (and perhaps miraculously) creating a collaborative and synergistic relationship between developers and DBAs.
A tool that removes the complexity of discovering and diagnosing poor-performing SQL may represent its findings in a simple dashboard. For example, what is typically printed out on 30 pages of text in Oracle Statspack is now mapped to a simple graph of peaks and valleys along with the associated SQL code. Instead of relying on a DBA (whose attention should be fixed on production) to decipher 30 pages of text to help you assess your performance issue, you can profile the database, produce a visual snapshot, and focus your attention on the top SQL causing database and application performance degradation.
Should you need the assistance of a DBA, you can hand over a snapshot that allows DBAs to avoid spinning their wheels chasing red herrings -- a favor they will appreciate. The dashboard can highlight the SQL that requires your attention. In the same way you dropped the SQL into the tuner, you can sweep any SQL from the profiling session into the tuner and generate suggestions for fixing each statement, choosing whatever fix performs the best -- all at the click of a button.
Empowered with these tools, the time has come to break through the typical division of labor and into the black box to own the performance of your code from application to database and back -- to fill the void left by the consolidation of resources in a constricting economy with self-assurance and unheard of performance. For the application developer, it’s system-wide optimization or bust.
Greg Nerpouni is the senior product manager of developer solutions at Embarcadero Technologies where he’s responsible for delivering productivity and performance optimization tools to developers, QA engineers and DBAs. He has more than 13 years of product management and professional services experience. You can contact the author at
greg.nerpouni@embarcadero.com