[fdo-users] postgis and performance

alucas alucas at srtec.com
Mon Jul 28 06:30:39 EDT 2008 

Hi Zac,

The not the same performance ... better :-). 

zspitzer wrote:
> 
> 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
> _______________________________________________
> 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-tp18653591p18687666.html
Sent from the FDO Users mailing list archive at Nabble.com.

More information about the fdo-users mailing list