topic Re: Slow - 10.4 Client with Sql Spatial Views in Geodatabase Questions

Slow - 10.4 Client with Sql Spatial Views

Drew — Fri, 15 Apr 2016 14:18:04 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

VinceAngelo — Fri, 15 Apr 2016 18:52:31 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

Drew — Fri, 15 Apr 2016 20:28:22 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

VinceAngelo — Sat, 16 Apr 2016 03:27:39 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

Drew — Tue, 10 May 2016 13:31:07 GMT

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.

Re: Slow - 10.4 Client with Sql Spatial Views

JoshuaBixby — Tue, 10 May 2016 14:35:34 GMT

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?

Re: Slow - 10.4 Client with Sql Spatial Views

Anonymous User — Thu, 12 May 2016 15:53:04 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

SteveCaswell — Fri, 17 Jun 2016 02:18:40 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

Drew — Fri, 17 Jun 2016 13:04:56 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

joonpark — Fri, 12 Aug 2016 17:47:15 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

Anonymous User — Mon, 22 Aug 2016 21:19:39 GMT

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

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

Re: Slow - 10.4 Client with Sql Spatial Views

JoshuaBixby — Tue, 23 Aug 2016 12:27:06 GMT

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.

Re: Slow - 10.4 Client with Sql Spatial Views

ChelseaRozek — Thu, 27 Oct 2016 16:39:57 GMT

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."

Re: Slow - 10.4 Client with Sql Spatial Views

TedChapin — Thu, 10 Nov 2016 21:20:20 GMT

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.

Re: Slow - 10.4 Client with Sql Spatial Views

JoshuaBixby — Fri, 11 Nov 2016 15:26:16 GMT

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.

Re: Slow - 10.4 Client with Sql Spatial Views

Drew — Fri, 11 Nov 2016 16:35:00 GMT

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.

Re: Slow - 10.4 Client with Sql Spatial Views

ChrisBeaudette — Sat, 12 Nov 2016 02:37:32 GMT

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.

Re: Slow - 10.4 Client with Sql Spatial Views

VinceAngelo — Wed, 16 Nov 2016 15:31:48 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

ChrisBeaudette — Wed, 16 Nov 2016 16:34:55 GMT

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

Re: Slow - 10.4 Client with Sql Spatial Views

TedChapin — Wed, 16 Nov 2016 16:54:27 GMT

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.