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

2695
8
Jump to solution
03-27-2019 06:20 PM
SBerg_VHB
Occasional Contributor

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

0 Kudos
1 Solution

Accepted Solutions
TanuHoque
Esri Regular Contributor

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

View solution in original post

8 Replies
TanuHoque
Esri Regular Contributor

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
Occasional Contributor

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

0 Kudos
TanuHoque
Esri Regular Contributor

Hi Sam,

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

0 Kudos
MichaelMannion
New Contributor III

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.

0 Kudos
TanuHoque
Esri Regular Contributor

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

0 Kudos
CarlosSchonert_Martínez
Esri Contributor

Hi Tanu Hoque

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

Thank you!

0 Kudos
TanuHoque
Esri Regular Contributor

Carlos,

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

Sam Berg‌,

do you happen to have the bug id handy?

0 Kudos
AdamPodlewski
New Contributor

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.

0 Kudos