[fdo-users] postgis and performance

Zac Spitzer zac.spitzer at gmail.com
Mon Jul 28 05:38:39 EDT 2008 

So your after the same performance with the fdo provider for postgis
as say with oracle, mysql or sql server then?

I think the fdo db provider behaviour is generally slow across all databases,
have you tried doing what I did and comparing the different providers?

Are you saying that you want to achieve the same performance that you get
with mapserver and postgis, with mapguide and postigs?

?

On Mon, Jul 28, 2008 at 6:25 PM, alucas <alucas at srtec.com> wrote:
>
> 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
>



-- 
Zac Spitzer -
http://zacster.blogspot.com (My Blog)
+61 405 847 168

More information about the fdo-users mailing list