topic Re: SQL 2008 Spatial Query Layer Indexing in Data Management Questions

SQL 2008 Spatial Query Layer Indexing

AndrewWise — Thu, 13 Jan 2011 02:18:58 GMT

Hi All,

I am after some assistance with getting ArcGIS Desktop to use a spatial index I have set up on a large database with 3 million records.

There query ArcGIS uses (picking up via SQL profiler) is

declare @bb geometry
select @bb = geometry::STGeomFromText('POLYGON((338293500 126983400,338538336 126983400,338538336 126808775,338293500 126808775,338293500 126983400))',0)

select ST_NUM, FEANME, FEATYP, DIRSUF, MUN, COM_NME, ESZ, MSG, MSLINK, TEXT_ID, X_CORD, Y_CORD, FEA_MSLINK, VICMAP_ADDRESS_PFI, VICMAP_ROAD_PFI, CAD_STRING, GEOM, 1 as "ESRI_OID" from (select * from LIVE_MAPDATA.dbo.ADDRESS) a
where GEOM.STIntersects(@bb) = 1

This takes about 29 seconds to run.

I re-write the query with an index hint it returns almost instantly
select ST_NUM, FEANME, FEATYP, DIRSUF, MUN, COM_NME, ESZ, MSG, MSLINK, TEXT_ID, X_CORD, Y_CORD, FEA_MSLINK, VICMAP_ADDRESS_PFI, VICMAP_ROAD_PFI, CAD_STRING, GEOM, 1 as "ESRI_OID" from lIVE_MAPDATA.dbo.ADDRESS a with(index(IX_ADDRESS_SPATIAL))
where GEOM.STIntersects(@bb) = 1

Does anybody know how I can get ArcGIS to make use of the spatial indexing in SQL 2008 for query layers?

Thanks
Andrew Wise
GIS Specialist
Emergency Services Telecommunications Authority
Melbourne, VIC, Australuia

Re: SQL 2008 Spatial Query Layer Indexing

JonDeRose — Fri, 14 Jan 2011 13:16:57 GMT

Hi Andrew,

What version of SQL Server 2008 are you using?

Have you seen the list of bugs fixed by Microsoft with SQL Server 2008 sp1? One of these causes the optimizer to use the spatial index when its use is appropriate whereas in the initial SQL 2008 release this was not the case.

http://support.microsoft.com/kb/968369

Re: SQL 2008 Spatial Query Layer Indexing

AndrewWise — Tue, 18 Jan 2011 21:49:15 GMT

Hi,

Yeah I did see that but we are running SP2:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

Seems like something that should be added by ArcGIS / forced option when adding a query layer.

eg Use spatial index hints on this layer

Re: SQL 2008 Spatial Query Layer Indexing

richwebb — Mon, 04 Apr 2011 04:21:08 GMT

Hi,

Only a workaround till MS fix the bug (I hope). But after reading the second post in this thread http://social.msdn.microsoft.com/Forums/en/sqlspatial/thread/1d7c3d89-c9bf-4338-869b-a9d563a81838 I added a not very selective index to a GEOGRAPHY table and now SDE benefits from the Spatial Index.

By adding a definition query such as for eg. WHERE PERENNIAL IN ('1','2','3') - Perennial being a field in a table of hundreds of thousands of records with an index that has only 3 categories (not very selective) the SQL Server Query Optimiser has decided that the Spatial Index is lesser cost than the current clustered index scan and rendering via SDE is now order of magnitudes faster at lesser scales.

Not ideal but till the bug's fixed it will have to do...

Re: SQL 2008 Spatial Query Layer Indexing

JosephWallis — Thu, 11 Aug 2011 14:42:45 GMT

so if I am reading right, you essentially had to fool SQL server to use the more efficient index by creating a less efficient one?

Re: SQL 2008 Spatial Query Layer Indexing

RobertHu — Thu, 11 Aug 2011 15:50:04 GMT

If your SQL 2008 R2 is on a multiprocessor server see this article,

http://support.esri.com/en/knowledgebase/techarticles/detail/38871

or the reply from Ed Katibah in this thread,

http://forums.arcgis.com/threads/33970-SQL-Server-Native-Types-Pros-and-Cons

In UC, I talked to a couple of ESRI experts about spatial index. They recommanded to test different spatial grid properties. For example, the default is medium, medium,medium, medium, you can try low,medium,medium,medium or low,low,medium,medium, etc.

In my SDE GDB, the property change didn't help. However, the test is so easy, it may be worth to just try it on a slow FC.

Thanks!

Re: SQL 2008 Spatial Query Layer Indexing

JosephWallis — Fri, 12 Aug 2011 14:03:39 GMT

yeah I tried altering the spatial index levels to no avail. I think I'll try this on R2 with that hotfix though.