topic Re: Spatial Index Performance: SQL 2008 vs ArcSDE in Data Management Questions

Spatial Index Performance: SQL 2008 vs ArcSDE

RussellMiles — Thu, 08 Apr 2010 14:15:36 GMT

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

Spatial Index Performance: SQL 2008 vs ArcSDE

VinceAngelo — Thu, 08 Apr 2010 18:08:07 GMT

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

Spatial Index Performance: SQL 2008 vs ArcSDE

RussellMiles — Thu, 08 Apr 2010 19:28:07 GMT

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.

Spatial Index Performance: SQL 2008 vs ArcSDE

DanSteen — Fri, 09 Apr 2010 13:29:53 GMT

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.

Re: Spatial Index Performance: SQL 2008 vs ArcSDE

ScottNoldy — Thu, 13 May 2010 14:12:46 GMT

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.

Re: Spatial Index Performance: SQL 2008 vs ArcSDE

RussellMiles — Thu, 13 May 2010 14:27:54 GMT

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.

Re: Spatial Index Performance: SQL 2008 vs ArcSDE

JoshuaBixby — Thu, 13 May 2010 15:32:01 GMT

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.

Re: Spatial Index Performance: SQL 2008 vs ArcSDE

ScottNoldy — Mon, 17 May 2010 14:37:36 GMT

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!

Re: Spatial Index Performance: SQL 2008 vs ArcSDE

JimSmith — Thu, 09 May 2013 22:15:40 GMT

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!

Re: Spatial Index Performance: SQL 2008 vs ArcSDE

JoshuaBixby — Tue, 14 May 2013 12:24:01 GMT

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!

Re: Spatial Index Performance: SQL 2008 vs ArcSDE

JimSmith — Tue, 14 May 2013 23:00:56 GMT

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!

Thanks for the link and feedback. For this issue, both ArcGIS and Geodatabase are 10.1 sp1, MSSQL is 2008R2. We imported a parcel base feature class of ~300,000 polygons, stored in sde_binary, and it performs just fine, as it always has since the days of MSSQL 2000 and 9.something ... but an exact copy of it with geometry storage, display and query performance is unacceptable, almost shapefile slow. The layer envelope is not the issue. I can't find any discussion on the support forums etc. or guidance in online help. Any suggestions?

Thanks!

Re: Spatial Index Performance: SQL 2008 vs ArcSDE

KeithAdams — Mon, 15 Jul 2013 20:16:27 GMT

Thanks for the link and feedback. For this issue, both ArcGIS and Geodatabase are 10.1 sp1, MSSQL is 2008R2. We imported a parcel base feature class of ~300,000 polygons, stored in sde_binary, and it performs just fine, as it always has since the days of MSSQL 2000 and 9.something ... but an exact copy of it with geometry storage, display and query performance is unacceptable, almost shapefile slow. The layer envelope is not the issue. I can't find any discussion on the support forums etc. or guidance in online help. Any suggestions?

Thanks!

If this ever gets / got answered, I'd LOVE to know because I'm in exactly the same boat, and on the same platform. To compound the problem, it is taking me 18+ hours to copy a single feature class of about 420,000 polygons, averaging 2,125 vertices / feature and I have too many feature classes to do to have the luxury of that kind of time. They all have to get done and they all have to get done in the same time window of less than 12 hours.

Thanks-

-= Keith Adams =-
SAIC HRSA Data Warehouse Systems Analyst