[fdo-users] postgis and performance

Mon Jul 28 04:25:28 EDT 2008

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.