Performance problem with drawing several layers using Postgis

[Andre Karp]

Hello Frans,

thank you for your reply. I also tried the postgis-connection with the
PROCESSING "CLOSE_CONNECTION=DEFER" option, unfortunatly with no significant
improvement.
I have not indexed my classification coloums yet, since finishing a map
request via postgis-connection took already 5 seconds without any
filtering/classifying which is far to slow for my purpose. So the problem
cannot be the filtering/classifying, but must have something to do with my
postgis-connection, I think.

Regards, Andre


----- Original Message ----- 
From: "Frans Knibbe" <frans at geodan.nl>
To: "Andre Karp" <karp at MSP-DORTMUND.DE>
Cc: <MAPSERVER-USERS at LISTS.UMN.EDU>
Sent: Wednesday, November 02, 2005 12:33 PM
Subject: Re: [UMN_MAPSERVER-USERS] Performance problem with drawing several
layers using Postgis


> Hello Andre,
>
> At least you could leave PROCESSING "CLOSE_CONNECTION=DEFER" in the
> mapfile. Have a look at the thread about PostGIS performance where this
> is suggested:
>
http://lists.umn.edu/cgi-bin/wa?A2=ind0510&L=mapserver-users&D=0&I=-3&X=44C29F25DCFA63B744&Y=frans%40geodan.nl&P=70952
>
> And do you have an index on the columns you use in the classification?
> I'm still not sure if this index will be used.. It would be interesting
> to see if this makes a difference.
>
> Regards,
>
> Frans
>
>
> Andre Karp wrote:
>
> >Hi,
> >
> >my problem sounds a bit obscure to me, but I couldn't figure it out on my
> >own neither searching the archive, so maybe someone of you can give me an
> >advice:
> >
> >I want to use the Mapserver (cgi) to draw a map in which the polygons are
> >colored according to a datebase attribute (I think I have to store this
> >attribute in MySQL or Postgre since it's changed quite often through the
> >internet, several times a day). The user should be enabled to select one,
> >several or all layers out of a set of 15 Layers. Every layer contains the
> >same geometry: about 400 Polygons, not too complex; the corresponding
> >shp-file is about 1000 kB, and drawing the shapefile with mapserver
> >overlaying it 15 times takes less than a second.
> >First I tried accessing the attribute data which is stored in a
> >MySQL-Database via the OGR/ODBC driver, and performance was very weak
(app.
> >10 sec.)(the join fields where indexed). Then I tried by moving my data
> >completly into a Postgis-based system (I also built a spatial index):
> >performace got better, but it still takes roughly 5 sec to get a map,
just
> >drawing 15 Postgis-stored layers without any filtering or table joins and
so
> >on. The application runs on a P3-800 Mhz with 512 MB RAM and Windows
> >2000/Apache System (well, not really fast, but should be sufficient since
it
> >has to handle only one request a time?).
> >
> >I would greatly appreciate if someone could give me an idea what could be
> >wrong with my application - thanks a lot in advance!
> >
> >Here the relevant parts of my Mapfile (using OGR/ODBC):
> >
> >LAYER
> >  NAME "Layer1"
> >  TYPE POLYGON
> >  CONNECTIONTYPE OGR
> >  CONNECTION "test.shp"
> >  DATA "SELECT * FROM test LEFT JOIN 'ODBC:user at testdb'.tbl_reg_cat ON
> >test.ET_ID = tbl_reg_cat.et_id"
> >  PROCESSING "CLOSE_CONNECTION=DEFER"
> >  STATUS ON
> >  ...
> >
> >and this using PostGIS:
> >
> >LAYER
> >  NAME "Layer1"
> >  TYPE POLYGON
> >  CONNECTIONTYPE postgis
> >  CONNECTION "user=test1 password=pw1 dbname=test_pg host=localhost"
> >  DATA "the_geom from test"
> >  STATUS ON
> >    CLASS
> >    ...
> >
> >Regards,
> >
> >Andre Karp
> >
> >
> >
> >
>
>
>