[fdo-users] PostGIS FDO Provider performance issues

[Traian Stanev]

WKB to FGF conversion is not really that expensive. Even in the fastest provider out there (SQLite provider), converting from WKB to FGF on the fly is on the order of 5-10% overhead from a normal feature read. I have not measured WKT -> FGF conversion, but I assume nobody who cares about performance is using WKT as native storage.

Traian


> -----Original Message-----
> From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-
> bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
> Sent: Tuesday, June 10, 2008 10:39 PM
> To: FDO Users Mail List
> Subject: Re: [fdo-users] PostGIS FDO Provider performance issues
>
> My understanding is that this is going to always be the case with FDO
>
> SDF effectively stores the data in native FDO format (AGF which is a
> superset of WKT),
> whereas when using a database like Oracle or PostGIS there is a
> translation required
> from the native format to AGF.
>
> Rather than test with a normal select, try doing a select
> SDO_UTIL.TO_WKTGEOMETRY(geom)
> for your test ( substitute for the PostGIS version of course)
>
> With King Oracle, doing it that way, is a more equivalent test than
> just selecting the geometry,
> the performance is then pretty similiar
>
> z
>
>
>
> On Wed, Jun 11, 2008 at 12:13 AM, Carl Jokl <carl.jokl at keynetix.com>
> wrote:
> >
> > 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.
> >
> > _______________________________________________
> > fdo-users mailing list
> > fdo-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/fdo-users
> >
>
>
>
> --
> Zac Spitzer -
> http://zacster.blogspot.com (My Blog)
> +61 405 847 168
> _______________________________________________
> fdo-users mailing list
> fdo-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users