In a layer for GPS sites I am using this query:<br><br> DATA "SHAPE FROM (select <a href="http://s.id">s.id</a> as id,s.site_id as site_id,upper(s.site_code) as site_code,s.shape,nvl(s.city,'n/a') as city,sa.array_code
as array_code,nvl(s.site_name,'n/a') as name from site s, site_affiliation sa where s.site_id = sa.site_id and s.site_type_code = 'CONGPS' and sa.array_code in ('SCIGN','BARD','PANGA','BARGN','PBO','EBRY','WCDA','AKDA')) USING UNIQUE ID SRID 8307 NONE VERSION 9i"
<br><br>The query works fine on its own, but when run via MapServer the query is run for EACH matching record, so the apache error log looks like:<br><br>[Tue Nov 21 11:20:02 2006] [error] [client <a href="http://132.239.152.99">
132.239.152.99</a>] [Tue Nov 21 11:20:02 2006].167983 msOracleSpatialLayerGetShape was called. Using the record = 7234.<br>[Tue Nov 21 11:20:02 2006] [error] [client <a href="http://132.239.152.99">132.239.152.99</a>] [Tue Nov 21 11:20:02 2006].168002 msOracleSpatialLayerGetShape. Sql: SELECT ID, SITE_ID, SITE_CODE, CITY, ARRAY_CODE, NAME, SHAPE FROM (select
<a href="http://s.id">s.id</a> as id,s.site_id as site_id,upper(s.site_code) as site_code,s.shape,nvl(s.city,'n/a') as city,sa.array_code as array_code,nvl(s.site_name,'n/a') as name from site s, site_affiliation sa where
s.site_id = sa.site_id and s.site_type_code = 'CONGPS' and sa.array_code in ('SCIGN','BARD','PANGA','BARGN','PBO','EBRY','WCDA','AKDA')) WHERE ID = 7234<br>[Tue Nov 21 11:20:02 2006] [error] [client <a href="http://132.239.152.99">
132.239.152.99</a>] [Tue Nov 21 11:20:02 2006].172106 msOracleSpatialLayerGetShape was called. Using the record = 7390.<br>[Tue Nov 21 11:20:02 2006] [error] [client <a href="http://132.239.152.99">132.239.152.99</a>] [Tue Nov 21 11:20:02 2006].172125 msOracleSpatialLayerGetShape. Sql: SELECT ID, SITE_ID, SITE_CODE, CITY, ARRAY_CODE, NAME, SHAPE FROM (select
<a href="http://s.id">s.id</a> as id,s.site_id as site_id,upper(s.site_code) as site_code,s.shape,nvl(s.city,'n/a') as city,sa.array_code as array_code,nvl(s.site_name,'n/a') as name from site s, site_affiliation sa where
s.site_id = sa.site_id and s.site_type_code = 'CONGPS' and sa.array_code in ('SCIGN','BARD','PANGA','BARGN','PBO','EBRY','WCDA','AKDA')) WHERE ID = 7390<br>...etc<br><br>On queries that return many results this really bogs down the server and performance suffers big time. It seems like using FILTER wouldn't make a difference since it would just replace the WHERE clause that already exists in the query... Ideas? Something I'm doing wrong? This is in MapServer
4.6.2 and Oracle 9i.<br><br>Thank you,<br><br>Ian<br>