[fdo-users] postgis and performance

[Zac Spitzer]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cpu_load.png
Type: image/png
Size: 5028 bytes
Desc: not available
Url : http://lists.osgeo.org/pipermail/fdo-users/attachments/20080728/14c615e9/cpu_load-0001.png