Hi - looking to diagnose very slow query performance against a multi-million row feature class dataset, we observed that an ArcGIS Server Map/Feature Service layer query looking only for record count and including a spatial geometry filter produces an underlying SQL call returning the entire set of OBJECTIDs as a result (taking 1 min+), and does not issue a SQL call using a SELECT COUNT (taking a few seconds directly in SSMS). It appears that ArcGIS Server is requesting fields from all records and doing the count itself. The result is that certain web GIS apps become unresponsive when generating a count of features within the bounding box.
A request similar to this:
https://gisservice/MapServer/0/query?where=1%3D1&returnCountOnly=true&geometry=-82.706%2C41.315%2C-61.799%2C44.139&geometryType=esriGeometryEnvelope&inSR=4326&spatialRel=esriSpatialRelIntersects
...results in a SQL query similar to this:
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varbinary(max)',N'select OBJECTID, SHAPE, GDB_GEOMATTR_DATA from GISDATA.TABLE where (1 = 1) AND TABLE.SHAPE.STIntersects (@P1) = 1',@p5 output,@p6 output,@p7 output,0x6A69000001040500000080CE88F2A07326C100FED418B5F42741C04603B86CD72E4100FED418B5F42741C04603B86CD72E41A0C64B27CDA9314180CE88F2A07326C1A0C64B27CDA9314180CE88F2A07326C100FED418B5F4274101000000020000000001000000FFFFFFFF0000000003
Can a basic alternative or workaround be suggested? This is AGS 10.61 and SQL Server 2016.
Thank you.
Sam
