Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm using a Query Layer on SQL Server in ArcMap. The Query Layer executes instantly in in SQL Server but but takes so long to draw in ArcMap that the system appears unresponsive for about 10 minutes or longer. During the ArcMap draw the one of the CPU's is maxed out on the SQL Server process.

My Query is the STIntersects of a buffer on a line feature (Shannon) against a polygon feature class (Townlands) , as follows;

SELECT TOWNLANDS.TL_ID,TOWNLANDS.Shape FROM dbo.TOWNLANDS as townlands
with(index(FDO_Shape)) 
JOIN dbo.Shannon on townlands.Shape.STIntersects 
(Shannon.Shape.STBuffer(2.0))=1

The query returns 186 rows instantly. These can be draw in the SQL Server Management Studio Spatial pane without a problem

When I build a Query Layer in ArcMap with exactly the same syntax the system becomes unresponsive but does draw eventually. It looks as though, perhaps, ArcMap s not using the spatial index or is doing so differenlt from SQL Server causing an inefficient query on SQL Server that takes an age to return.

Can anyone advise on a remedy?

Thanks

ArcGIS Desktop: 10.2
ArcSDE: 10.2
RDBMS: Database and version: SQL Server 2008
OS: Windows Server 
share|improve this question

3 Answers 3

As you stated, your query seems to execute swiftly on the database level. Even if you were able to make the SQL more efficient, the real performance is on the spatial level.

Spatial SQL statements, like the one you are using, were allowed only recently with the introduction of the geometry type. SQL Server 2008 for ArcSDE supports three geometry data type, SDEBINARY, GEOMETRY and GEOGRAPHY. The differences are listed here

For best performance make sure you are using the Geometry or Geography (not SDEBINARY though since it is obsolete and not recommended) based on the nature of your data whether you are using earth spatial reference or not. Also make sure to rebuild spatial index on the TOWNLANDS featureclass. You can do that from ArcCatalog by right clicking on the featureclass, properties and select the indexes tab.

Hope that helps.

share|improve this answer

I don't see in your query the need to do a join. Try instead using WHERE.

SELECT TOWNLANDS.TL_ID,TOWNLANDS.Shape 
FROM dbo.TOWNLANDS as townlands
with(index(FDO_Shape)) 
WHERE townlands.Shape.STIntersects 
(Shannon.Shape.STBuffer(2.0))=1

In the original query the join appeared of no benefit to the result; I didn't see any columns from the Shannon table in the select line. Therefore, it seems like extra work.

share|improve this answer
    
Welcome to GIS SE! Your Answer is very brief so to help this Asker and later readers would you be able to use the edit button to expand upon what you are suggesting, please? –  PolyGeo Nov 13 '13 at 5:00

This is a known limitation of using ArcGIS with SQL Server that doesn't have a simple fix as far as I am aware.

If the SQL Server query planner decides it needs more than one CPU to execute the query, the odds of the spatial index being used are low.

Microsoft are aware of the issue but aren't in a hurry to improve the query planner because it would affect all queries, not just the spatial ones.

The only reliable solution is to set your maximum degree of parallelism (MAXDOP) on your database to 1, but this means all queries on that DB will only use 1 CPU per query, slowing everything down.

Creating a view that represents the table and forces the spatial index hint doesn't work as ArcGIS needs to query the table's metadata and stats and such a view kills those queries.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.