SQL Spatial Type vs. ArcSDE, Performance

Question

Before I embark on a a new web app project I need to evaluate the performance of SQL VS SDE.

My experience rests with SDE to perform spatial queries, I have not yet had an opportunity to experience SQL's spatial capabilities. I'm hoping some of you can lend your experience. Anyways...

The "current" SDE process through geoprocessing is this:

1) A polygon feature is created
2) Point records that intersect are selected
3) A subselection is created of the point records that satisfy a SQL statement

The point record set I am querying has almost 1,300,000 rows.

Currently I am seeing a selection speed of about 1,600 records per second.

Now assume none of the above exists and I have a polygon record and 1.3M point records in a SQL table using the Geometry Data Type:

If the above selections were executed natively in SQL what kind of performance would I experience?

Thanks!

RobertHu · Answer

Hi Keith,

I did a few tests, my processes are different from yours, just want to share my experience. Here are my steps,
1) Select one polygon using attribute from feature class 1;
2) Select polygons from feature class 2 that intersect with the polygon from the above step.

The FC2 contains about 800,000 polygons, and the polygons being intersected are about 8000.

The first test was done in ArcMap. The response times for the second selection are,
SQL Geometry: 23.66 sec
SDEBinary: 19.19 sec

The second test was in SSMS. The 2 selection steps were merged into one SQL statement. The response time is 4 minutes 36 seconds.

Both ArcMap and SSMS were launched on same client computer. The SQL geometry FCs and SDEBinary FCs are in different DBs, but under same SQL Server instance. The configurations of the two DBs are not same, for example, the sizes of data files are different. (Ahu..., Apples to Oranges).

I don't think my tests are conclusive in terms of performance. If I have to decide between SQL Spatial and SDEBinary, instead of performance, I would ask myself a different question.

Just my opinion. Thanks!

TomBrown · Answer

Just to be clear... what you stated is how the geoprocessing tool performs your operation. ArcSDE is simply a gateway for ArcGIS products to interact with the underlying DBMS platform. ArcSDE simply takes the SQL or request from the client application and passes it to the database. Therefore, if the geoprocessing tool is performing a compound query in two steps (first the spatial and then the second predicate filter) its performance will of course be different if the developer writes a query passing both predicate filters in the one query.

ArcGIS sometimes provides many options on how to perform a task, some which perform better than others based upon the data and the questions being asked.

In conclusion, you really can't compare the task you are describing with SQL and a native spatial type in the database. Apples to Oranges.

But, if others can provide their input and experience working with native spatial types - you may find the information useful.

Good luck.

JimBarry · Answer

An off-topic sub-discussion within this thread was split off and moved here.