[fdo-users] postgis and performance

Traian Stanev traian.stanev at autodesk.com
Mon Jul 28 04:34:20 EDT 2008 


You can try connecting to PostGIS using the OGR provider. May be it could perform better for your data set than the PostGIS provider, if you can live with the limitations of the OGR provider.

Traian



> -----Original Message-----
> From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-
> bounces at lists.osgeo.org] On Behalf Of alucas
> Sent: Monday, July 28, 2008 4:25 AM
> To: fdo-users at lists.osgeo.org
> Subject: Re: [fdo-users] postgis and performance
>
>
> Hi Zac,
>
> I know that any db system is more slow than SDF or SHP. I don't want to
> compare POSTGIS performance with SDF. The objetive is to get the same
> performance with postgis than another db systems with FDO. In this case,
> the
> client have a postgis datastore and publish this layers vía mapserver
> and
> the performance is acceptable, but not with mapguide.
>
> Thanks.
>
>
> zspitzer wrote:
> >
> > Oracle is also pretty slow and cpu heavy with mapguide when compared
> > to SDF, the attached
> > graph shows the same dataset (some region boundaries in a 12mb sdf,
> > 270 rows written out using fdo2fdo from 135 rows in oracle))
> > being rendered via Studio off my desktop from Oracle and from a SDF.
> >
> > The first bump is the initial view in studio of the SDF layer,
> > The second is the same data but only 2 column in oracle,
> > The third is the has all the 32 columns in the SDF
> >
> > As you can see the SDF is much faster and uses much less cpu
> >
> > As for the column count, yep it doesn't make so much of difference
> > after all! sorry!
> >
> > it is only a small table I do remember that reducing the column count
> > made a difference tho
> > much bigger dataset...
> >
> > z
> >
> > On Mon, Jul 28, 2008 at 5:52 PM, alucas <alucas at srtec.com> wrote:
> >>
> >> Hi Traian,
> >>
> >> In this case, the user don't wants to change his spatial database
> system.
> >> We
> >> need to achieve a performance similar to Oracle Spatial or MS Sql
> server
> >> (even MySQL).
> >>
> >> PD: Sorry for my english.
> >> Thanks.
> >>
> >>
> >> Traian Stanev wrote:
> >>>
> >>>
> >>> Yes, to get best performance for this many features, consider
> publishing
> >>> a
> >>> snapshot of the data, exported to SDF or SHP format for example.
> >>>
> >>> Traian
> >>>
> >>>
> >>>> -----Original Message-----
> >>>> From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-
> >>>> bounces at lists.osgeo.org] On Behalf Of alucas
> >>>> Sent: Monday, July 28, 2008 3:08 AM
> >>>> To: fdo-users at lists.osgeo.org
> >>>> Subject: Re: [fdo-users] postgis and performance
> >>>>
> >>>>
> >>>> Hi zac,
> >>>>
> >>>> But... Views are more slowly than tables. The best choice would be
> to
> >>>> change
> >>>> the provider to avoid this string matching.
> >>>>
> >>>> Regards.
> >>>>
> >>>>
> >>>> zspitzer wrote:
> >>>> >
> >>>> > For better performance, try using views on your tables which
> only
> >>>> > expose the minimal
> >>>> > set of columns you require.
> >>>> >
> >>>> > Mapguide does "select * from table" which slows things down a
> lot,
> >>>> > because each column
> >>>> > is accessed via name rather than index and that's a lot of
> string
> >>>> > matching..
> >>>> >
> >>>> > http://trac.osgeo.org/mapguide/ticket/272
> >>>> >
> >>>> > i.e. with a 500k row table, each additional column is now
> involved in
> >>>> > another 500k of string matches
> >>>> >
> >>>> > Regards
> >>>> >
> >>>> > Zac
> >>>> >
> >>>> > On Sat, Jul 26, 2008 at 1:09 AM, alucas <alucas at srtec.com> wrote:
> >>>> >>
> >>>> >> Hello List,
> >>>> >>
> >>>> >> I'm testing postgis to store some Gregraphic layers and publish
> this
> >>>> data
> >>>> >> to
> >>>> >> the internet via Mapguide Enterprise 2009. I've performance
> problems
> >>>> to
> >>>> >> publish some large volumes of vector data. For example,
> mapguide
> >>>> can't
> >>>> >> preview (with Autodesk Mapguide Studio) one layer with 140.000
> >>>> records. I
> >>>> >> need to publish some layers with aprox 500.000 records
> >>>> >>
> >>>> >> So, I need to manage large volume data sets with postgis and
> >>>> mapguide and
> >>>> >> I've some questions:
> >>>> >>
> >>>> >> - In the unit tests for the postgis provider, what are the
> volumes
> >>>> of
> >>>> >> data
> >>>> >> tested?
> >>>> >> - I've loaded the data into postgis via shp2pgsql. I don't know
> if
> >>>> the
> >>>> >> default database created via shp2pgsql is the best for the
> postgis
> >>>> >> provider
> >>>> >> performance. It's possible to improve the database performance
> for
> >>>> >> example
> >>>> >> creating some gist indexes? If yes, how can I create the
> optimal
> >>>> index to
> >>>> >> achieve the best performance in queries?
> >>>> >>
> >>>> >> To summarize, I need some help for optimize my postgis
> datastore...
> >>>> >>
> >>>> >> Some useful information about the system:
> >>>> >>
> >>>> >> MGE 2009
> >>>> >> Autodesk Mapguide Studio 2009
> >>>> >> Postgis 8.2.9
> >>>> >> Postgis 1.3.3
> >>>> >> FDO provider for postgis: 3.3.0 (Downloaded from GeoMap web
> page)
> >>>> >>
> >>>> >> Thanks in advance.
> >>>> >>
> >>>> >>
> >>>> >> --
> >>>> >> View this message in context:
> >>>> >> http://www.nabble.com/postgis-and-performance-
> >>>> tp18653591p18653591.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
> >>>> >
> >>>> >
> >>>>
> >>>> --
> >>>> View this message in context: http://www.nabble.com/postgis-and-
> >>>> performance-tp18653591p18685149.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
> >>> _______________________________________________
> >>> fdo-users mailing list
> >>> fdo-users at lists.osgeo.org
> >>> http://lists.osgeo.org/mailman/listinfo/fdo-users
> >>>
> >>>
> >>
> >> --
> >> View this message in context:
> >> http://www.nabble.com/postgis-and-performance-
> tp18653591p18685669.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
> >
> >
>
> --
> View this message in context: http://www.nabble.com/postgis-and-
> performance-tp18653591p18686043.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

More information about the fdo-users mailing list