performance with different data formats

Wed Aug 23 12:29:52 EDT 2006

Mark,

  You may want to change the "select *" to "select col1,col2, etc") if you
have a lot of attributes in the table to ensure the query returns ONLY the
attributes you need.  I seem to recall that mapserver does string malloc'ing
and type conversion (and of course there are other overheads).  I was able
to cut my query time in half (using PostgreSQL) with that simple change...

Brent Fraser
GeoAnalytic Inc
Calgary, Alberta, Canada

----- Original Message ----- 
From: "Mark Adams" <Mark.Adams at DPRA.COM>
To: <MAPSERVER-USERS at LISTS.UMN.EDU>
Sent: Wednesday, August 23, 2006 8:49 AM
Subject: Re: [UMN_MAPSERVER-USERS] performance with different data formats

Hi Fernando,

I'm not sure I understood all of your comments, so here's some more info
about how we've defined our layers in Oracle Spatial. Here's a typical
data connection:

DATA "GEOM from (select * from MINFWHSE_P1.REFERENCES order by
C_REFty_REFERENCE_TYPE_DESC,C_REFER_REFERENCE_NBR) using unique REF_ID
FILTER VERSION 9i"

In this case the REF_ID column is indexed. We don't always use the data
at full extents or at any particular scale, it is entirely up to the
user to zoom in and out, but mostly the users are likely to zoom in to
whatever geographical area is of interest to them, so that's why we
included the FILTER directive. In the example application we are testing
with the data covers a large geographical area (all of NorthWest
Territories and Nunavut in northern Canada, we have a spatial index
built in Oracle as an RTREE index.

>Another issue with you data in Oracle is SRID that you define. If you
are
>using geodetic data the query and the draw will more slow than no
geodetic
>data

I'm not sure what is meant by "geodetic". The data in the case of our
Oracle Spatial testing is projected using a custom Lambert Conformal
Conic projection optimized for the Canadian north. We have not defined
the SRID in Oracle, rather we just define it in the MAP file with an
epsg code (we've added the projection definition to the epsg
configuration file for use with proj4). Does this matter, from a
performance point of view?

Thanks for your help.

------------------------------
Mark Adams
General Manager, Ottawa Office
DPRA Canada
440 Laurier Ave W
Suite 200
Ottawa, ON K1R 7X6
Phone: 613.782.2326
Fax: 613.782.2228
Email: mark.adams at dpra.com

-----Original Message-----
From: Fernando Simon [mailto:fsimon at univali.br]
Sent: August 23, 2006 10:21 AM
To: Mark Adams
Cc: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] performance with different data
formats

Hi Mark,
    For Oracle and all of the databases sources Mapserver have a big
problem if query's. Because it will execute a select row by row for the
result of your query, and plus, will call login/logoff for every select.
To improve this performance you can use the
PROCESSING="CLOSE_CONNECTION=DEFER" in all of your layers to force
Mapserver to use the same connection for all of your query's.
    Specific for Oracle, you can use in your USING UNIQUE a columns that
it's the unique identifier (and indexed) for your table. Another hint is
with the token that are you using in your data connection, if in the
most of time you use the full extent of your data you can define the
token NONE in your data. This token force Mapserver to not use the
Oracle Spatial operator to query your data, maybe will more faster. If
in the most of the time you will work with specific extent of your data
you can force Mapserver to just uses the SDO_FILTER operator, by default
Mapserver always uses this operator, but you can force to always use it
just passing FILTER in your data definition.
   Another issue with you data in Oracle is SRID that you define. If you
are using geodetic data the query and the draw will more slow than no
geodetic data. With Oracle 9i the kind of your index can degrade the
performance for request spatial data. You can check the kind of the
index that are you using and check the performance in database for the
query.
   You can try adjusts directly in the Oracle Server too, like views,
tablespaces.... But I believe that it's the hint's that come in my mind
now, so it's the hint's now for Oracle Spatial connection.
   Best regards.

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

Bart van den Eijnden (OSGIS) wrote:
> Mark,
>
> some things that come to my mind:
>
> 1. Mapserver's query mechanism is quite inefficient against database
> sources (two pass, and pass by id to get the actual record, so for
> every feature in your resultset a SQL statement is executed against
> the DB). So I am not surprised about your query performance, I have
> had the same experiences. What USING UNIQUE clause are you using in
> your MAP file, and can you check that this pass by ID is indexed in
> the database?
>
> 2. You could try Mapserver as a Fast-CGI since connection overhead
> could be an issue
>
> 3. You could also get Mapserver to output the XML (i.e. GML) of a
> FeatureCollection if you would use the Mapserver WFS interface. Then
> using an XSLT you could create your HTML table. Maybe this is faster,
> but it still suffers from 1. ofcourse.
>
> Hope this helps.
>
> Best regards,
> Bart
>
> Mark Adams wrote:
>>
>> Hi All,
>>
>> I'm looking for ideas and explanations regarding performance when
>> publishing maps from ArcSDE and Oracle Spatial, and also with
>> displaying attribute data from these data source. We have been
>> working for a while now with from these formats through MapServer and
>> PHP mapscript, and have found the performance has been disappointing.
>> Specifically, we are trying to use Oracle Spatial (9i) as well as
>> ArcSDE against both Oracle and SQL Server (we gave up entirely on MS
>> Access personal geodatabase through OGR). Generally speaking we've
>> found that map draw operations are substantially slower with these
>> data sources than if we use Shapefiles; and that the performance hit
>> is even greater with drawquery operations, especially as the size of
>> the selected feature sets increases.
>>
>> We did some informal testing, both within our MapServer environment
>> to compare the performance with different data formats, and also
>> between MapServer and ESRI ArcIMS environments. Here are some of the
>> results we came up with:
>>
>> 1. We compared MapServer performance with SQL Server ArcSDE and
>> Shapefiles (with spatial indexes defined for the Shapefiles), with a
>> large polygon layer representing property parcels (329870 records).
>> To draw the whole layer at full extents, it took 10 seconds with
>> Shapefiles, 26 with SDE. To zoom in to a small area, 0.3 seconds with
>> Shapefiles, 2.2 seconds with SDE. These times are just the draw times
>> reported from the debugging.
>>
>> 2. We compared MapServer performance with Oracle Spatial 9i Standard
>> Edition and Shapefiles, using a polygon layer that has about 6000
>> records. To draw the whole layer at full extents, it took 0.29
>> seconds with Shapefiles, 0.98 with Spatial. To zoom in to a small
>> area, 0.28 seconds with Shapefiles, 0.6 seconds with Spatial. We also
>> tested selected and highlighting features: select 75 records
>> (querybyrect), 0.5 seconds with Shape, 2.7 with Spatial; select 3000
>> records, 0.5 seconds with Shape, 9.4 with Spatial.
>>
>> 3. We compared MapServer performance with ArcIMS performance using
>> the same data sources (the property parcel layer with 329870
>> records). This is an even more informal test as we didn't have access
>> to the same debugging info on the ArcIMS side, so I will just say
>> that comparing with Shapefiles only, the 2 platforms were roughly
>> comparable; but ArcIMS speeds up a little with SDE, while MapServer
>> slows dramatically with this format.
>>
>> 4. We have even more problems when we want to use the results from
>> the selection in test 2 to show the attribute data to the user on a
>> separate HTML form. On the ArcIMS side we can process attribute data
>> quick quickly as the server sends back an XML file with the attribute
>> data in it. On the MapServer side the only way we know to publish the
>> attributes is to loop through each field of each returned record to
>> create the HTML table. Extracting the data in this fashion is again,
>> for some reason, way faster with Shapefiles as the selection sets get
>> bigger - for example with the 3000 record test it took 3.2 seconds
>> for Shapefiles, and 9.4 seconds with Oracle Spatial. When you add
>> those times to the draw times the Oracle Spatial approach becomes
>> unworkable; likewise with ArcSDE.
>>
>> So, there are some questions in all this that I'm hoping the experts
>> on the list can answer: are we doing something wrong, or are these
>> numbers to be expected, and if the latter, why are these other,
>> supposedly enterprise type data sources so slow? Is there a better
>> way to get the attributes onto a form than looping through each
>> record, each field? Are there steps we can take to optimize the
>> performance of Spatial or SDE with MapServer?
>>
>> I know there are lots of other users out there using Spatial and SDE
>> - I'd really appreciate hearing what has worked for you.
>>
>> Finally, anticipating questions on the testing technique, I do want
>> to stress the use of the term "informal" to describe these tests -
>> this was not a formal benchmarking exercise. But the results bear out
>> the experiences we've been having over months of working with these
>> datasets, so while the precise times are not to be taken too
>> seriously I think the general results are representative.
>>
>> Thanks in advance.
>>
>> ------------------------------
>>
>> Mark Adams
>>
>> General Manager, Ottawa Office
>>
>> DPRA Canada
>>
>> 440 Laurier Ave W
>>
>> Suite 200
>>
>> Ottawa , ON K1R 7X6
>>
>> Phone: 613.782.2326
>>
>> Fax: 613.782.2228
>>
>> Email: mark.adams at dpra.com <mailto:mark.adams at dpra.com>
>>
>
>