We are running into a very similar situation with ArcGIS Server v10.4.1: published map services in v10.4.1 with a query layer are extremely slow so as to be unusable, whereas the same MXD published to a v10.3.1 AGS instance -- using the same data layers in the same SDE instance (v10.2.2 on SQL Server 2014) -- perform as expected.
When using the AGS JS API to browse the map services (pan, zoom, etc), each containing the same, single query layer, the following browse times are observed in Chrome Dev tools and SQL Server Profiler:
- v10.4.1:
o time for round trip in browser: 4m 36s
o duration of time in database query: 349ms
o number of database reads: 559,555
- v10.3.1:
o time for round trip in browser: 3.2s
o duration of time in database query: 2.02s
o number of database reads: 830,292
Ignoring the differences (by a factor of 6) for the duration of the database query (since the db times are a small fraction of the total time w/ v10.4.1), it's pretty obvious that the bulk of the time of the request to v10.4.1 is spent in the AGS tier. At the time of the request, CPU and memory on the GIS server were not under strain and were not variable.
In other map services that had multiple layers, when one of them was a query layer, the map services were equally slow, as were ALL map services on the v10.4.1 instance at the time the map service with the query layer was running. When the query layer was removed from the map service, performance returned to normal.
The query itself references tables all within the same database and runs in 4s in SSMS for all features:
SELECT Addresses.[LocationAddressID]
, Addresses.[HouseNumberStreetNameFull]
, Addresses.[ParcelID]
, Locations.LocationPointID
, Locations.Shape
FROM [GIS].[Addresses_All] Addresses
INNER JOIN [GIS].[LOCATIONPOINT] Locations
ON Locations.LocationPointID = Addresses.LocationPointID
WHERE Addresses.[LocationAddressID] IN
(SELECT MIN(LocationAddressID) FROM [GIS].[Addresses_All] GROUP BY LocationPointID)
The noted bug (BUG-000097186) refers only to ArcGIS Desktop -- not ArcGIS Server.
ESRI: Is this also a known issue with ArcGIS Server? This should be simple to reproduce -- have you been able to do so? Other than downgrading to v10.3, what are my options?