Slow - 10.4 Client with Sql Spatial Views

Drew · ‎04-15-2016

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

joonpark · ‎08-12-2016

Lately I've upgraded from ArcMap 10.2 to ArcMap 10.4 and have similar with "Add Layer" from layer file performance for SQL server. It got 5 times slower.

- I thought this was ArcObject issue but it's ArcMap itself. It seems it try to make new reconnect to SQL Server whenever reload same layer in same session. Something got worse and waiting response from ESRI.

If there is more information about this , please let me know .

Thanks guys

Anonymous User · ‎08-22-2016

This issue might be related to the following bug that was recently logged:

BUG-000097186: Data displays slower in ArcGIS 10.4 for Desktop..

JoshuaBixby · ‎08-23-2016

Thanks for the reference and link, it is always helpful. Given the bug is still "New" after 2+ months, it appears an Esri Development team hasn't even reviewed it yet. My guess, even if the root cause is found for 10.4.x, a patch won't be released. Think about installing 10.5 when it gets released.

TedChapin · ‎11-10-2016

I have a support case connected to this bug as well. Getting in on this thread to keep up with any developments. Some SQL Server views work fine in 10.3, slow in 10.4.1, Pro 1.3.1 (some views are find in both, it depends on what they're doing). We did a lot of database design and even custom Pro SDK development around these views, so they're critical to us.

MartinKrál · ‎02-02-2017

Hi.

Our customer had some simillar issue.
By tracing display of same feature class (2.8M points) at ArcMAP 10.2.1 (display~40 seconds)/10.4.1 (display~100 seconds) we found that our problem is in fetching records from cursor.(same as is writen BUG-000097186: Data displays slower in ArcGIS 10.4 for Desktop)

ArcMAP 10.4.1 simply fetches records in many 100 records buffers. (10.2.1 fetches everything at once)

Trying same approach as 10.4.1 with just one call of exec sp_cursorfetch with total number of feature class records in SSMS we get to time 50 seconds (which would be fine) .
By setting SDE_SERVER_CONFIG parameter MAXARRAYSIZE we were able to change size of record buffer from 100 to just 250 (setting higher had no effect) so the time of display improved slightly to aprox 75 seconds.
Unfortunately from esri support came, that they do not know how to sett it (for further testing) any higher then those 250.
Anyway I am alo convinced that setting it very high would help, but should be then thoroughly tested (it could also have some negative side effects by my opinion)

Regards

ChelseaRozek · ‎10-27-2016

This may be related, but I'm unable to publish a spatial view (from SQL server 2012) to our 10.4.1 ArcGIS Server. I can view it fine in ArcMap 10.4.1, and it publishes to our 10.3.1 ArcGIS Server without problems. The error I get when publishing to 10.4 is "Packaging succeeded, but publishing failed. ERROR 001487: Failed to update the published service with the server-side data location."

ChrisBeaudette · ‎11-10-2016

We are running into a very similar situation with ArcGIS Server v10.4.1: published map services in v10.4.1 with a query layer are extremely slow so as to be unusable, whereas the same MXD published to a v10.3.1 AGS instance -- using the same data layers in the same SDE instance (v10.2.2 on SQL Server 2014) -- perform as expected.

When using the AGS JS API to browse the map services (pan, zoom, etc), each containing the same, single query layer, the following browse times are observed in Chrome Dev tools and SQL Server Profiler:

- v10.4.1:
o time for round trip in browser: 4m 36s
o duration of time in database query: 349ms
o number of database reads: 559,555

- v10.3.1:
o time for round trip in browser: 3.2s
o duration of time in database query: 2.02s
o number of database reads: 830,292

Ignoring the differences (by a factor of 6) for the duration of the database query (since the db times are a small fraction of the total time w/ v10.4.1), it's pretty obvious that the bulk of the time of the request to v10.4.1 is spent in the AGS tier. At the time of the request, CPU and memory on the GIS server were not under strain and were not variable.

In other map services that had multiple layers, when one of them was a query layer, the map services were equally slow, as were ALL map services on the v10.4.1 instance at the time the map service with the query layer was running. When the query layer was removed from the map service, performance returned to normal.

The query itself references tables all within the same database and runs in 4s in SSMS for all features:

SELECT Addresses.[LocationAddressID]
 , Addresses.[HouseNumberStreetNameFull]
 , Addresses.[ParcelID]
 , Locations.LocationPointID
 , Locations.Shape
 FROM [GIS].[Addresses_All] Addresses
 INNER JOIN [GIS].[LOCATIONPOINT] Locations
 ON Locations.LocationPointID = Addresses.LocationPointID
 WHERE Addresses.[LocationAddressID] IN 
 (SELECT MIN(LocationAddressID) FROM [GIS].[Addresses_All] GROUP BY LocationPointID)

The noted bug (BUG-000097186) refers only to ArcGIS Desktop -- not ArcGIS Server.

ESRI: Is this also a known issue with ArcGIS Server? This should be simple to reproduce -- have you been able to do so? Other than downgrading to v10.3, what are my options?

JoshuaBixby · ‎11-11-2016

What patches have you applied to your 10.4.1 server? Although the ArcGIS for Server Publishing Patch doesn't seem to address thi issue directly, I wonder if it does address it nonetheless.

Drew · ‎11-11-2016

We tried this patch.. nothing changed.

Our only fix is to use the Spatial View SQL code as the query layer instead of referencing the view itself.

ChrisBeaudette · ‎11-11-2016

Thanks for the reply Drew.

We actually already tried using the SQL from the spatial view in our query layer -- that's the code included in my post above. Before we did that, our query layer was (effectively) a simple 'SELECT * FROM OUR_SPATIAL_VIEW', and OUR_SPATIAL_VIEW had the SQL code above. Pulling the SQL into the query layer seemed to help at first, but subsequently performed poorly for us. The performance stats noted above were using the "full" SQL in our query layer.

Would love to hear from Esri on this.