Should slow refresh rate be expected while viewing 1000 points out of 100 million using ArcSDE/Oracle?

Question

Is it normal to experience very slow refresh rate in ArcMap with 1000 points shown out of 100 million using ArcSDE/Oracle?

I am testing a setup with ArcSDE/Oracle database to contain geographical data. One table contains 100 million and another table contains 70,000 polygons.

The refresh rate while viewing the polygon table in ArcMap is fast but very slow for the point table. I do not expect to be able to look at all the 100 million points at the same time, but thought it should be possible to use the “definition query” option to only work with a subset of the data (for instance the points with id between 1-1000).

The definition query works fine and only the points with id between 1 and 1000 are shown on the map, but the refresh rate is extremely slow for some areas.

These areas are where points are located in the point table, as expected these points are not shown because of the definition query but then why is the refresh rate so slow when only 1000 points total are shown?

When zoom to layer is used refresh takes 1 sec at scale 1:30,000 and 4 min at 1:500.000 with the same data shown. See http://youtu.be/b-9ubqHy7nM .

The network activity shows that the local computer only receives data for areas where the points are shown and the CPU is not stressed either so it is not because the local computer are doing anything.

It seems almost like there is no spatial index or there is something wrong with it, but this is not the case. In Oracle’s SQL Developer spatial queries for points within a certain polygon and for a certain id works very fast.

Is it possible that a setting somewhere in the setup causes the poor refresh rate in ArcMap or is this normal behavior for tables containing this amount of points?

Versions in use: ArcGIS 10.1, Oracle 11g, I’m a little unclear about the version of ArcSDE but the describe function (arcpy.Describe(“DB_connection.sde").release) returns 3,0,0. Feature Class Properties->Index under spatial index shows this

Answer 1

Keep in mind what you are doing -- You are asking the database to return all features within the display envelope AND whose ids are in the first 1000. And you are likely doing so with a spatial constraint first option. Yes, it is very normal to see poor performance under such circumstances.

Best practice for rendering very large tables is not to. This can take many forms: You can set scale dependency so that the points are not rendered until the spatial index can assure good performance. You can cluster point features into multi-point geometries in large rectangles. You can use ATTRIBUTE_FIRST query option on tightly restricted attributes.

If you do need to spatially query a very large table, you should make sure that it is spatially defragmented (clone the table via an ORDER BY, either on spatial index cell or some other spatial attribute [e.g. COUNTY_CODE]), or you will get poor performance no matter what spatial index size (or RDBMS, or storage format) you choose.