Skip to main content
Tweeted twitter.com/#!/StackGIS/status/394625014450102273
edited tags; edited tags
Link
PolyGeo
  • 65.4k
  • 29
  • 114
  • 345
Formatted code, added tags.
Source Link
Paul
  • 11.6k
  • 1
  • 30
  • 47

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

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

Eamonn

ArcGIS Desktop: 10.2 ArcSDE: 10.2 RDBMS: Database and version: SQL Server 2008 OS: Windows Server

ArcGIS Desktop: 10.2
ArcSDE: 10.2
RDBMS: Database and version: SQL Server 2008
OS: Windows Server 

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

Eamonn

ArcGIS Desktop: 10.2 ArcSDE: 10.2 RDBMS: Database and version: SQL Server 2008 OS: Windows Server

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 
Source Link

ArcGIS 10.2 Query Layer on SQL Server performance

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

Eamonn

ArcGIS Desktop: 10.2 ArcSDE: 10.2 RDBMS: Database and version: SQL Server 2008 OS: Windows Server