[fdo-users] PostGIS FDO Provider performance issues

[Zac Spitzer]

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