ArcGIS Query Layer performance documentation or feedback

Question

I first tried asking this question in the ArcGIS forums but as is common I got a total of 0 responses. I'm willing to accept this may be due to a lack of knowledge on the subject, but I'm optimistic. I will cross-pollinate responses or just delete the ArcGIS post if it remains untouched.

I've just started using Query Layers for read-only maps served through ArcGIS Server (10 SP2) reading data from Oracle 10g. I love it! I've always felt oddly uneasy about SDE and this seems a good sidestepping opportunity.

In my tests performance looks good but I would like to see some documentation (ideally) or other user feedback on Query Layer performance on live services or large datasets and how it compares to other DBMS options (SDE, SDE DC).

The only docs I've seen make a big deal out of the data being queried each time a user action occurs, but I would expect this to happen in all types of connections. Does SDE do some magical caching that a Query Layer doesn't? Does it better handle concurrent users in some way?

If anything I would expect Query Layers to improve performance at runtime (lower overhead) and reduce development / setup time (fewer SDE configuration headaches).

Any experience or informed thoughts on this would be very much appreciated. Please assume I am not concerned with versioning / multi-user editing, or any of SDE's other non performance-related benefits.

Answer 1

I have done some research and in my test cases it looks like ArcSDE has a slight performance edge over query layers which widens as the dataset grows.

I don't have exclusive use of the infrastructure so there are some unknowns here, but the tests were repeated a few times.

I created 50,000 random points in WGS84 Lat/Lon. I first created a query layer in ArcMap and set some non-default styling (semi-transparent star symbols). I exported the map to MSD and created a new map service with default configuration.

I then used Apache Bench (ab) to make 3 lots of 10 requests with concurrency of 2 for images covering the full or almost full dataset, then 3 lots of 20 requests with concurrency of 4 for images covering smaller areas of the data.

I then disabled the query layer map service and set up an SDE-based service, using the same styling rules, saving to MSD, and the same default map service configuration. The same number of tests were run with the same extents.

By comparing the mean average of each type of request's complete response time I see:

• ArcSDE is faster in 4 of the 6 tests
• Speed differences between the two approaches range from 3ms to 1300ms (for context the faster response times here are 5000ms and 7000ms respectively)
• ArcSDE's lead over query layers is larger for the full or near full extent map images (50,000 points), suggesting it better handles larger datasets

One limitation of my test is that ab is repeating the same request many times and with concurrent clients, so if one configuration is better than the other at generating the same images repeatedly it will perform better here, but this use case might not arise in real use.

Update I ran more tests against 100,000 points and SDE's lead widened. This time all 5 requests were on the full or near-full dataset, 10 requests with a concurrency of 2. Here are the mean total response times (ms)

Query Layer || ArcSDE

• 10080 || 8117
• 12438 || 8360
• 10232 || 8620
• 9291 || 8149
• 8105 || 5836

Interesting observation: The SDE-based service seems to require the WKT definition of the SRS as a URL parameter, whereas the Oracle-based query layer sends the Oracle SRID (4326). Presumably this means ArcGIS Server is asking Oracle for its WKT definition of 4326 - if this happens repeatedly it could help explain some of the differences.