topic Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views in ArcGIS Pro Questions

ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

JohnFix1 — Sun, 16 Aug 2020 18:21:08 GMT

I was using ArcGIS Pro 2.5x and prior with a SQL Server 2017 database connection (on my local machine). It is NOT a registered database, but always performed well.

I just upgrade to ArcGIS Pro 2.6 and now it takes 45 minutes plus to open my project file. I have isolated the project to a SQL Server Spatial view table. I can load tables from the database without problems. But if I try to add the "view" to my project, ArcGIS Pro just sits and spins. After about 45 minutes or more, it does load the data. Once loaded, it performs fine.

This certainly is a change from ArcGIS Pro versions prior to 2.6.

I should note, I determined I have both the Microsoft ODBC drivers 13 and 17 installed. I think "17" was installed for ArcGIS, but "13" was installed when SQL Server 2017 was installed.

Any one else having this problem? Any thoughts on a solution?

Thanks.

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

JohnFix1 — Sat, 11 Dec 2021 16:00:54 GMT

Just adding some info to the above, in SSMS I was able to see the query that is running....ArcGIS Pro 2.6 is trying to determine the bounding box of the table. The query is:

SELECT min(((GEOM.STEnvelope()).STPointN(1)).STX),min(((GEOM.STEnvelope()).STPointN(1)).STY),max(((GEOM.STEnvelope()).STPointN(3)).STX),max(((GEOM.STEnvelope()).STPointN(3)).STY) 
FROM MYDB.DBO.ADDRESS_GEOCODE_VW‍‍‍

"MYDB" is just a placeholder for my actual DB name.

"ADDRESS_GEOCODE_VW" is a view.

The table has 9.7 million rows and this query has been running for 4 hours at this point.

When the base table is added to a map in Pro, it's very fast and renders as fast as I can drag the map.

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

JohnFix1 — Mon, 17 Aug 2020 00:03:04 GMT

Last post for now.... The active query running that is taking FOREVER is:

SELECT top 1 GEOM FROM MYDB.DBO.ADDRESS_GEOCODE_VW WHERE GEOM IS NOT NULL

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

George_Thompson — Mon, 17 Aug 2020 11:13:08 GMT

Adding another space for more eyes: Geodatabase‌

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

ChetDobbins — Mon, 24 Aug 2020 18:52:04 GMT

Hi John,

Have you opened a technical support case for this?

That may help us identify if this is something specific to your data and be able to troubleshoot further.

If you have not or can not open one please let me know.

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

ThomasColson — Tue, 25 Aug 2020 13:02:04 GMT

We have noticed that 2.6 RC is slower in everything: opening projects, loading SDE data, feature service data, can't even take a map offline, reproducible on very many workstations, doesn't matter what type, size, or format of data, the ones that are still 2.5.1 do not exhibit this behavior.

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

WilliamRice — Sun, 30 Aug 2020 22:53:06 GMT

John, what is the performance of the same spatial view with ArcGIS Desktop? Also, what spatial type is being used in your Enterprise Geodatabase to store the geometry ? SDEBINARY or one of the new Geometry formats?

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

TimMinter — Tue, 05 Jan 2021 16:15:04 GMT

Hi John, et al,

We noticed this problem when we upgraded from ArcGIS Pro 2.6.2 to 2.6.3. It remains a problem at 2.7. We currently have a SQL Server 2016 (13.0.5102.14) database with a view that returns 501 records in less than a second (edit: in SSMS) with ObjectID, Shape (geography, null), and other columns. ArcGIS Pro 2.6.2 could read and load the view in a few seconds. ArcGIS Pro 2.6.3 and up cannot even get the properties for the view in less than 10 minutes. Rather than expending noticeable resources with a support request, our strategy is generally to use ArcGIS Pro "whatever version works" for the particular function that we need to complete. So, in this case, we just read our view with ArcGIS Pro 2.6.2.

It would be great if Esri staff could notice this kind of discussion in the forum and handle bug evaluations internally rather than requiring Esri customers to spend significant amounts of our time to reproduce and enter into the bug log. It would be even greater if all the testing harnesses and holistic quality approaches could catch these sorts of things.

tim

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

TimMinter — Wed, 06 Jan 2021 16:42:41 GMT

Hmm. Well, I just typed up a rather detailed "me too" note on this item which has gone poof. The short of it is, "me too" with 2.6.3 and 2.7 in SQL Server 2016 eGDB 10.8.1.2.6. Result - use 2.6.2 and don't spend time with support request.

I was stepping back in to add that SQL Server Profiler shows ArcGIS Pro issuing this query:

SELECT top 1 SHAPE FROM <view> WHERE SHAPE IS NOT NULL AND SHAPE.STIsValid() = 1

When I issue that query in SSMS, it takes 1m 1s to return one record out of 501 total that the view returns in less than 1s. SQL Server Profiler indicates that ArcGIS Pro issues the query over and over and over and over again. Might be a thing to look at.

tim

edit - it looks like my detailed post de-poofed. great.

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

TimMinter — Fri, 15 Jan 2021 17:44:15 GMT

Update - well... had to open a support case with Esri. At present, it appears to be headed toward bug status. Because a support request can consume significant customer staff time, I can share the case number in case you need to go down this path. If it reaches bug status, I'll post a link, if possible.

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

AliceRobinson — Wed, 27 Jan 2021 17:14:50 GMT

Is SQL Server on a Virtual Machine? Specs?

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

TimMinter — Wed, 27 Jan 2021 19:55:34 GMT

Hi Alice - In my testing scenario, SQL Server is on a PM - Dell Precision 7530, i9, 32 GB RAM at 2933 MHz, 2 SSDs, which turns out to be pretty zappity and not prone to machinery-borne pokiness. The Esri support case is still open, and seems to be moving toward bug status this morning after the tech was able to reproduce the problem. A key finding at the moment is that when I write a view that delivers lat/long columns as SQL Server Spatial geography data type, the slowness occurs with ArcGIS Pro 2.6.3 and up. All remains fast with ArcGIS Pro 2.6.2 and down and also with ArcMap 10.8.1 and down. The problem does not currently seem to be about SQL Server, my SQL view writing skillz (which certainly could improve), or the device.

cheers,

tim

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

IngridMans — Wed, 17 Feb 2021 16:49:58 GMT

I'd just like to throw my hat in to say I'm having the same issues. It's painful after upgrading to 2.7. Even with one layer turned on that's using a definition query (sourced from an SDE enterprise database, version 10.7.1), any changes I try to make are painfully slow.

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

TimMinter — Thu, 04 Mar 2021 16:25:16 GMT

Well, after expending notable resources with a support request, the result is:

#BUG-000137891 On ArcGIS Pro 2.6.3 and above, slowness in display of ‘Properties’ of spatial views is experienced when definition of the query consists of CAST function, CASE function and storage type GEOGRAPHY

Which may or may not become available here at some point: https://support.esri.com/en/Search-Results#search?q=BUG-000137891&content-type=Bugs

The technical support analyst determined that removing any one of the CAPS elements above made things speed up.

So, it feels like there's a slim chance that a temporary workaround might exist which would require the application of Rube Goldberg design principles. Maybe splitting into views on views, although query optimizer might hand the same thing to ArcGIS Pro which might continue slowly. Maybe make a few steps to persist a table that ArcGIS Pro sees? Idk.

cheers & back to work,

tim

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

NicholasKeller2 — Wed, 02 Nov 2022 18:41:11 GMT

Is this bug fixed yet? Cause I seem to be experiencing similar issues on 3.0.2...

Re: ArcGIS Pro 2.6, very slow to load SQL Server 2017 Views

TimMinter — Thu, 03 Nov 2022 14:55:36 GMT

That's a good question. I'm not sure. If I recall, I think I registered the view with the geodatabase, which took forever, got it to work a little less slowly, handed it off to a workmate, and quietly slunk away. I'll keep an eye on Teams to see if my workmate starts grumbling on Tuesday morning, which is when supervised automation occurs for that particular data collection. All was quiet and successful this past Tuesday morn... hmm. Um. Well, we're at AGP 2.9.5, holding off on 3.x until some dust settles. Thanks for the report. We'll have a look after 3.1 releases. Also, this pattern with Esri's software has become known as "feature flicker" around our shop. A feature works properly at one release, then is borked at the subsequent release. Fairly annoying.

tim