Problem: Performance of SQL Server views degrades after upgrading to ArcGIS 10.4
After upgrading to ArcGIS for Desktop 10.4 or later, accessing views that join two or more tables is slower than at earlier releases.
With ArcGIS for Desktop version 10.4, there were substantial changes to the transaction model used by ArcGIS with SQL Server. This change included an update to the way data is fetched from the database to the client application. At version 10.3.1 and earlier releases, the entire query result set was pushed to the client in one operation. At version 10.4, data is fetched using a different database API call. In the case of views where multiple tables are joined, each fetch operation performs a full table scan if there is no index present on the join column. Multiple fetches incur multiple full-table scans.
Solution or Workaround
Ensure that the columns used in the joins are correctly indexed. While it is always beneficial to ensure that valid indexes are in place when joining tables, it is much more important to do this at version 10.4 than at earlier releases.