performance with different data formats

Tue Aug 22 18:44:34 PDT 2006

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060822/03080d58/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 4114 bytes
Desc: image001.jpg
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060822/03080d58/attachment.jpg>