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 · Accepted solution]

Query Layers and registered spatial views are very different creatures.  The registered views know the expected geometry type and expected data envelope due to metadata stored in the sde.sde_layers table.  This allows the Direct Connect DLL to avoid using a spatial query constraint if it would be inefficient (e.g., it wouldn't significantly reduce the number of features).

You've also had some significant trauma to your database due to the various upgrades.  Rebuilding all the indexes and all the index statistics would be a good course of action at this point.  If you have any large (250k+ row) tables that are performing poorly, you should consider spatially sorting them into a new table, and measuring for spatial query performance.

In the future, please use specific time intervals in performance questions (e.g "17.3 minutes"), and include the topology type (point/line/poly) and the number of rows involved.

- V

[Drew]

Thanks for the reply Vince.

The one key thing is that the spatial views work fine in a ArcMap 10.3  but slow in 10.4.

I am doing testing on a Polygon feature class (parcels to be specific) and it takes about 12 minutes to load into ArcMap in 10.4 and maybe 15 seconds in 10.3  --- using the exact same sql spatial view.  The parcels data is about 200,000 records.

Inside the view is a very simply join to ownership data.  I have seen this same issue on other sql spatial views with only 200 records.

Thanks,

Drew

[VinceAngelo]

But they're different instances, with different database releases (?? - this wan't clear), right?  Therefore 10.3/10.4 is not the only variable.

What tweaks were you making to change view performance?

In the end, you'll need to determine the optimizer query plan, and figure out why the query is processed so inefficiently.

- V

[Drew]

Sorry for the delayed reply. I just got caught up with other things.

They are the same Database instance just different ArcMap Clients.

10.4 = Slow

10.3 = Fast

Same data source, same query, same computer specs.

We have noticed this on many different SQL Spatial views.

We will be looking into it further in the coming days to see if we can see what is happening.

Once last note, the same issue appears in ArcGIS Server also. Our 10.4 services renders the data very slow where 10.3 is blazing.

[JoshuaBixby]

Are these spatial views being created new or were they created under <= 10.3.x and carried over?  If the latter, have you tried deleting the views and re-creating them natively in 10.4?

[MelissaJarman]

A new technical article has been created that sounds similar to this geonet thread - changes in the transaction model at 10.4 result in slower performance with joined views if the joined columns not being indexed properly.  

Problem: Performance of SQL Server views degrades after upgrading to ArcGIS 10.4 

[Anonymous User]

Hey Drew,

You mentioned that you also upgraded the geodatabase from 10.3 to 10.4. In SQL Server, this process should adjust the geodatabase properties to enable READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION, but if the user upgrading the geodatabase doesn't have the appropriate permissions it might not make that adjustment. Can you verify these two options are set to TRUE? See the link below for more information:

What's new in ArcMap—ArcGIS Help | ArcGIS for Desktop

Best,

Supriya

[SteveCaswell]

Hi,

Did you ever solve this? We have exactly the same thing happening here. SQL Spatial Views created in 10.3 are terribly slow in Arcmap 10.4, i.e. a spatial view loaded into Arcmap 10.3.1 takes around 20 seconds to load, the same view takes 19 minutes to load in ArcMap 10.4.

As a test, I've created a fresh SQL database using the tools in ArcGIS 10.4, imported only the couple of datasets involved in the spatial view and freshly created the spatial view in this new database. Exactly the same result, seconds to load in 10.3.1, minutes in 10.4.

Like Drew, our web services using these spatial views with ArcGIS Server 10.4 are so terribly slow I have had to remove the spatial views and use other datasets to keep them up and running.

Both old and new SQL databases are SQL Server 2012, both have READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION enabled.

Any advice?

Thank you.

Steve

[Drew]

Hello Steve,

We have not figured it out yet a full solution yet, but a patch is to actually paste the views SQL Code into ArcGMap as a Query Layer. It's messy but it keeps our AGS Services up and running without copies of the data.

I checked the settings that Supriya suggested and everything looked good. ArcMap is doing something.
We hope to open a ticket in the near future but just have not had the time to get a full demo up and running to send to ESRI.

I hope this helps in the short term.

Drew