Performance problem with drawing several layers using Postgis

[Frans Knibbe]

Andre,

A colleague suspects that the PROCESSING  "CLOSE_CONNECTION=DEFER" 
option is only used if OGR is used to access the PostGIS data and is 
ignored if you use the native PostGIS driver. Maybe someone can confirm 
this?

Regards,

Frans



Andre Karp wrote:

>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
>>>
>>>
>>>
>>>
>>>      
>>>
>>
>>    
>>
>
>
>  
>