topic Re: ArcGIS Server Record Count Query with Spatial Filter against large dataset issuing full table query in ArcGIS Enterprise Questions

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

SBerg_VHB — Thu, 28 Mar 2019 01:20:35 GMT

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

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

TanuHoque — Thu, 28 Mar 2019 16:46:45 GMT

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"   }   ]

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

SBerg_VHB — Thu, 28 Mar 2019 17:53:32 GMT

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

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

TanuHoque — Thu, 28 Mar 2019 17:57:17 GMT

Hi Sam,

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

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

MichaelMannion — Thu, 28 Mar 2019 18:13:25 GMT

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.

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

TanuHoque — Thu, 28 Mar 2019 18:17:23 GMT

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

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

CarlosSchonert_Martínez — Wed, 10 Jun 2020 09:05:42 GMT

Hi Tanu Hoque‌

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

Thank you!

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

TanuHoque — Wed, 10 Jun 2020 17:54:52 GMT

Carlos,

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

Sam Berg‌,

do you happen to have the bug id handy?

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

AdamPodlewski — Mon, 09 Aug 2021 15:06:15 GMT

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.