phpmapscript - query caching?

[Mark Cave-Ayland]

> -----Original Message-----
> From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
> Behalf Of Chris Matheson
> Sent: 11 January 2006 16:07
> To: MAPSERVER-USERS at LISTS.UMN.EDU
> Subject: [UMN_MAPSERVER-USERS] phpmapscript - query caching?
> 
> Hi Folks,
> 
> I'm using postGIS (v1.04), postgreSQL (v7.4.8) and phpmapscript (from
> mapserver 4.4).
> 
> I need a bit of help.  I think I am using the wrong approach to this
> problem...
> 
> I'm creating a map with a report attached using php.  I'm creating a
> dynamic layer using postGIS & mapscript using a function like
> $layer->set("data", $statement).  Later I run the same statement using
> pg_query($connection, $statement) to get the attributes.
> 
> Everything works, but, because the query is big, and I'm running it
> twice, my script is really slow.  It's got to be a bad way to do it, it
> feels wrong, even to me.  Is there a way to avoid running the query
> against the db twice?
> 
> P.S. - I've tried using the attributes from the mapscript layer object,
> but I didn't have much luck and fell back to running it with pg_query.
> I want to run some php against the data to sort, filter and format it.
> 
> 
> Chris M.


Hi Chris,

I don't think the list received the copy of your attached PHP - you might
like to paste a simplified version of what you are trying to do into the
message body and repost.

In terms of sorting, filtering, and formatting the data, there is a good
chance you can get PostgreSQL to do all this for you as part of your data
statement using subselects, instead of having process it with PHP a second
time. But until we can see your sample code, it's almost impossible to help.

Also, it is worth checking your database schema and indices to see if your
original query can be sped up. Bad query plans can often mean the difference
between a 30 minutes query time and a sub-second query time.


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

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.