[fdo-users] PostGIS FDO Provider performance issues

[Carl Jokl]

I am involved in a project which has to manage large volumes of Gemometric
Data for Mapguide. An exising system is in place which makes heavy use of
SDF files. As part of the roll out of the next version of the system we are
looking into the alternatives available for storing the Geometric Data. One
promising solution has been to make use of PostGIS. My role currently has
been to write bencharking code in order to give an impression differences
between using flat file SDF vs PostGIS. The initial test has involved
writing C# .Net code to create providers for the two data sources and run
some identical tests and measure the time taken.

The initial version of the testing was simply to load all the data in a
fairly large data set (about 250,000 entries) which was loaded into the
PostGIS database and stored in an SDF 3.0 file. Nothing particularly fancy
was being done here just trying to see how fast data could be retrieved from
each kind of source.

It made logical sence that in this scenario SDF would likely be faster
considering that the provider was simply reading from a read-only file with
the file directly accessible and the data retrieval code for FDO running
within the same process as the testing code. By comparrison PostGIS would be
running on it's own process and be communicating vial TCP/IP albeit on the
same host but still causing extra overhead.

The results came back and though it would be expected SDF would be faster
the results were very bad for PostGIS beyond what was expected. All the
entries were retrieved from the SDF 3.0 file in about 15 seconds. By
comparrison the same data has been taking between 4 and 5 minutes to be
retrieved from PostGIS through FDO. In response to this a lot of tinkering
has been done with PostGIS configuration to boost performance such as the
Vaccuum analyse and checking the indexes etc but without these changes
making a significant difference. 

I wanted to try and Identify why retrieving from PostGIS was so slow given
that the information I could find on the internet showed PostGIS to be
considered to be quite performant and trusted by a lot of large
organisations.

I used the PGAdmin too to test selecting all the entries from the database.
The test database just uses a single table so doing a basic Select * from it
is sufficient to bring everything back. In PGAdmin the retrieval took about
50 seconds. This is much faster that the 4-5 minutes taken using FDO. I
tried also writing a little Java program to do the same retrieval via JDBC
to get an idea of performance accessing data from inside a program. In this
case the retrieval took about 12-13 seconds including initiating a database
connection. 

Looking at these two examples then did show that it is possible for achieve
fast data retrieval from PostGIS and based on that it seems more likely that
the problem is a poor FDO Provier implementation for PostGIS. This is fair
considering we are currently using a beta version of the FDO provider. 

It could be posible that the results are performing badly due to the
connection not being configured in the most efficient way for this kind of
activity. For example the SDF Connection is specified as being read-only
where I have not yet found of how (if it is possible) to set the the PostGIS
FDO connection to be read only or otherwise optimised to this kind of
retrieval.

Documentation (or even the FDO Provider downloads themselves) is proving to
be difficult to find.

My questions are: 1) Has anyone else had these kinds of severe performance
issues using the Alpha or Beta PostGIS providers?

2) Does anyone know or know where to find documentation about what settings
which can be used and are supported when creating an FDO connection to
PostGIS?

3) Is it known when a release candidate or release version of the PostGIS
provider can be expected?

4) Is there any other advice someone could give me regarding achieving the
best performance when using PostGIS through FDO?
-- 
View this message in context: http://www.nabble.com/PostGIS-FDO-Provider-performance-issues-tp17756475p17756475.html
Sent from the FDO Users mailing list archive at Nabble.com.