DALESANDRO.NET

Tip 1: Optimize The Query

In my opinion, the first and most important step in performance tuning an application is to optimize the underlying query. If you have a slow query or a query that returns too much data, then the downstream processing will have a negative performance impact regardless of any optimization used in those subsequent steps.

• Minimize the number of columns returned by the query by using named columns instead of SELECT *. Are all of the columns from all of the joined tables truly needed?
• Add the appropriate constraint clauses to your query so that the returned data set is reduced to the right size.

If your operational data set is too large, i.e. you don’t need the data, then don’t return it. This will help improve downstream performance by limiting the amount of data processed by the DBMS and subsequently transferred over the network between the database and application layers.

• Use data aggregation functions in the query. If the data can be consolidated through summation or other aggregation techniques, this will help reduce the data set by returning one row instead of many.
• Identify opportunities to eliminate zero based row data. Depending upon your application requirements, it may not be necessary to return rows with zero or null data elements.
• Provide query hints to the DBMS. In some instances, it may be beneficial to assist the built-in query optimizer when the DBMS generates the execution plan. The optimizer does its best to generate the optimal execution plan, but sometimes the developer knows best.

It is sometimes necessary to fetch a large result set from a database in order to display data as part of a detailed analytical report. In these instances, user interface based performance tuning is not a viable option, e.g. splitting data across multiple viewable pages. A challenge I recently encountered involved the retrieval of a medium-sized data set (thousands of rows by tens of columns) and conversion of that data set into a structured HTML table. An Excel Web Query then imports the generated HTML table by calling a web service and transforming the data into a set of predefined Excel Pivot Tables. In a bit more detail, a Java based web application triggers a JDBC call to an Oracle Stored Procedure which returns a REF CURSOR to the result set. Once the result set is returned, Java iterates the data and converts it into HTML to be used downstream. Phew.

Over the next few posts, I will describe some tips and tricks for tuning your Java JDBC application.

After many attempts, I finally have the NextGEN Gallery plugin working together with the Auto Thickbox plugin. I use NextGEN Gallery for the Photo Gallery pages on this site because I like the way it manages photo albums and galleries. However, I also like to include images in posts outside of NextGEN Gallery’s control. I wanted all of the clickable images on the site to be displayed using a Thickbox, e.g. the fancy popup frame that shows a larger image and the photo title. NextGEN Gallery can handle the images in its albums, but any images in regular posts would just display the raw image file, i.e. no Thickbox frame or site wrapper. When I added the Auto Thickbox plugin to the site, NextGEN Gallery and Auto Thickbox were in conflict since they were both attempting to add a Thickbox to the album images. To get both plugins to work together, I did the following:

Within NextGen Gallery Administration:
Under Gallery -> Options -> Effects:
Set JavaScript Thumbnail effect: Custom
Link Code line: class=”thickbox” rel=”%GALLERY_NAME% nobox”

The “nobox” option instructs NextGEN Gallery not to add the Thickbox code. This then allows Auto Thickbox to add Thickbox code for all clickable images (including images in NextGEN Gallery managed albums). No more conflict!