SQL Spatial Type vs. ArcSDE, Performance

[KeithSandell]

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!

[VinceAngelo]

What database product are you using (are a least half a dozen SQL-supporting databases
that also support spatial types)?  On what platform?

There isn't any way to predict the performance of native types across products or platforms,
or even in differing storage format on the same product/platform.  Even the same storage
format can give wildly different performance, depending on the nature of input data, order
of execution and tuning parameters.

- V

[KeithSandell]

What database product are you using?  On what platform? There isn't any way to predict the performance...

Well I wasn't looking for a "prediction" of things that "could be". I was hoping to find someone who could "share" an actual "experience."

Please forgive me, but it sounds as though you are being defensive because my "experience request" involved comparing an ESRI product based solution against a non-ESRI product based solution. I understand your loyalty to ESRI, but if your not going to be helpful don't bother responding.

For those of of you who may be interested in sharing an "experience" and feel that it would be helpful if I provide more information please consider the following:

ENVRIONMENT = SQL 2008 on Windows Server 2003 R2, SP2
QUERY = spatial query followed by an attirubute query

All things being constant (insomuch as possible) in either scenario and the necessary parameters/requirements being appropriate to each:

1) Submit a QUERY to an SDE feature class that is based on the ENVIRONMENT
2) Submit a QUERY to the ENVIRONMENT

"What" is the performance that you have "experienced"?

OR if none of this makes sense, just tell me about your performance "experiences" regardless of:

"products or platforms, or even in differing storage format on the same product/platform. Even the same storage format can give wildly different performance, depending on the nature of input data, order of execution and tuning parameters."

Thanks!

[JimBarry]

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

[TomBrown]

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.

[RobertHu]

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!