[Chameleon] Slow Rectangluar Query Performance

[LITTLE Nelson]

Hi all,

I found a solution to my problem, and I thought I would post my results
here as they might be useful for others.

Firstly I didn't have any special indexes on my PostGIS tables. This was
fixed via:

CREATE INDEX table_with_the_geom ON table (the_geom) USING GIST;

Secondly, my map file has about 10 PostGIS layers and was opening and
closing a new database connection for each layer. You can force
mapserver to use connection pooling (thus it will share the one
connection) by adding the following to each PostGIS layer in the map
file:

PROCESSING "CLOSE_CONNECTION=DEFER"

I also created indexes for any attributes which were used in the FILTER
parameter in the map file.

End result is a rectangular query that once took 30second in total to
return results and highlight the features now takes ~ 5 seconds!

Cheers,
Nelson

-----Original Message-----
From: chameleon-bounces at lists.maptools.org
[mailto:chameleon-bounces at lists.maptools.org] On Behalf Of LITTLE Nelson
Sent: Thursday, 6 September 2007 5:58 PM
To: chameleon at lists.maptools.org
Subject: [Chameleon] Slow Rectangluar Query Performance

Hi all,

I am using the latest Chameleon and Mapserver on a Window machine
running MS Windows 2000 on a Pentium 4 2.8Ghz with 1Gb of RAM.

I have noticed that when I perform rectangular queries using the Query
Widget with highlighting turned on, the performance, or time taken for
a) the query to complete, and b) the highlighted features to become red
is quite poor. 

For example, doing a query which will return ~ 4 features (2 from one
layer and 2 from another layer) takes 10 seconds to show the Query
Results and 29 seconds to highlight the features in the display. 

Furthermore, I noticed that during the query in the process list the CPU
usages peaks at 100% a couple of times and that php-cgi.exe uses a bit
of memory and CPU.

My map file consists of shapefile layers and postgis layers, but the
postgis layers are very simple and FILTER on columns that have indexes.
The same machine where I am running the client application also runs the
Postgresql database and Apache web server.

Can anyone offer any help or advice as to why things seem to be slow? 

Cheers,
Nelson

Disclaimer :
The contents of this e-mail including any attachments are intended only
for the person or entity to which this e-mail is addressed.  If you are not,
or believe you may not be, the intended recipient, please advise the sender
immediately by return e-mail, delete this e-mail and destroy any copies.
Tenix does not warrant nor guarantee that this email communication is free
from errors, virus, interception or interference.