Slow - 10.4 Client with Sql Spatial Views

[Drew]

Hello,

We have recently upgraded our ArcMap instances to 10.4 and are now having performance issues with our SQL Spatial Views.

Querying on the database side is fine, its when the views are loaded into ArcMap 10.4 when the slowness starts.  If I fiddle with the view I can make it perform better but its hit and miss. The 10.4 client is doing something because in 10.3 clients the views perform as expected.

Our SDE Geodatabase has also been upgraded to 10.4 and is running on SQL Server 2012 (64 bit)

One additional note: If I create the exact same view as a "Query Layer" in ArcMap the data performs as expected.  Its only when its compiled as a SQLSpatial View when the slowness starts.

Has anyone else had this problem or have a solution?

Drew

[VinceAngelo]

If you want to hear from Esri on a topic, you should create a Tech Support incident.  Second choice (but not nearly as effective) is to try to contact your local marketing rep, who has access to techincal resources for ad-hoc queries.

While plenty of Esri staff participate in GeoNet, very few are authorized to speak for Esri, so the moment you write "Would love to hear from Esri on this" you discourage any further participation from Esri staff (which probably wasn't your intent).

- V

[ChrisBeaudette]

Got it.  My expectations have been correspondingly lowered.  (Again)

[TedChapin]

Vince, your comments are contrary to what I've been hearing from Esri at DevSummit and geodev meetups. I hope (and think jbarry-esristaff‌ would agree) that asking for Esri employee feedback in this forum is acceptable and encouraged. A bug has already been referenced above, so obviously people are using tech support. This thread has been an invaluable supplement to a tech support incident I've logged.

BTW, there are other groups in Esri that use Geonet heavily.  For example, the Pro SDK team asked their users to post questions on their content area, and they respond daily. It's been a huge help both with and without an official tech support incident.

[VinceAngelo]

Employee feedback is different than corporate feedback. Asking for an "Esri" response in a place where those with authority to respond on behalf of Esri do not participate does not increase the response rate.  If the issue is a known bug without a resolution, there isn't much response that is possible.

GeoNet is not intended to be a replacement for Tech Support. If you contact Tech Support, Esri has a better idea of the number of folks with a problem, and can notify you directly if/when a hot-fix or other resolution is found.  If the bugfix does not resolve all issues, then this is something which should be caught earlier than later, and not participating in the process extends the time to response.  Using GeoNet as a supplement to bug reports is one of the main reasons that GeoNet exists.

- V

[TedChapin]

Hey c_r_b‌ and others experiencing this SQL view performance problem.  I had luck adding an index to the attribute involved in the JOIN in my query. So Chris, try indexing your LocationAddressID field. I have no idea why 10.3.1 performs the same with and without the index, but for me, adding the attribute index brought 10.4.1 and Pro 1.3.1 performance down to the same speed as 10.3.

[ChrisBeaudette]

Thanks Ted, good advice!  An index should have existed on that table already, but didn't.

Some interesting results this morning:

- Before adding the index, querying the layer took ~3.7s in the browser (it was unusually fast this morning for some reason)

- Added a unique, clustered index on Addresses.LocationAddressId (used in the WHERE clause):  query took 43s.

Completely counter-intuitive!

Restarted the GIS service:  4.6 minutes

Even less intuitive!

While I was at it, I added a non-unique, non-clustered index to Addresses.LocationPointId (used in the JOIN) and a unique, non-clustered index on Locations.LocationPointId (also used in the JOIN).  (Note that there was already a clustered index on OJBECTID in the Locations table, so I was forced to create a non-clustered index).  

Result (after restarting the map service):  4.5m 

Time spent in DB on "exec sp_cursorexecute ..." (the business end of the query with 554K reads): 381 ms

For the balance of the duration of the request, the database is executing "exec sp_cursorfetch" commands twice per second, e.g. "exec sp_cursorfetch 180150129,2,1,100", until the response is returned to the browser, at which point the "exec sp_cursorfetch" commands cease.

If I remove all indexes and restart the map service, a single request still took 4.5m. 

This is a development environment and I'm the only one hitting both the GIS server and the database.  A SQL Profile trace confirms this.

Note that my tests consist of panning a map service with a single query layer in it using the JS API in a Web browser.

I suspect yours and my situations are different for environmental reasons unknown.  Wish I could get this to work in a reliable manner, but it looks like my only option is to downgrade to AGS v10.3x.

[TedChapin]

How did you add the index? Directly in SQL Server or using ArcCatalog?

[ChrisBeaudette]

SQL

[TedChapin]

Try creating the index with ArcCatalog in the feature class properties Index tab. That's how I did it. Now I'm curious to look under the hood and see what kind of index that creates.

[ChrisBeaudette]

That helped -- when adding the indexes via ArcCatalog, all round-trip requests to/from the browser were about 4-8s, depending on the zoom level.  Remove the indexes, and requests took  4.5m again.  Add them back in (via ArcCatalog), and they returned to 4-8s.  Unfortunately that's still too slow for our purposes, especially when compared to v10.3 which is consistently about 2s regardless of scale.

Thanks Ted Chapin!  That's a good tip to know regarding adding indexes via ArcCatalog.  We've got scripts that use SQL to add indexes to feature classes -- we're going to have to modify them to use the ArcPy methods to do so.

Andrew Timmins:  If you used SQL and not ArcCatalog to create your indexes, try using ArcCatalog and see if you get different results.