Tuning spatial indexes
Release 9.2 Last modified December 3, 2010	Print all topics in : "Tuning an ArcSDE geodatabase"

About the spatial index

NOTE: ArcSDE Enterprise only

NOTE: Beginning with ArcSDE 9.2 geodatabases, the spatial index is calculated for you when you create a new dataset and provide a coordinate system in ArcGIS Desktop. You shouldn't need to tune such spatial indexes. However, if you are using an older release of ArcSDE, you can follow the steps below to tune the index.

The multilevel spatial index is used to decrease the time it takes to locate features that match a spatial query. ArcSDE uses a multilevel spatial index for data stored in the ArcSDE compressed binary and Open Geospatial Consortium well-known binary (OGCWKB) geometry storage types. These geometry storage types are only available if you are using Oracle or SQL Server databases, so the steps provided below for tuning the spatial index only apply to those databases and those geometry storage types.

NOTE: IBM DB2 Spatial Extender also uses a multilevel grid spatial index. Since the spatial index is generated by the Spatial Extender, you should use the Index Advisor tool provided by IBM to help tune this index. You can read more about this tool at IBM's Information Center.

Learn more about spatial indexes generated by the DB2 Spatial Extender

The multilevel spatial index defines imaginary x/y grids for a feature class. There may be one, two, or three of these grid levels defined per feature class. The size of each grid in an ArcSDE feature class must be at least three times the previous grid size. Most feature classes need only one grid level, but more levels may be needed if the average sizes of the feature envelopes (the bounding boxes of each feature) vary widely.
Learn more about spatial indexes

Each feature in the feature class is indexed using only one of the grid levels; small features are indexed in the first level and larger features in the second or third grid levels, if these levels are present. ArcSDE places an entry (a row) in the spatial index table for every instance in which a single feature intersects a single cell in the one grid level used for that feature.

When a spatial query is issued, ArcSDE performs primary filtering and secondary filtering to return all features that meet the requirements of the spatial query.

For primary filtering

• The first thing ArcSDE does during primary filtering is find the envelope of the spatial filter shape. This is the shape that is specified in the query as the limiting factor. For example, if your query selects all parcels that intersect neighborhood X, neighborhood X is the spatial filter shape.
  
• Next, ArcSDE identifies the grid cells in all grid levels that intersect the envelope of the spatial filter shape.
  
• Finally, ArcSDE returns all the features that have envelopes that intersect those grid cells. This is called the result set. In the neighborhood example, the features in the result set would include all the parcels with envelopes that intersected neighborhood X's envelope.
  

Secondary filtering is done to reduce the result set to the features that satisfy the spatial query.

The default grid size settings are designed to ensure that the data can be loaded. In most circumstances, the default grid sizes will be appropriate for fast spatial queries. However, depending on the characteristics of your data, they may not be the optimal size. Tuning the grid sizes might result in better spatial query performance.

When you tune the spatial index, you must balance selectivity against reducing the number of entries in the spatial index. The greater the number of entries in the spatial index, the fewer features there will be in the result sets, which the secondary spatial filter must examine and possibly eliminate to meet the requirements of the spatial query. However, the more spatial index entries there are, the larger the spatial index will be, which will slow down queries on the spatial index table.

When performance tuning, the only way to tell if you have made a positive change is to monitor the results of each change. ArcSDE provides statistics about the spatial index which, along with performance testing, can ease the tuning process. The administration command sdelayer –o si_stats is the primary tool for reporting spatial index grid statistics used to tune the spatial index.

NOTE: Building a new spatial index for an ArcSDE feature class is a server-intensive operation; you should not do it for very large feature classes when a large number of users are logged in to the server.

How to tune a spatial index

The general steps to follow when tuning a spatial index are

1. Establish a repeatable test to measure spatial query performance. This may be a manual test or you might create an automated test that performs and times a defined set of spatial queries.
   


2. Use sdelayer –o si_stats to gather beginning spatial index statistics. Be sure to record the original size of the grid levels in case you decide to revert to them. You might decide to do this if you find the changes you made to the grid size cause spatial query performance to decline.
   

The syntax for sdelayer –o si_stats is



sdelayer –o si_stats –l <business_table,spatial_column> 
[–i <service>] [–s <server_name>] [–D <database>]
–u <DB_user_name> –p <DB_user_password> [–q]





NOTE: If you are not the owner of the table/feature class, you must qualify the table name as owner.table.



The following is an example of what gets returned by sdelayer –o si_stats:



Layer Administration Utility

Layer 5 Spatial Index Statistics:
Level 1, Grid Size 4.940464025

 Grid Records: 966                                                       
 Feature Records: 23                                                    
 Grids/Feature Ratio: 42                                                
 Avg. Features per Grid: 16.37                                       
 Max. Features per Grid: 96                                           
 % of Features Wholly Inside 1 Grid: 59.71                     

                   Spatial Index Record Count By Group          
Grids:        <=4    >4    >10    >25    >50   >100    >250   >500

Features:      23     0      0      0     0      0      0      0        
% Total:      100%    0%     0%     0%    0%     0%     0%     0%



The statistic Grids/Feature Ratio shows the ratio of the number of entries in the spatial index table versus the number of features in the feature class. Fewer entries in the spatial index table equate to faster queries. Optimally, the Grids/Feature Ratio should be less than 2. If it exceeds 4, consider modifying the spatial index settings. As you can see in the example above, with a Grids/Feature Ratio of 42, this spatial index needs modifying.

At the end of the output is a histogram showing how many spatial index records (entries) exist for each feature. The majority of the features should have fewer than four records. Even in a well-tuned spatial index, if there are a few features that are significantly larger than the others, these features will have more spatial index entries. If many features have more than four spatial index entries, consider modifying the spatial index.


3. Change the spatial index grid settings. You can use ArcCatalog or the sdelayer command to do this.
   
Learn how to modify the spatial index in ArcCatalog

To use sdelayer, do the following:


• Use sdelayer –o load_only_io to drop the spatial index. Switching the feature class to load-only mode drops the spatial index. While the feature class is in load-only mode, no spatial queries or data loading is allowed on this layer.
  



sdelayer –o load_only_io –l <table,column> 
[–i <service>] [–s <server_name>] [–D <database>]
[–u <DB_user_name>] [–p <DB_user_password>] [–q]




• Use sdelayer –o alter –g n,n,n to specify new grid sizes. Specify 0 for the second or third grid size if not used.
  



sdelayer –o alter –l <table,column> –g {<grid_sz1>[,<grid_sz2>[,<grid_sz3>]]}
[–S <layer_description_str>] [–i <service>] [–s <server_name>]
[–D <database>] –u <DB_User_name> [–p <DB_User_password>] [–N] [–q]




• Use sdelayer –o normal_io to rebuild the spatial index and make the layer accessible again.
  



sdelayer –o normal_io –l <table,column>
[–i <service>] [–s <server_name>] [–D <database>]
[–u <DB_user_name>] [–p <DB_user_password>] [–q]






4. Run your query performance test again, and check the spatial index statistics to see if the changes had the desired effect. If not, you could try again or you might choose to undo the changes, especially if they have a negative effect on performance. You would do this by repeating step 3 using the grid index sizes you recorded in step 2.
   


5. Repeat steps 3 and 4 until you run out of reasonable changes to make or you find that remaining changes have a negligible effect on performance.
   

Tips

A good starting place is to set the grid size to three times the length of the edge of an average-sized feature. Where possible, use only one spatial index grid level. Because it needs to search each level used in each feature class, ArcSDE usually performs best when a single spatial index grid level is used. However, feature classes with highly variable feature envelope sizes may benefit from multilevel spatial indexes. You may choose to experiment with a multilevel spatial index to see if it will improve the spatial index statistics and the query performance. Where possible, specify a grid size where a high percentage of features fall wholly within one grid cell. If the percentage falls below 80 percent, consider modifying the spatial index settings. Keep the average number of features per grid fairly low, between 100 and 300. Try to keep the maximum number of features per grid below 4,000. A spatial query that happens to include a grid cell with many associated features will return all those features to ArcSDE to be processed by the secondary spatial filter. For details on the use of sdelayer and other administration commands, consult the Administration Command Reference that is provided with the software. You can also download a PDF version of the file from the ESRI support site. (Login required) http://support.esri.com/index.cfm?fa=knowledgebase.documentation.listDocs&PID=19

Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.