Slow loading of map template and seriously stressed DB

[PeterSteen]

Hi Zigs

I'm currently developing a small Arc dsktop app. Initial step is the
loading of a template with 4 tables in a PostGis workspace. Now
creating a new document based on the template takes more than one
minute and i notice that my PostGreSQL Db maxes out on CPU nearly as
long. As is, this is not very maintanable in a multi user environment.

My db schema contains some 70+ tables and views.

Any advice conserning this ? Hint: i plan to deploy this at a client
later this month and several licened ZigGIS installation will
result :).

Best and Keep up the good work
Peter

[Abe Gillespie]

Hi Peter,

Although we're happy to entertain any question, the zigGIS list is
more specific to zigGIS related discussion. You'll probably be served
much better over at http://postgis.refractions.net/support/

Thanks.
-Abe

[PeterSteen]

Hi Abe

I'm Sorry if i was unclear in my question. I do believe this has
something to do with ZigGis and the way ziggis creates a workspace.

I think that the request from ziggis is what maxes the db out when it
is creating a list of tables and views. When i try it on a db with
only a few tables (on the same server) no prob.

Peter

[PeterSteen]

Sorry guys

Just noticed that this has been adressed in previous posts. It's the
amount of tables no doubt. btw. Can't google do something about all
the spam ?

Peter

[Volkmar Herbst GCI]

Yes- this is still a problem for some.. I think the way Ziggis is going
about a workspace should be reviewed in a way that it is configurable to use
the Postgis geometry_columns tables. I used ziggis in a ArcMap extension and
had an agreement to adapt the ziggis code to our project. I have some
experience about the use of ziggis classes and it would be good to provide
an API for ArcObject developers.

I will list some problems and the way I have solved them:

- Field Types: The Postgresql and ArcGIS field types have to be compatible.
Try to use the compatible field types (not Numeric etc,) and you will be
fine. If this is not possible create updatable views with specific casts for
these field types. Then you don’t have to change your data model

- ObjectID column: If you have a non integer primary key (e.g. GUID) you can
also create an updatable view with a creation of an integer primary key by
using the window function row_number()with a creation of an integer primary
key in postgres

Other changes I did in our project to test but these involved changes in the
code which we did with a special agreement. It would be great if this would
be implemented because it would really extend the possibilities for
developing with ArcGIS:

- PostGIS workspace: To increase performance we used the geometry columns
for workspace creation.

- Added Event for getting the Edidt Layer which is used by Ziggis

- Added Event for getting the Ziggis Transaction in order to add own updates
(calculations, additional attribute updates etc.) It allows you also to
check the data in your own code before a commit.


In the end we really got it going quite well - it works great! Performance
is not like QGIS or ArcMap on a Shape file but it's quite acceptable.

Regards
Volkmar

-----Ursprüngliche Nachricht-----
Von: zig...@googlegroups.com [mailto:zig...@googlegroups.com] Im Auftrag von
PeterSteen
Gesendet: Donnerstag, 1. Oktober 2009 08:41
An: zigGIS
Betreff: Re: Slow loading of map template and seriously stressed DB