[GIS] Adding data from a SQL Server Spatial database to ArcMap or ArcEngine application with ArcObjects for better performance

arcobjectsenterprise-geodatabaselarge datasetsquery-layersql server

I have an ArcSDE geodatabase stored in MS SQL Server 2008 R2. The datasets in this geodatabase range from 10 record to 7 million records and there is a mixture of tables and spatial datasets (SQL Geometry). All datasets are currently related via relationship classes.

It is nearly impossible to work with this data in ArcMap or via ArcObjects because the datasets are so large. ArcMap hangs when querying or getting related records. I tried query layers in ArcGIS 10 that query the SQL tables directly, but because these requery the database with every re-draw of the map or identify or attribute table change, the performance was still extremely slow.

Is there a way to query the SQL tables directly once and load the data into ArcMap as a layer?

Best Answer

7 million is not that big a number of records. The relationship classes are probably what are killing you. I would suggest two things. 1) For editing, use small area child replicas. This will greatly reduce the number of records needed while still giving you the capabilities you need from your relationship classes. 2) For viewing, start with a non-versioned read-only replica for viewing only. If that still is too poor of performance, then switch to a denormalized view. If this is a regularly edited database, the denormalized view will be a little trickier to maintain, but you will have dramatically improved performance at the cost of space. You might even be able to do spatially limited view only replicas like I recommended for editing, depending on the use pattern for your database. (Also, make sure your view-only clients are using feature caching.)

Related Solutions

[GIS] Adding rows of data to a non-spatial table in ArcMap with a SDE SQL Server for Metadata purposes

Create a stand alone table in your database through a connection in ArcCatalog so that the table will be registered with the database. Then assign privleges to the table and register as versioned through ArcCatalog. You should be able to add it to ArcMap and edit it then.

[GIS] Create Auto-Incremented Field within SDE Feature Class (SQL Server)

This is how I've done it in the past. Not sure how long the command line sdelayer will be around though.

In SQL Server:

CREATE TABLE myFC (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    OtherData VARCHAR(10),
    SHAPE Geometry -- Could be a Geography
);
CREATE SPATIAL INDEX myFC_sdx ON myFC (SHAPE) -- Make your own spatial index
USING GEOMETRY_GRID
WITH (
    BOUNDING_BOX =(0, 0, 100000, 100000) --Use some sensible bounds
    ,GRIDS=(HIGH, HIGH, MEDIUM, MEDIUM)
    ,CELLS_PER_OBJECT=100
);

From a command line:

sdelayer -o register -l myFC,SHAPE -C ID,USER ... connection information, etc

SDELayer reference

Note: When you export this to another geodatabase these IDs will not increment there. I am assuming that you are talking more about publishing rather than distributed editing though.

This will not work with Feature Classes registered as versioned.

Best Answer

Related Solutions

[GIS] Adding rows of data to a non-spatial table in ArcMap with a SDE SQL Server for Metadata purposes

[GIS] Create Auto-Incremented Field within SDE Feature Class (SQL Server)

Related Question