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

[RuchiraWelikala]

They were both NVARCHAR. However, one had a field length of 30, and the other had a field length of 37. Initially before trying the versioned view method, I tried analyzing the tables as well as rebuilding indexes. For my spatial view using the versioned view, I kept the query the same as before (i created it using SSMS) but just swapped out the table name to reference the view rather than the base tables. Worked like a charm. The right table is a base table and not a view. 

[MichelleBoivin]

I was having the same exact problem in 10.5, SQL 2016 and it was giving me a headache. Didn't even think to look at the data types. This made a HUGE difference - nightmare over! Thanks for posting!

[ThomasColson]

I've had great luck with https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-indexed-views-the-basics/ in increasing view performance

[MichelleBoivin]

Thanks Thomas, I'll take a look into that! Looks like the feedback on it was very positive. Appreciate the suggestion!