Querying a database: Hints & tips

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

This set of topics includes tips to help you

Enhancing data set performance

This section provides some tips for fine-tuning the performance of a QueryDataSet and a QueryProvider. For enhancing performance during data retrieval, eliminate the query analysis that the QueryProvider performs by default when a query is executed for the first time. See "Persisting metadata of a query" for information on doing this.

To enhance performance during data inserts/deletes/updates:

Persisting query metadata

By default, a query is analyzed for updatability the first time it is executed. This analysis involves parsing the query string and calling several methods of the JDBC driver. This analysis is potentially very expensive. You can remove the time overhead from run time, however, and perform the analysis during design of a form or data model.

To do this,

  1. Highlight the QueryDataSet in the designer, right-click it, and select Activate Designer.
  2. Press the "Persist All Metadata" button in the Column designer.
The query is now analyzed, and a set of property settings will be added to the code. For more discussion of the Persist All Metadata button, see "Using the Column designer to persist metadata". To set the properties without using the designer,

  1. Set the StorageDataSet's metaUpdate property to NONE.
  2. Set the StorageDataSet's tableName property to the table name for single table queries.
  3. Set the Column's rowID property for the columns so that they uniquely and efficiently identify a row.
  4. Change the query string to include columns that are suitable for identifying a row (see previous bullet), if not already included. Such columns should be marked invisible with the Column's visible or hidden property.
  5. Set the column properties precision, scale, and searchable to appropriate values. These properties are not needed if the metaDataUpdate property is in something other than NONE.
  6. Set the Column's tableName property set for multi-table queries.
  7. Set the Column's serverColumnName property set to the name of the column in the corresponding physical table if an alias is used for a column in the query.

Opening and closing data sets

Database and DataSet are implicitly opened when components bound to them open. When you are not using a visual component, you must explicitly open a DataSet. "Open" propagates up and "close" propagates down, so opening a DataSet implicitly opens a Database. A Database is never implicitly closed.

Ensuring that a query is updateable

When JBuilder executes a query, it attempts to make sure that the query is updateable and that it can be resolved back to the database. If JBuilder determines that the query is not updateable, it will try to modify the query to make it updateable, typically by adding columns to the SELECT clause.

If a query is found to not be updateable and JBuilder cannot make it updateable by changing the query, the resulting data set will be read-only.

To make any data set updateable, set the updateMetaData property to NONE and specify the data set's table name and unique row identifier columns (some set of columns that can uniquely identify a row, such as columns of a primary or unique index). See "Persisting metadata of a query" for information on how to do this.

You can query a SQL view, but JBuilder will not indicate that the data was received from a SQL view as opposed to a SQL table, so there is a risk the data set will not be updateable. You can solve this problem by writing a custom resolver.