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

Question

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?

Answer 1

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.)

Answer 2

Most likely, your spatial indexes are messed up. Drop and recreate them and see if you notice a difference.

Second thing I would try is using direct connect instead of a three-tier approach for arcsde. Why? Because three tier will be moving data around three processes instead of two.

Third thing I would try is removing the relationship classes and seeing if that makes a huge difference (most likely it really does). If that is the case, consider removing several of them first by denormalizing. Another option is to put indexes in the fields that ArcMap is querying (for sure the foreign keys which should have been done by ArcCatalog, but I would double check).

Finally, remember you can always use the standard SQL Server tools to see what queries are taking the longest, analize the cost of the query, and drop indexes on those fields.

Yes, extracting a smaller chunk of data, working with that and then resyncing would work, but at those small numbers you do not need to do that yet.

And to answer your question about querying the data once, well, a mapcache (aka editcache) is effectively that (querying the data and caching it all locally).Nevertheless, I would not use it with 9 million features because extracting and syncing would be a much better approach than that for several other reasons outside the scope of this question :)