Spatial Index Performance: SQL 2008 vs ArcSDE

[RussellMiles]

Has anyone done any benchmarking that compares the performance of these two spatial indexes?  Thanks.

[VinceAngelo]

Benchmarking of spatial index parameters tends to be specific to individual installs.
I've found that the "best" parameters vary from site to site.  While there isn't much
value in publishing the results, there is benefit in doing your own testing with
"large" layers which might produce the most improvement.

Keep in mind that there is no one "ArcSDE" index -- there are probably a dozen
different rough guidelines that will produce different "best" performance with
different data and query methodologies, and optimizing the data can greatly
impact the net performance.

Finally, you should make sure that the tests closely correspond to the operations
being performed -- No amount of spatial index tuning will improve a full table scan
query.  There may in fact be times where you want to use scale dependency from
the application client to query different tables, tuned differently, at different scales.

If you want to do your own benchmarking, there are utilities in 'se_toolkit' that
provide individual layer performance calculations, and well as statistical analysis
tools that could provide feedback for spatial index grid sizing.  See the old Forums
for details -- http://forums.esri.com/Thread.asp?c=158&f=2290&t=303576&mc=1#949714

Good luck.

- V

[RussellMiles]

Let me ask this question a different way.  Has anyone converted data from using the ArcSDE spatial index to using the GEOMETRY type in SQL 2008 with associated spatial index?  I'm wondering if the SQL 2008 spatial component can work as well or better than the ArcSDE index technology performancewise.

[DanSteen]

Although we have not conducted any formal benchmarking, we've converted some of our sde feature classes to spatial data type using the the default spatial index settings (geometry grid, 16 cells/object, medium granularity for all 4levels).  The problem we're seeing is the redraw times in Arcmap are much slower than they were as SDE binary.  For example, the redraw on a 200,000 poly dataset for a small extent (display <50 polys) was instantaneous as SDE binary but is taking about 2 seconds as sql spatial data type.  The spatial index works great for sql spatial operations but it appears as though Arcmap is not effectively using it.  We're getting the same results in v10 beta.    Would like to hear your experience and others as well as any other suggestions.

[ScottNoldy]

I'll add my experience to this.  We recently moved to SQL 2008 and using SQL Geometry datatype for storage of our SDE data.
We created an ArcGIS Server dynamic map service of bathymetry for the state.  It was taking approximately 19 seconds to render.  For testing purposes we set up an identical map service with the data stored in SDEBINARY.
Draw times dropped to under a second.

Tried the same experiment again with vegetation types.  22 seconds using SQL Geometry, 4 seconds using SDEBINARY.

We are in the process of converting all our data back to SDEBINARY.

I would really like to know why this is.

[RussellMiles]

To fune tune performance with SQL Geometry, it may be helpful to refer to this blog post: http://blog.geodatabase.com/2008/10/sql-server-2008-spatial-indexes-and.html

Let me know if you have any success with this.  Thanks.

[JoshuaBixby]

Although I have been working with ArcGIS 10 Pre-Release, the last time I dealt directly with this issue was ArcGIS 9.3.1 so what I am about to say may not apply to the new release.

For the datasets I have worked with, the extent of the spatial indexes on GEOMETRY and GEOGRAPHY types had a large impact on performance in ArcMap, both drawing and querying the data.  For whatever reason, ArcCatalog would usually define large spatial index extents (on the order of projection extents) for data loaded into SDE using the GEOMETRY type.  If my memory serves me, usually just deleting and rebuilding the spatial index in ArcCatalog would define better spatial index extents and result in better performing spatial indexes.  I do remember, though, rebuilding a couple spatial indexes in SQL Server Management Studio.

For the data our organizaiton was working with, we could never get the GEOMETRY type to perform as well as SDEBINARY, but we could get the performance differences down to acceptable levels.  For example, redraw/refresh times in ArcMap for a couple of dense datasets went from 15 to 25 seconds down to 2 to 5 seconds.  Not great but acceptable since we needed the data stored with GEOMETRY type.

Since SQL Server 2008 was the first version to support native spatial types, I am mostly chalking the performance differences up to the DBMS and not ArcSDE (not to say there isn't room for improvement with ArcSDE).  For example, SQL Server 2008 SP1 addressed a fairly substantial issue with the SQL Server Query Optimizer where poor costing information was having the optimizer ignore spatial indexes in favor of full table scans.  The workaround at the time, before the service pack, was to force the use of the index through a HINT, but ESRI had valid reasons for not wanting to re-write code to always force the index.  As spatial support matures in SQL Server, I expect the native types to become more viable.

I have generally been pleased with the performance of Query Layers in ArcGIS 10, so I may re-visit those datasets to check whether the experiences we had with 9.3.1 are still valid with 10.

[ScottNoldy]

To fune tune performance with SQL Geometry, it may be helpful to refer to this blog post: http://blog.geodatabase.com/2008/10/sql-server-2008-spatial-indexes-and.html

Let me know if you have any success with this.  Thanks.

That did it.  Draw times are now on par with sdebinary.

Thanks!

[JimSmith]

That did it.  Draw times are now on par with sdebinary.

Thanks!

Hi, I *really* need to know the content of that blog post, which is no longer available.
Can you provide me with the fix for slow draw times with SQL 2008 geometry on large sets of features?

thanks!

[JoshuaBixby]

Hi, I *really* need to know the content of that blog post, which is no longer available.
Can you provide me with the fix for slow draw times with SQL 2008 geometry on large sets of features?

thanks!

There was a domain name change, but the blog post is still available:
http://geodatabase.net/wp/sql-server-2008-spatial-indexes-and-arcsde-9-3/

The content of that blog post, and this forum discussion, is pretty good, but it all is nearly 2 1/2 years old.  ArcSDE has changed some and SQL Server has had lots of improvements to its spatial support.  If you are truly running SQL Server 2008 with no service packs and ArcSDE 9.3/9.3.1, I think there is likely a larger issue that needs to be discussed more than tuning spatial indexes.

Good luck!