The Objectix Loader is a cross-platform solution. It accomodates a number of different approaches to loading spatial data, but the only code specific to the supported platforms is in one class per platform (subclasses of SpatialDbms).

A platform specific solution is usually going to offer better performance, at the cost of additional development effort and vastly increased maintenance effort. A cross-platform solution ensures that most issues are resolved for all platforms when they are resolved for one.

General Issues

Spatial indexes should not be created before the seed load. Inserting rows into a spatial table with a spatial index built on it is vastly slower than building the index after the load. For example, our experience is that running a normal sized incremental on a full SqlServer/SpatialWare IUF database with spatial indexes in place runs several times longer than dropping the indexes, running the incremental and then re-building the indexes. SpatialWare tables do not even need to be spatialized before the seed load.

The historical table indexes (index.sql) need to be built before you start loading incremental files for Oracle and MySql, and right at the start for SqlServer/SpatialWare

Oracle and SqlServer normally run in transaction mode, but performance can be improved a little by running in auto-commit mode. This may be justified for the seed, but we strongly advise against it for incrementals. Dealing with exceptions is much more straight forward in transaction mode.

Platform Issues

MySql 4.1 performance is very close to optimal. Version 4.1 does not support stored procedures and the spatial load functions are OGC based. This makes it a very close fit for the cross-platform approach.

Oracle performance could be improved by using PL-SQL stored procedures in the persistence layer. We estimate this may improve performance by about 10%, but at a significant development and maintenance cost.

SQL Server / SpatialWare performance could also be improved using Transact-SQL procedures. The SpatialWare interface is the least integrated of all the supported platforms, and presents the biggest performance challenge. We have managed this issue by changing geometry creation from a one at a time scheme to an IUF file at a time scheme. We moved geometry creation to the stage 2 query, normally used to order updates correctly. This means that SpatialWare needs to run a stage 2 process even for seed files (the other platforms do not), and there is a slight performance overhead as a result. However this overhead is orders of magnitude lower than creating geometry one at a time in SpatialWare.