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

[Drew]

Just to add.. I tested our Views in the latest 10.5 release available on my.esri.com and the issue still exists.

I did try what Ted suggested about the index but no luck for us at this time. 

[ChrisBeaudette]

Sorry to hear this bug still exists at v10.5.  Looks like we're stuck at 10.3.1 for a while.  I've submitted a support request with the intention of getting this bug fixed.

[Drew]

I did try to create the index used in our JOIN though ArcCatalog today, there was no improvement for us.
Thanks for the tip though. 

[CraigSwadner]

are you still having performance issues ?

[Drew]

Well its been just about a year since I posted the original question and I think we have finally fixed our problem.

Our views were not the most simple and there was a lot of areas to explore but in the end it all came down to data types.

We had a mix of varchar and nvarchar in some of our tables. These columns participated in the particular joins we had issues with. Once we redefined the columns needed to be varchar things go fast again! Just like the old 10.3 days

This is a huge relief for us as we have been holding all our desktop users back on 10.3 because of this.

I hope this helps others out there,

Drew

[JustinConnerWR]

Is the specific relevant tech article for that solution Problem: Display performance of ArcSDE spatial views could be slow when the definition of join field... ?

[RuchiraWelikala]

Another way I was able to resolve this was using a versioned view as the left table (for a spatial view via a left outer join). The right table can be either view or standard table/feature class. Basically, the layer you're getting the geometry from has to be the versioned view. Even if it's not versioned, you can enable SQL Access through Catalog and still use the view. I think most newer versions of SDE automatically creates the view  now anyway.

[ChrisBeaudette]

Good to know, thanks rwelikala_toak‌.  The versioned view gets created automatically by SDE when versioning or enabling archiving for a feature class.  I don't think it gets created otherwise.

Regarding this issue, we're still at v10.3.1 and hoping Esri fixes it by v10.5.x since that's our planned upgrade path.

[RuchiraWelikala]

Unfortunately, I'm on 10.5.1 and it's still not fixed, hah. From what I understand, it's not a bug, so I don't know if it'll ever be "fixed". But the method of using the database/versioned view seems to work lightning quick.

[Drew]

Just in case you missed anything, did you check all your data types that are in your join and ensure they are the same data type?  Also, does copy and pasting the view as a Query Layer fix the issue too?