Troubleshooting WebSphere Commerce performance problems with the Rational Application Developer SQL Profiler
Identify SQL-related performance problems during unit testing
WebSphere Commerce uses primarily EJB entity beans as its persistence layer. Entity beans wrapped with access beans provide an object-oriented approach to accessing data in the database. The component services programming model uses the WebSphere Commerce Data Services Layer component, which uses Service Data Objects (SDO) that are mediated with the database. The entity beans and SDOs, like many other object relational mapping technologies, have a container or entity manager that manages persistence or mediation of these data objects.
The SQL for these persistence technologies is primarily generated by tooling. The container or mediator is responsible for executing the SQL statements and Java database connectivity API calls, thus making the actual database access opaque to the developer. These technologies help you, as a developer, focus more on developing the business logic. Unfortunately, sometimes the consequence of this approach is that database access performance problems occur when you are completely unaware of what your code is doing during a database activity.
Database vendors have provided ways to trace SQL statements. However, this approach does not permit easy cross referencing between the SQL statements and the actual Java code that produced it. Extracting the data set in parameterized statements has also been a difficult task with existing database vendor tracing. Developers working in their IDE toolkits are also not eager to run additional traces and tooling outside of their toolkits to try to capture SQL statements and attempt to correlate the data with their application code.
During the lifecycle of a typical WebSphere Commerce project, performance testing has occurred at the end of a construction or an iteration phase. Most performance problems encountered with WebSphere Commerce are related to the way the application code interacts with the database. The type of database interaction problems are primarily the quality and quantity of SQL statements produced by the application code.
These types of problems can be identified earlier during the code unit test construction phase and by the developer. This article introduces a unit testing asset to help developers identify SQL related problems. This permits earlier detection before performance testing at the end of each iteration or construction phase.
This article is intended for developers who develop solutions using WebSphere Commerce. You should have a good understanding of the Javs™ programming language, Java Enterprise Edition API, and the Structured Query Language (SQL) programming language.
Introducing the Rational Application Developer SQL Profiler
The Rational Application Developer SQL Profiler is a JDBC profiler and an Eclipse plug-in. The JDBC profiler captures SQL events executed in the WebSphereApplication Server environment and transmits them to the Eclipse plug-in, which provides a perspective for analyzing the data.
The SQL Profiler provides the following key features:
- Real time SQL capture and performance metrics computation.
- User-friendly user interface, a sorting display table, and user-friendly intuitive buttons and actions.
- Filter statement events by Java class name, Java method name, or excluding statement events executed within the WebSphere Commerce scheduler component.
- View SQL statements within their transaction scope.
- Links SQL statements back to the Java code with an execution tree view.
- Exposes parameterized data on prepared statements.
- Execution tree displays configurable software artifacts by regular expression patterns.
- Categorization of key artifacts by model view controller pattern. A color scheme in the call stack dialog and execution tree view based on model view controller categories.
- Saves captured data to a comma separated value or XML file.
- Supports Apache Derby, DB2®, or Oracle® as the data source.
Installing the SQL Profiler on the server
Download sql_profiler.zip from the provided sample zip file.
- Extract the contents of sql_profiler.zip into a temporary directory.
- Extract updatesite.zip from the sql_profiler.zip file into a temporary directory.
- Copy the jdbcProfiler.jar into your
- Start your WebSphere Commerce Toolkit.
- In your WebSphere Commerce Toolkit workspace, make sure you are in the J2EE perspective.
- In the project explorer view, open Enterprise Applications > WC, and then open the Deployment Descriptor for the WebSphere Commerce Server by double-clicking on it (Figure 1).
- Once the application deployment descriptor has opened, locate and
click the Deployment tab found at the bottom of the
Figure 1. Application Deployment Descriptor dialog
- Locate the JDBC provider for your database vendor within the "JDBC provider list" in the editor.
- Click the Edit button to bring up the "Modify a JDBC Provider"
dialog (Figure 2).
Figure 2. JDBC Provider dialog
- Change the "Implementation class name" to one of the proxy classes
for your database vendor. You need to enter the class name manually
since it will not show up as an option in the drop down list box. You
only need to change the implementation class name. The table below
shows a list of class names to use for different database vendors.
Figure 3 shows an example of DB2 as the data source.
Database vendor Proxy data source class name Apache Derby com.ibm.issw.jdbc.wrappers.WrappedDerbyDataSource IBM DB2 Type 2 CLI com.ibm.issw.jdbc.wrappers.WrappedDB2DataSource IBM DB2 Type 4 JCC com.ibm.issw.jdbc.wrappers.WrappedDB2JCCDataSource Oracle com.ibm.issw.jdbc.wrappers.WrappedOracleDataSource
Figure 3. JDBC Provider dialog using proxy data source for DB2
- Click Finish to return to the application deployment descriptor editor.
- Save the changes.
- Start the WebSphere Commerce Test Server and publish the server.
- Re-start your server.
Installing the client plug-in in Eclipse
The installation of the client plug-in uses Eclipse's update manager.
- From your Rational Application Developer menu, select Help
> Software updates > Find and install.
This brings up the "Install/Update" dialog (Figure 4).
Figure 4. Install/Update dialog
- Select Search for new features and then click Next.
- Within the Install dialog, click the New Local Site button.
- Navigate to the location where you extracted your updatesite.zip
content. Select the updatesite folder and then click OK
Figure 5. Browse for the folder
- In the Install dialog, check the checkbox for the newly created local
site and make sure all the other sites are unchecked. Click
Next (Figure 6).
Figure 6. Install dialog
- In the search results, select the RAD SQL Profiler feature and
click Next (Figure 7).
Figure 7. Search results
- Read and accept the license agreement and click Next (Figure
Figure 8. License agreement
- Select a location where you wish the plug-in to be installed and
click Finish. For example, install it next to the WebSphere
Commerce plug-ins in
Figure 9. Install location
- When prompted to install an "unsigned feature", click the
Install button (Figure 10).
Figure 10. Jar verification
- When prompted, accept to restart Eclipse by clicking the Yes
button (Figure 11).
Figure 11. Restart Eclipse
Your WebSphere Commerce Toolkit will re-start and your plug-in installation is completed.
Using the SQL Profiler
Now that you have installed and configured the profiler and you have installed the Eclipse plug-in, you are now ready to start using the SQL Profiler. This section introduces the SQL Profiler perspective and its two views.
SQL Profiler perspective
- Open the SQL Profiler perspective. In the menu, select Window
> Open perspective > Other. This brings up the
following dialog shown in Figure 12.
Figure 12. SQL Profile perspective
- Select the SQL Profiler perspective from the list. This opens
the SQL Profiler perspective, which looks like Figure 13.
Figure 13. SQL Profiler
- If your WebSphere Commerce test server is not running, then the
following dialog shown in Figure 14 will display to indicate that the
client plug-in was unable to connect to the remote profiler. This is
fine and you can re-connect the client to the Profiler after your
server has started.
Figure 14. SQL Profile connection dialog
Within the perspective you have two views. Let's take a moment to look at the views. Figure 15 shows the SQL Profiler perspective containing the captured SQL statement events.
Figure 15. SQL statements in the SQL Profiler
Execution tree view
The top view labeled "Execution Tree" displays an execution tree for each statement event captured by the Profiler. The root node for the tree will be a transaction identifier. This view allows you to trace back the application code from the SQL statement, which will always show up as the last leaf in the execution tree. Each node in the tree below the transaction identifier has a Java execution stack frame. To the left of the stack frame, there is a number count of SQL statements executed by this frame and all its children.
SQL Events view
The second view labeled "SQL Events" displays a table where each row is a captured SQL statement event. Here you have a number of columns that provides information on each event. Below is a description of each column:
- Count: Maintains a count of all the statement events in the table. You can click the column header to return the sorting of the table back to the natural sequence.
- TX Sequence: Maintains the sequence count for the events within the scope of a JTA/JTS transaction. Each time the counter resets when a new transaction has begun.
- Thread: Displays the Java thread name where this statement was executed. This is helpful to see if the statements were running within the Web container or the scheduler.
- Type: Displays the JDBC statement type that was used for the statement event. Valid types include statement, prepared, and callable.
- Prepare Time (ms): Displays the time in milliseconds it took for this statement to be prepared. This is the time spent in the Connection.prepareStatement() method in the Java JDBC API. WebSphere Application Server maintains a prepared statement cache and if the statement event was executed from a cached statement, the column will display "cached". This provides insight to the cache hit efficiency with the statement cache.
- Execution Time (ms): Displays the amount of time it took for the statement to execute. This is the time between the executeQuery() or executeUpdate() call on the Statement, PreparedStatement, or CallableStatement object.
- Rows Read: Displays the number of rows read from the application code. This is the number of times the call to ResultSet.next() and not the number of results the database could have returned with an SQL query statement.
- Rows Updated: Displays the number of rows updated by an update or delete SQL statement.
- Tables: Displays the table or tables that were included in the SQL statement.
- SQL Statement: Displays the actual SQL statement text exposing the parameterized data.
You can sort each column by clicking the column header.
The SQL Events view has a toolbar with a set of buttons. These buttons are described below:
- Resume/Connect: This button is enabled when the client plug-in is not connected to the remote profiler. Clicking this button will re-connect the client plug-in to the Profiler.
- Pause/Disconnect: This button disconnects the client plug-in from the remote profiler. This permits stopping any additional events allowing you to analyze the data captured from your request.
- Show call stack: This button opens the SQL Event details dialog. Double-clicking on a node in the Execution tree view or double-clicking on a row in the SQL Events view also opens the SQL Event details dialog.
- Add Filters: This button opens the Filter dialog. The Filter dialog allows you to set filters on the statement events displayed in the SQL Events view.
- Clear: This button clears all captured statement events. Both the Execution tree view and the SQL Events view will be cleared.
- Refresh: This button refreshes the two views.
- Save: This button opens the save dialog.
You can filter the SQL Events view by using the filter dialog shown in Figure 16.
Figure 16. Filter dialog
The Filters dialog sets three types of filters. These filters are a Java class filter, a Java method filter, and a WebSphere Commerce scheduler component filter. The Class and Method filter can be either inclusive or exclusive. The scheduler component filter is only exclusive.
- The class filter allows, once checked, two possibilities for entering the fully qualified Java class name you wish to filter. If the resource is in your workspace, then you can click the Class button on the right of the input label. This brings up the Eclipse resource selection dialog, which permits finding a Java type resource within your workspace. If the resource is not in your workspace, then you can enter the fully qualified class name in the input box.
- The method filter filters events based on a named method. You need to enter the method name in the input box. This is just the method name without parentheses and parameters found in the method signature.
- The scheduler component filter, once checked, will filter out all scheduler events from the SQL Event view.
SQL Event details dialog
Double-clicking any row in the "SQL Events" view or double-clicking any node within the "Execution tree" view will bring up the "SQL event details" dialog (Figure 17).
Figure 17. SQL event details dialog
Here you can see that the dialog contains two parts:
- The top part shows the full SQL statement and exposes the data on a parameterized statement. The SQL statement itself is selectable, allowing you to copy the text. Any data that was originally set with a parameter marker will show up with a surrounding question mark and parentheses "?( )". The actual data will be inside the parentheses. This distinguishes between parameterized data and literals. Literals will just be displayed as is with no preceding parameter marker or parentheses.
- The bottom part shows the Java call stack responsible for executing the statement. You'll note that certain stack frames have unique colors and are in bold. These colors are used to highlight the software artifact that has been categorized within the model view controller pattern. This color scheme is also used in the "Execution tree" view. The software artifacts themselves are selected based on a configurable regular expression pattern. You will see this in more detail when we look at the preferences dialog.
SQL Profiler preferences
The SQL Profiler comes with a set of user defined preferences that you can access via the menu by selecting Window > Preferences (Figure 18). Locate the SQL Profiler preferences in the preferences hierarchy.
Figure 18. Preferences dialogg
There are three major preferences options that you can modify:
- The first option is the remote profiler port number. This is the port
that the JDBC profiler is listening to that the client plug-in will
use to connect. The default value is 26000. However, if you need to
change this, you can do so in the preferences dialog. To set the bind
port for the profiler on the server side, you'll need to set the
following JVM property:
com.ibm.issw.jdbc.profiler.port=<portnumber>.You can set this using the WebSphere Application Server administrative console.
- The second option is the remote profiler host name. This is the host name where the JDBC profiler is running. Within your WebSphere Commerce Toolkit, this will be "localhost" and does not to be modified. The client server architecture for the SQL Profiler is not limited to running exclusively on your development environment. You can install the Profiler on any WebSphere Application Server environment and connect remotely. Furthermore, the client plug-in is not limited to running with Rational Application Developer and can run in plain Eclipse 3.0 to 3.2.
- The third option allows you to define regular expression patterns that will be used to build the execution tree and stack frame, highlighting in the SQL Events details dialog. Each pattern must be associated with a pattern category. The category allows you to define where the artifact fits within the model view controller pattern. A list of patterns is already provided, which is specific to the WebSphere Commerce programming model and framework. The list should be suitable for most cases.
Identifying performance problems with the SQL Profiler
Looking at individual statements
Using the SQL Events view, you can quickly see if there is a problem with an individual SQL statement. The prepare and execution times should be within an acceptable range. The number of rows read is also small. With an online transaction processing application such as WebSphere Commerce, you do not want to fetch large result sets from the database. The transactions and result sets are also small. The rows updated should be a small number when performing updates or deletes. If any of these values are too high, then you'll want to use the execution tree view to isolate the application code and resolve the problem.
Looking at all statements for a transaction
The SQL Profiler isolates the statement events by transaction. An application like WebSphere Commerce uses short transactions with the least amount of SQL. You'll want to make sure the total number of statements for a transaction is not too large. Even with an individual prepare and execution time of one millisecond, if you have one thousand statements for your transaction, you'll end up with one second response times already consumed by the database activity.
Inefficient fetching of aggregate data
Some navigation scenarios customers have developed with WebSphere Commerce to require fetching aggregate data from several tables. An example of this is a page that allows a shopper to view a side-by-side comparison of several products with most of the product attributes displayed for comparison. This is a case where using a smart data bean derived from an access bean is not very efficient. Let's assume that the shopper has selected five products and the comparison page displays ten attributes for each product. Using the data beans in the JSP to render this executes at least ten SQL query statements for each attribute, and then ten SQL query statements to fetch the attribute value multiplied by the five products displayed on the page. This results in one hundred unique SQL statements just to display the attribute data for this page. This type of access does not scale up and yields poor response times.
You can use the SQL Profiler to easily identify this problem. Whenever you see a high volume of repeated access to a single table, you have this problem.
Using the SQL Profiler to validate caching
While developing JSPs and commands, you can use the Profiler to validate caching. Using the "Execution tree" view, you'll see a branch for each JSP dynamic include. This helps isolate candidates for full page or fragment caching. Figure 19 shows the total number of SQLs for each JSP dynamic includes.
Figure 19. Number of SQLs for each JSP
You can see that the parent JSP was TopCategoriesDisplay. This JSP yielded 70 SQL statements. You can also see that this JSP called several dynamic includes (CachedHeaderDisplay, ContentSpotDisplay, and CachedFooterDisplay). If you continue to work down the tree, you will see other JSP dynamic includes, and finally the persistent objects to the SQL statements. Figure 20 shows an SQL statement at the end of the branch.
Figure 20. SQL statement at end of branch
Using the Profiler to better understand out-of-the-box behavior
The Profiler actually goes beyond its original objective of analyzing database interaction performance. The Profiler also helps you better unit test, given that it exposes the SQL statements and the associated Java code execution path. You can also use it to better understand out-of-the-box behavior with WebSphere Commerce. The online Information Center is the main source for understanding out-of-the-box behavior, but the Profiler provides a more detailed view of the database interaction of the WebSphere Commerce code.
Performance is a subject that needs to be addressed early on in any WebSphere Commerce project. Along with incorporating caching design and performance oversight during solution outline and macro design phases, it is also imperative to measure performance during the construction phase. The goal of this article is to provide an easy-to-use tooling for developers and to identify common problems using the SQL Profiler tool. Having these activities occur during the construction phase will better prepare and reduce the effort during performance testing that occurs at the end of each iteration or post construction phase.