slow count before feature layer loading

[SCS_ANST]

Hi there.

We used Mapimage Layer so far. Now we tried to use Feature Layer instead, but we have an issue with the count request, which seems to be automatically generated from ESRI with followed Query Parameters:

• f: json
• returnIdsOnly: true
• returnCountOnly: true
• spatialRel: esriSpatialRelIntersects
• where: 1=1 AND (FIELD_ONE = 1) AND (FIELD_TWO= 1)

So the result of the count is 309'947 and the request takes about 12 secondes.

This Request is made everytime first, before the important requests goes out with the pbf results.

So for 12 seconds, the user is waiting.

Our Service is based on a referenced data store to a View on an Oracle database.

We analyzed the query from ESRI on Oracle: 

"SELECT SG_POINT, ID
FROM (SELECT * FROM VIEWNAME) a
WHERE (1 = 1 AND (FIELD_ONE = 1) AND (FIELD_TWO= 1))"

So it's clear that it's slow, when ESRI takes all the spatial objects instead of a select count(*).?!?

Now the question is: can we deactivate this automated count or is there another solution/workaround? For me this seems to be a bug or a bad solution because of two points:

• no geometry filter in count request
• get the whole data from origin data source (oracle db), before count is made by esri, instead of request count directly from data source

JavaScript API 4.22

ArcGIS Enterprise 10.8.1

[SCS_ANST · Accepted solution]

Seems that this issue is only on Feature Layer on MapService. If I publish the Service also with Feature Access (without possibility to edit) and reference in the url to *featureserver* instead of *mapserver* the count request takes only a few ms?!? Same access, same data.

So there is definitely a difference between featurelayer on mapserver and featurelayer on featureserver. With this change my problem with the long during count request seems to be solved.

[mgeorge]

Hi @SCS_ANST. 

Thanks for the report! Yes, we're aware that some configurations have very slow count queries, and there's some alternative count querying APIs (for approx count) that we are looking at potentially exploring for 4.25. 

Does your definitionExpression for the layer in question include that 1=1? I don't think the API should be generating that unless there is no definitionExpression set on the layer. Talking to the server folks, it sounds like that can degrade performance for dbs against mapserver. 

[SCS_ANST]

Hi @mgeorge 

thanks for your reply. The 1=1 is part of our definitionExpression. As this is dynamic regarding filters set by the users, we add different where clause with and. Therefore we start always with 1=1. Yes if the user is setting no other filters, it will take all from the view, but always with the sdo_geometry filter, which reduce the transmitted data to the extend.

The Select itself is very optimized and fast. We check this always on database side.

But when the sdo_geometry filter is missing, like it is with this count on mapserver, of course it's very slow, as it will receive the whole geometry from the whole view without further limitations. I think with featureserver there is made only a select count(*) to the database because it's very fast.