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

635
12
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.

Reply
0 Kudos
12 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.

Reply
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

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

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

Reply
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? 

Reply
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

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

 

Reply
0 Kudos
TimMinter
Occasional Contributor III

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.

Reply
0 Kudos