[Mapserver-users] mappostgis query

[Mark Cave-Ayland]

> -----Original Message-----
> From: mapserver-users-admin at lists.gis.umn.edu 
> [mailto:mapserver-users-admin at lists.gis.umn.edu] On Behalf Of 
> Markus Schnider
> Sent: 05 July 2004 12:53
> To: mapserver-users at lists.gis.umn.edu
> Subject: [Mapserver-users] mappostgis query
> 
> 
> Hi all
> 
> I am perhaps in the wrong  mailing list (it should be perhaps 
> in the developer 
> mailinglist ...), but I could not run a query on a layer with 
> more than 
> 2'000'000 rows. So I tried to change the code. Than I had 
> some results, and 
> the results comes faster!
> 
> mapserver 4.2 (mappostgis.c)
> postgresql 7.4.2
> postgis 0.8.1
> 
> Why is the following code (see below (1) ):
> better than this (2)?
> 
> I've tried both versions and version (2) is faster.
> 
> (1)
> -------------------------------------------------------
> 		if (strlen(user_srid) == 0)
> 		{
> 			sprintf(query_string_0_6,"DECLARE 
> mycursor BINARY CURSOR FOR SELECT %s from 
> %s WHERE (%s) and (%s && setSRID( %s,find_srid('','%s','%s') ))", 
> columns_wanted,geom_table,layer->filter.string,geom_column,box
> 3d,removeWhite(f_table_name),removeWhite(geom_column));
> 		}
> 		else
> 		{
> 			sprintf(query_string_0_6,"DECLARE 
> mycursor BINARY CURSOR FOR SELECT %s from 
> %s WHERE (%s) and (%s && setSRID( %s,%s) )", 
> columns_wanted,geom_table,layer->filter.string,geom_column,box
> 3d,user_srid);
> 
> 		}
> -------------------------------------------------------
> 
> 
> (2)
> -------------------------------------------------------
> 		if (strlen(user_srid) == 0)
> 		{
> 			sprintf(query_string_0_6,"DECLARE 
> mycursor BINARY CURSOR FOR SELECT %s from 
> %s WHERE (%s)",	columns_wanted,geom_table,layer->filter.string);
> 		}
> 		else
> 		{
> 			sprintf(query_string_0_6,"DECLARE 
> mycursor BINARY CURSOR FOR SELECT %s from 
> %s WHERE (%s)", columns_wanted,geom_table,layer->filter.string);
> 
> 		}
> -------------------------------------------------------
> 
> Thanks in advance
> 
> Markus Schnider


Hi Markus,

I can think of two reasons why your query may be failing.

Firstly, it may be that you are running into a memory/resource limit
when executing your query. A good place to start would be to change the
"FETCH ALL FROM cursor" command into a "FETCH FORWARD X FROM cursor"
(where X ~ 10,000 perhaps?) to reduce the memory requirements and copy
the data into the appropriate mapserver structure in batches - otherwise
the PostgreSQL libraries will attempt to load the entire 2 million rows
into memory before it does anything :) Do you get any messages in the
mapserver error log at all?

Another possibility may be that if the second query works quicker than
the first, perhaps the default statement_timeout parameter in your
postgresql.conf is too low and hence the query is aborted as it takes
too long to execute? I imagine that the second version of your code
should be much faster because the WHERE clause (which calls two other
functions) must be executed *once per row* and hence with 2 million rows
the time taken would definitely add up. Again, if the statement times
out, there may be an indication of this in the mapserver error log.


Kind regards,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.