R: [UMN_MAPSERVER-USERS] Performance improvement

[Fernando Simon]

Hi Antonio,
    Maybe this time delay is relate with Oracle connection cost
(login/logout), if you are using small extents, that don't cover all
your data, to use FILTER instead of NONE.
    For the SRID, the unique issue is that Oracle server will use more
CPU (cpu cost) to process and correct the extents of the query to apply
for your coordinate system. About the SRID you can try to define another
(not geodetic) that is good for your place. Geodetic are the SRID's
between 8192 and 8330 and equal  2. You can check the SRID's in the
"mdsys.cs_srs" table for all that you can define. I used this sql to
return all the SRID's that you can check and that are closer (can have
the same parameter) than your 8307:
         select cs_name, srid , wktext FROM mdsys.cs_srs WHERE wktext
like('%WGS 84%') and (srid < 8192 or srid > 8330) and wktext not
like('%UTM%');
     Your SRID is:
CS_NAME
--------------------------------------------------------------------
WKTEXT
--------------------------------------------------------------------------------
Longitude / Latitude (WGS 84)
GEOGCS [ "Longitude / Latitude (WGS 84)", DATUM ["WGS 84", SPHEROID
["WGS 84", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ],
UNIT ["Decimal Degree", 0.01745329251994330]]

     Maybe the SRID's 32771 or 1 can be used instead of 8307. But you
need to analyze what of the options is the best for you case.
     Best regards.

------------------------------------------------------------------------
Fernando Simon
Mapserver and Oracle Spatial developer
G10 - Laboratorio de Computacao Aplicada - Brazil
http://www.univali.br/g10 - UNIVALI/CTTMAR
------------------------------------------------------------------------




Antonio Iandolo wrote:
> Hi Fernando,
> thanks for your answer.
>
> I knew that the "NONE" clause wouldn't have been a good idea (the
> executed query in msOracleSpatialLayerWhichShapes doesn't include any
> spatial filter) but it actually is roughly the same:
>
> 1) "DATA GEOM FROM EDIFICI USING SRID 8307 NONE": about 8.522 seconds
> 2) "DATA GEOM FROM EDIFICI USING SRID 8307 FILTER": about 9.176 seconds
> 3) "DATA GEOM FROM EDIFICI USING SRID 8307 NONE VERSION 10g": about
> 8.594 seconds
> 4) "DATA GEOM FROM (SELECT GEOM FROM EDIFICI) USING SRID 8307 NONE":
> about 8.115 seconds
>
> Moreover I'm using an SRID 8307 since that's my data's coordinate system
> (is there a way to permanently "translate" my data in order to "cut"
> this overhead?)
>
> Anyway this is my actual layer in the mapfile:
>
>   LAYER
>     NAME "Edifici"
>     STATUS DEFAULT
>     DATA "GEOM FROM EDIFICI USING SRID 8307 NONE"
>     TYPE POLYGON
>     CONNECTIONTYPE ORACLESPATIAL
>     CONNECTION "XAFFADM/XAFF"
>     MAXSCALE 945248462
>     CLASS
>       NAME "myclass"
>       STYLE
>         SYMBOL 4
>         COLOR 131 0 0
>         OUTLINECOLOR 0 0 0
>         SIZE 2
>         MINSIZE 2
>         MAXSIZE 2
>         ANTIALIAS TRUE
>       END
>     END
>   END
>
> Any other suggestion/hint?
>
> Regards,
> Antonio
>
>
>
>   
>> -----Messaggio originale-----
>> Da: UMN MapServer Users List 
>> [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] Per conto di Fernando Simon
>> Inviato: venerdì 14 luglio 2006 13.38
>> A: MAPSERVER-USERS at LISTS.UMN.EDU
>> Oggetto: Re: [UMN_MAPSERVER-USERS] Performance improvement
>>
>>
>> Hi Antonio,
>> This issue is relate with your DATA definition. You are using 
>> NONE token, and it's indicate for Mapserver Oracle driver to 
>> don't use any spatial filter or spatial operator (SDO_FILTER 
>> or SDO_REALTE). Even that you are in a little extent that 
>> just contains 100 records, the NONE force to load all the 
>> 60.000 records in your case. Another point, are you using a 
>> geodetic SRID? If yes the Oracle use more internal 
>> mathematical functions to process the request. I suggest to 
>> change NONE to FILTER in you layer definition to test the 
>> performance. What is it the results when you change for 
>> FILTER? You can try to rebuild your index too. Best regards.
>>
>> --------------------------------------------------------------
>> ----------
>> Fernando Simon
>> Mapserver and Oracle Spatial developer
>> G10 - Laboratorio de Computacao Aplicada - Brazil 
>>     
> http://www.univali.br/g10 - UNIVALI/CTTMAR
> ------------------------------------------------------------------------
>
>
> Antonio Iandolo wrote:
>   
>> Hi all,
>> I've set up MapServer with a 4 layers map on an Oracle10g database 
>> thru a native connection (not OGR).
>>
>> The largest layer (which takes about 40% of the total time) is a 
>> polygon type of about 60,000 records (with a spatial index on the geom
>>     
>
>   
>> column) which isn't involved in any queryBy* php_mapscript function 
>> (only
>> $map->setExtent) and has this DATA definition:
>>   DATA "GEOM FROM MYLAYER USING SRID 8307 NONE"
>>
>>
>> The whole page takes about 9-11 seconds to be rendered, while the same
>>     
>
>   
>> php script (exactly the same, except for the mapfile), when 
>> interfacing ESRI shapefiles, (Oracle data come from ESRI thru 
>> shp2sdo.exe utility) takes less than 4 seconds in total...
>>
>> The most of time (about 80-90%) is taken by the $map->draw() method, 
>> where I supose it applies the spatial filter to determine which shapes
>>     
>
>   
>> are to be rendered...
>>
>> How could I improve these performance? Any suggestion/hint to check 
>> any bottleneck?
>>
>> My environment is:
>> - Pentium 4 - 1.8GHz - 512MB RAM
>> - Windows XP Professional SP1
>> - Apache 2.0.55
>> - MapServer 4.8.3
>> - php 4.4.3-dev (MS4W 1.5.3)
>> - php_mapscript_48.dll
>> - Oracle10g (XE) 
>>
>> Thanks in advance,
>> Antonio
>>
>>   
>>     
>
>