ArcGIS Server Record Count Query with Spatial Filter against large dataset issuing full table query

[SBerg_VHB]

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-6... 

...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

[TanuHoque · Accepted solution]

unfortunately that is a know issue. 

can you pls use outStatistics parameter of a query operation? that should return count result faster.

[   {     "statisticType": "count",     "onStatisticField": "objectid",      "outStatisticFieldName": "count_objectid"   }   ]

[SBerg_VHB]

Thank you Tanu.  In this case, the client application is an ArcGIS Open Data site so we can't ourselves change the underlying query.  I have put in an enhancement request for this item to Esri.  Thank for you for the help and for verifying our finding on this.

Sam

[TanuHoque]

Hi Sam,

I see what you mean... hope this known issue on the server side will be resolved soon (finger crossed)

[MichaelMannion]

Tanu - If you're able to share, is there a specific timeline / target release for a fix to the original query?returnCountOnly=true issue? Thanks.

[TanuHoque]

Unfortunately I don't have any at this point  sorry about that.

[CarlosSchonert_Martínez]

Hi Tanu Hoque‌

I understand there is an ENH created for this behavior. Could you share the enhancement code with me?

Thank you!

[TanuHoque]

Carlos,

I can't seem to find the external facing bug id/link.

Sam Berg‌,

do you happen to have the bug id handy?

[AdamPodlewski]

Hi, TanuHoque

Since we are facing the same problem using the build-in query widget in Web App Builder - where exactly can we modify the code of this widget to force outStatistics to be used?

Regards.