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

933
13
08-16-2020 11:21 AM
JohnFix1
New Contributor III

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.

0 Kudos
13 Replies
JohnFix1
New Contributor III

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.

0 Kudos
JohnFix1
New Contributor III

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

0 Kudos
ChetDobbins
Esri Contributor

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.

0 Kudos
TimMinter
Occasional Contributor III

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

0 Kudos
George_Thompson
Esri Frequent Contributor

Adding another space for more eyes: Geodatabase

--- George T.
ThomasColson
MVP Frequent Contributor

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. 

0 Kudos
WilliamRice
Occasional Contributor II

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? 

0 Kudos
TimMinter
Occasional Contributor III

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

0 Kudos
TimMinter
Occasional Contributor III

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.

 

0 Kudos