by Minna Lunney

Anyone familiar with Java database connectivity (JDBC) knows that this powerful functionality often comes at a price, in the form of decreased application performance.  The steps required to retrieve information from a database (e.g. log in/connect, perform query, retrieve records, return records) tend to be more numerous and time-intensive than the steps required to retrieve the same information from a file stored on the local machine.

The following are performance-boosting tips specifically geared toward database interaction in MapXtreme Java.

Use Connection Pooling

Establishing a connection to a database takes time and resources.  A program that has to access a database several times is quickly bogged down if forced to create a new connection for each “trip.”  With a connection pool, you create database connections that are then recycled by your program and other users/processes that may need them.  For more information about connection pooling, please refer to Chapter 11 in the MapXtreme Java Developer Guide: http://reference.mapinfo.com/software/mapxtreme_java/english/4_8_2/dev_guide/MapXtremeJavaDeveloperGuide.pdf

Prior to following the directions in the above documentation, you will need to perform these steps to ensure MapXtreme Java can connect to your database:

1) Shut down the MXJ server and quit the Web/StandAlone Manager(s), if necessary.

2) Locate or download the appropriate JDBC driver for your database.  If the driver is in .zip format, change its extension to .jar.

3) Copy the database driver to the following locations:

- [MXJ_Home]/lib/client

- [MXJ_Home]/[Tomcat folder]/webapps/mapxtreme[ver]/WEB-INF/lib

4) Browse to [MXJ_Home]/bin. Modify both the StandAlone and Web .lax files to include your driver in the classpath (the lax.class.path variable at the top of the file).  Follow the same forward- and backslash convention that is used for the other .jar files in the classpath, and separate each entry with the proper delimiter.  As an example for Windows:

lax.class.path=C:\\MXTJ482/MapXtreme-4.8.2/lib/client;C:\\MXTJ482/MapXtreme-4.8.2/lib/client/ojdbc14.jar;C:\\MXTJ482/MapXtreme-4.8.2/lib/client/mxjclient.jar;

….

The changes will take effect the next time you start the Manager(s) and/or MXJ server.

Specify Table Metadata

When retrieving a map table from a database, MapXtreme Java requires some basic information about the table, such as its coordinate system, spatial column, and rendition columns (if any).  This information is referred to as “table metadata.”  If you don’t supply metadata, MapXtreme Java will query the MAPINFO.MAPINFO_MAPCATALOG table in your database to obtain it, which adds to the time and resources required to retrieve the database table.

There are two ways to manually provide MapXtreme Java with the table’s metadata.  If you’re accessing the table through the MapXtreme Java Manager’s Add Layer Wizard, you can supply the metadata on the “Specify Other Table or Query Information” screen.  Click Use the following settings and fill in as many of the fields as possible before clicking Next.

Programmatically, you can provide metadata to the constructor of the TableDescHelper.  For example:

// Table information

String[] idColumns = {"mi_prinx"};

String table = "States";

boolean bUseQuotes = false;

String spatialCol = "GEOLOC";

String rendCol = null;

RenditionType perFeatureType = RenditionType.none;

String labelRendCol = null;

RenditionType perFeatureLabelType = RenditionType.none;

CoordSys csys = CoordSys.longLatWGS84;

int dimensions = 2;

String owner = "mary";

// Create a TableDescHelper

OraSoTableDescHelper tableTDH = new OraSoTableDescHelper(table,
 bUseQuotes, idColumns, spatialCol, rendCol, perFeatureType,
labelRendCol, perFeatureLabelType, csys, dimensions, owner);

Use Zoom and Visibility Constraints

By default, when displaying a map layer, MapXtreme Java will retrieve all records from the table, even if you are only viewing a portion of the layer.  Every time the map is operated on- pan, zoom in/out- MXJ will again retrieve all the features from the table before rendering the modified view.  When the map layer comes from a database, this can result in slow performance as potentially hundreds or thousands of records are returned each time the map is manipulated.  By setting zoom constraints on a layer, you limit MXJ to retrieving just those records that fall within the specified zoom range, and to retrieving no records if the map layer is not visible within a given map view.

Within the MapXtreme Java Manager, you can adjust zoom constraints by selecting the database layer within Layer Control, and clicking the Display button.  On the tab that appears, check Display within Zoom Range, enter the desired visibility range and distance units, and click OK.

Programmatically, you can use the following methods of the FeatureLayer class to control a database layer’s visibility:

FeatureLayer dbLayer = (FeatureLayer) myMapJ.getLayers().get(“DB_LAYER”);

dbLayer.setZoomLayer(true);

dbLayer.setMinZoom(new Distance(0, LinearUnit.mile));

dbLayer.setMaxZoom(new Distance(10, LinearUnit.mile));

Use QueryParams – Retrieve Only the Attributes You Need

When performing a search against a database table, the QueryParams class gives you control over the type and quantity of data retrieved.  For example, you may only need to retrieve the value of the “Name” column for each feature, in which case returning all columns and geometries would be overkill.  You can specify your search as attribute-only, geometry-only, or some combination thereof.  The more specific you make your return criteria, the better the search will perform.  For more information about the use of this class, please refer to Chapter 12 of the MXJ Developer Guide:

http://reference.mapinfo.com/software/mapxtreme_java/english/4_8_2/dev_guide/MapXtremeJavaDeveloperGuide.pdf

Also helpful are the MXJ Javadocs: http://reference.mapinfo.com/software/mapxtreme_java/english/4_8_2/docs/api/index.html

Use Pass-Through Queries – Retrieve Only the Records You Need

When loading a database table, you can limit which features and attributes are retrieved with the use of a pass-through query.  For example, SELECT ID, Name, MI_GEOMETRY, Population FROM myuser.world WHERE Population > 100000000.

When using the MapXtreme Java Manager, a pass-through query can be specified within the Add Layer Wizard:

Programmatically, a query can be defined within the constructor for the table’s TableDescHelper:

String sql = "SELECT SW_MEMBER, Longitude, Latitude, FROM Addresses WHERE Outfirm='OFFICE BLDG'";

CoordSys coordsys = CoordSys.longLatWGS84;

String[] idColumns = {"SW_MEMBER"};

XYTableDescHelper xyTDH = new XYTableDescHelper(sql,
 idColumns, "Longitude", "Latitude", null, RenditionType.none,
null, RenditionType.none, coordsys);

For more information, see Chapter 12 in the MXJ Developer Guide: http://reference.mapinfo.com/software/mapxtreme_java/english/4_8_2/dev_guide/MapXtremeJavaDeveloperGuide.pdf

Use QueryBuilders

If you wish to search a database table that has been defined by a pass-through query (as discussed above), you must associate an instance of the QueryBuilder class with the table.  The QueryBuilder can refine your search and limit the amount of information that is retrieved.  Sample QueryBuilder classes are provided in the examples/client/querybuilders directory on a default MXJ install.  You can use these classes directly, or implement your own QueryBuilder class to suit your specific needs.  More information is available in Chapter 12 of the MXJ Developer Guide:

http://reference.mapinfo.com/software/mapxtreme_java/english/4_8_2/dev_guide/MapXtremeJavaDeveloperGuide.pdf

Use Database Indexes

Creating indexes within the database from which mapping data will be retrieved will improve the performance of all database operations.  For instructions on creating an index, please consult the documentation for your specific database(s).

Keep Layers Simple

Tables with complex geometries will be more resource-intensive than tables that contain simpler geometries.  If possible, use MapInfo Professional to perform snap/thin operations on complex features before uploading a table to your database.  Directions for feature thinning in MapInfo Professional can be found in this Knowledge Base document: http://testdrive.mapinfo.com/techsupp/miprod.nsf/kbase_by_product/C04149D2C49B7AE185256AAB006E0349

For more information on how MapInfo Professional can be used to streamline tables, please refer to the MIPro User Guide: http://reference.mapinfo.com/software/mapinfo_pro/english/10/MapInfoProfessionalUserGuide.pdf