topic Re: SelectFeatures query slow when using only ObjectIds param in ArcGIS JavaScript Maps SDK Questions

SelectFeatures query slow when using only ObjectIds param

DianaBenedict — Thu, 21 Feb 2013 16:20:33 GMT

I have recently found that when I attempt to use the following query to selectFeatures from a FeatureLayer the response is significantly slower if I use only the query.objectIds versus the query.where. Below are the two samples:

//The query below runs much faster when using a where clause
var selQuery = new esri.tasks.Query();
//selQuery.where = "OBJECTID = " + [addResults[0].objectId];
selQuery.objectIds = [addResults[0].objectId];

//The query below takes about 40 seconds to run when using only the objectIds.
var selQuery = new esri.tasks.Query();
//selQuery.where = "OBJECTID = " + [addResults[0].objectId];
selQuery.objectIds = [addResults[0].objectId];

Essentially, I have to call FeatureLayer.selectFeatures after a new feature is created so that I can show the custom attribute dialog to the user. The applyEdits addResults returns only the ObjectID and GlobalId along with a success (true/false). I have been able to wire all this successfully. I was just hoping to move away from having to deal with a where clause and string manipulation vs just using an array of objectIds.

It just seems interesting to me that the query.objectIds ONLY takes so long to return the FeatureSet. (Note the data that I am dealing with is very large .. national scale).

Has anyone else noticed this. ESRI, is this a known issue or am I implemting the query.objectIds incorrectly?

Thanks

Re: SelectFeatures query slow when using only ObjectIds param

DianaBenedict — Thu, 25 Apr 2013 14:37:47 GMT

All

Sorry to bring this issue up again but it looks like there is a SIGNIFICANT performance issue when doing a query using only objectIds. I am working with national data (therefore I have lots of data to deal with). I have noticed that when I do a query that only uses the objectIds paramater ( query.objectIds = [12345,12346]; ) , the performance is REALLY slow, in my data it takes it somewhere between 6-8 seconds to return results. However, when I use a where clause (query.where = "OBJECTID in (12345,12346)" ) it retruns my results within a second. I would say that is a significant difference!

Has anyone else encountered this issue? Is it only apparent when we are dealing with large datasets? It seems to me like it is doing a complete table scan instead of using the "ObjectID" index.

Thanks for listening ....

Oh, I forgot to mention that I am using the Javascript 3.3 API. I will be upgrading soon but just haven't had the time to deal with that yet.

Re: SelectFeatures query slow when using only ObjectIds param

derekswingley1 — Thu, 25 Apr 2013 15:11:05 GMT

Can you reproduce this using the REST endpoint for your service or is it only slow when querying via a Feature Layer?

What I mean by querying using the REST endpoint is to go to the URL for your layer, I'll use this one as an example: http://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer/3

And do your queries there.

Query using a where clause:

http://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer/3/query?where=OBJECTID+IN+%283%2C+4%29&text=&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&outFields=&returnGeometry=false&maxAllowableOffset=&geometryPrecision=&outSR=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&returnDistinctValues=false&f=html

Query using object IDs:

http://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer/3/query?where=&text=&objectIds=3%2C+4&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&outFields=&returnGeometry=false&maxAllowableOffset=&geometryPrecision=&outSR=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&returnDistinctValues=false&f=html

Re: SelectFeatures query slow when using only ObjectIds param

DianaBenedict — Thu, 25 Apr 2013 15:49:22 GMT

Thanks for getting back to me so soon. I just used the the following rest endpoints; I have listed the results below:

FeatureService.query
http://myserver:6080/arcgis/rest/services/FeatureServices/LandformVectorToolEditService/FeatureServer/2/query
- using ObjectIds (10-13 seconds):
- using where clause (less than 1 second)

MapService.Query
http://myserver:6080/arcgis/rest/services/FeatureServices/LandformVectorToolEditService/MapServer/2/query?where=OBJECTID+%3D+50669457&text=&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&outFields=&returnGeometry=true&maxAllowableOffset=&geometryPrecision=&outSR=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&returnDistinctValues=false&f=pjson

-Where clause < 1second

http://myserver:6080/arcgis/rest/services/FeatureServices/LandformVectorToolEditService/MapServer/2/query?where=&text=&objectIds=50669457&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&outFields=&returnGeometry=true&maxAllowableOffset=&geometryPrecision=&outSR=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&returnDistinctValues=false&f=pjson

-ObjectIds 10-11 seconds

Again, I am dealing with National Data.

sorry I can't point you to my actual data .. it is behind a firewall.

Re: SelectFeatures query slow when using only ObjectIds param

derekswingley1 — Thu, 25 Apr 2013 16:31:00 GMT

Since you can see similar performance issues at the REST tier, that indicates that the performance problem is in something ArcGIS Server is doing or, more likely, that there's a performance issue on the database side, which is a whole other world of troubleshooting.

I would go with what's fast: use query.where as opposed to query.objectIds. Any reason you can't do that?

Re: SelectFeatures query slow when using only ObjectIds param

DianaBenedict — Thu, 25 Apr 2013 17:15:25 GMT

Since you can see similar performance issues at the REST tier, that indicates that the performance problem is in something ArcGIS Server is doing or, more likely, that there's a performance issue on the database side, which is a whole other world of troubleshooting.

I would go with what's fast: use query.where as opposed to query.objectIds. Any reason you can't do that?

Well, yes I can do that but it is not as clean...

It seems to me like ESRI REST API included the query by ObjectIds to make the functionality easier; especially when dealing with the editing enviroment and with OnSelection feature services. For example, during ApplyEdits the onEditsComplete(addResults, updateResults, deleteResults) event returns an array of FeatureEditResult object. This object not only indicates if the edit was successful, but it also returns the ObjectId. This is mostly important when dealing with new features that are created and the ability to get back the feature from the service. With that said, it makes it easier to simply get back your records using the array of objectIDs. It will require more code writing and "assumptions" to have to generate the where clause every single time.

So to recap, this appears to be an ESRI bug. Is it not better for ESRI to address this issue? In the meantime, I will have to "play around" with the code to account for this "feature" ... or there lack of.

Regards

Re: SelectFeatures query slow when using only ObjectIds param

derekswingley1 — Thu, 25 Apr 2013 22:05:25 GMT

It will require more code writing and "assumptions" to have to generate the where clause every single time.

How is generating a where clause considerably more effort than generating an array of objectIds? Whether it's a where clause (a string) or an array of numbers, you're setting a property on a query object. Building a comma separated string is slightly more effort, but it's minimal.

So to recap, this appears to be an ESRI bug. Is it not better for ESRI to address this issue? In the meantime, I will have to "play around" with the code to account for this "feature" ... or there lack of.

Hold on there...we can call this a bug when there's a repro case. In the small bit of testing I've done, I don't see dramatic differences in the time for selectFeatures to complete when using query.where vs. query.objectIds. If you'd like to take this further, we need a repro case that clearly shows the problem or you could open a ticket with support and have them help investigate (possibly easier option).

Re: SelectFeatures query slow when using only ObjectIds param

DianaBenedict — Mon, 29 Apr 2013 18:11:24 GMT

First let me apologize if I seemed a bit "upset".

How is generating a where clause considerably more effort than generating an array of objectIds? Whether it's a where clause (a string) or an array of numbers, you're setting a property on a query object. Building a comma separated string is slightly more effort, but it's minimal.

1) I think the code looks cleaner (developer preference)
2) I would assume that the .ObjectIds was added so that it can support what is returned by the FeatureEditResult object (though I must admit that is my assumption).
3)From my understanding of Databases. I believe that it is less efficient to send an "injected" where clause to the database (especially an IN statment) as opposed to lets say a "paramaterized query" or a stored proc. With that said, I guess it will depend on the "back-end" code that is used to process these queries. I have found that if I use the query.ObjectIds param along with another query paramater such .geometry or .where the query returns my results witin the expected <1 second.

Again, I have not had the chance/time to request a database query trace from our Oracle DBA on the two queries (query.where as opposed to query.ObjectIds) but my initial "hunch" is that the latter is performing a full table scan .. not utilitizing the ObjectId and/or GlobalId Indexes. Usually, this type of behaviour is not obviousl when dealing with smaller datasets but become much more apparent when you start dealing with much larger datasets/feature classes/tables ... additionally, out data is versioned and is a replicate with GlobalId. We found a similar issue prior to SP1 where by applyEdits with Inserts were taking over 40 seconds to work; it was only apparent with very large datasets that had GlobalId and it took me over 1 day of my development time to document and submit a "ticket". Findings revealed that a full table scan was actually being done ... it had something to do with GlobalId. The fix was posted in SP1 and now inserts work great!

When I get a chance I will request a database trace on these two queries and post my results. In the meantime I think it would also be helpful if ESRI could do a similar test so we can verify our results. Based on my results I will go ahead and submit a ticket if needed.

Re: SelectFeatures query slow when using only ObjectIds param

derekswingley1 — Sat, 11 Dec 2021 09:12:32 GMT

I put together a simple test case:

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=7, IE=9, IE=10">
    <meta name="viewport" content="initial-scale=1, maximum-scale=1, user-scalable=no">
    <title></title>
    <link rel="stylesheet" href="http://serverapi.arcgisonline.com/jsapi/arcgis/3.4/js/esri/css/esri.css">
    <style>
      html, body, #map {
        height: 100%; width: 100%; margin: 0; padding: 0; 
      }
      #feedback {
        background: #fff;
        bottom: 30px;
        color: #444;
        position: absolute;
        font-family: arial;
        height: auto;
        left: 20px;
        margin: 5px;
        padding: 10px;
        bottom: 20px;
        width: 200px;
        z-index: 40;
      }
    </style>

    <script src="http://serverapi.arcgisonline.com/jsapi/arcgis/3.4/"></script>
    <script>
      var map;
    
      require([
        "dojo/dom", "dojo/on", "dojo/query",
        "esri/map", "esri/layers/FeatureLayer", "esri/tasks/query", 
        "dojo/domReady!"
      ], function(dom, on, dojoQuery, Map, FeatureLayer, Query) {
          map = new Map("map", {
            basemap: "streets",
            center: [-93.011, 44.951],
            zoom: 11
          });

          var fl = new FeatureLayer("http://sampleserver6.arcgisonline.com/arcgis/rest/services/Census/MapServer/1", {
            id: "BlockGroups"
          });
          map.addLayer(fl);

          // generate 10 random numbers < 200,000
          var randoms = [];
          for ( var i = 0; i < 10; i++ ) {
            randoms.push(parseInt(Math.random() * 200000));
          }

          on(dom.byId("runQuery"), "click", function() {
            var queryMethod = dojoQuery("input[name=queryType]:checked")[0].value;
            var params = new Query();
            params.returnGeometry = false;
            if ( queryMethod === "where" ) {
              // params.where = "OBJECTID IN (300, 301)";
              params.where = "OBJECTID IN (" + randoms.join(",") + ")";
              console.log("set params.where", params.where);
            } else {
              // params.objectIds = [ 300, 301 ];
              params.objectIds = randoms
              console.log("set params.objectIds", params.objectIds);
            }
            fl.queryFeatures(params).then(querySuccess, queryError);
          });

          function querySuccess(results) {
            console.log("query success:  ", results);
          }
          function queryError(error) {
            console.log("query error:  ", error);
          }
        }
      );
    </script>
  </head>
  <body>
    <div id="map"></div>
    <div id="feedback">
      Query using:
      <br>
      <input type="radio" name="queryType" id="whereClause" value="where" checked="checked"><label for="whereClause">Where</label>
      <input type="radio" name="queryType" id="objectIds" value="objectIds"><label for="objectIds">ObjectIds</label>
      <br>
      <button id="runQuery">Query</button>
    </div>
  </body>
</html>

That code is querying ten random features by OID from a layer with ~200k features (census block groups). Querying with .where or .objectIds takes the same amount of time. Can you repro your issue with the code above? Is this case similar to what you're doing?

I would pursue this on the DB side to see what the different queries look like for query.where vs. query.objectIds.

Re: SelectFeatures query slow when using only ObjectIds param

DianaBenedict — Tue, 30 Apr 2013 18:48:01 GMT

Derek

Again, thank you for your response. I have contacted our Oracle DBA and requested that we do a database trace on the two queries. I have tested the queries with smaller data sets and with a feature layer is not multipoint (polygon feture class) to eliminate additional database discrepencies. It seems like I was still able to get a difference in query results with a smaller dataset but the results were still similar in that it did take longer on our end to do the query with objectIds vs where. FYI: I am pointing to an Oracle SDE databsae 10.1 sp1 that uses ST_Geometry.

This might actually be a database issue (as you did mention). I may try to create some services that point to some of out 9.3.1 SDE instance and see if I get different results. For now, I will go ahead and do some homework on my end and see if I can trace the issue.

Thanks again and I will post something once I find out the issue.

Re: SelectFeatures query slow when using only ObjectIds param

DerivenC — Wed, 14 Aug 2013 12:21:32 GMT

I am able to reproduce this as well. Feature layer using ArcGIS Server 10.1. If I use an array of object IDs in the query string, it takes minutes to return. If I use OBJECTID = ..., it is instantaneous.

The service is not mine. So I cannot tell you what the service is connected to. But I suspect it's linked to some other database and/or using a particular database server (Oracle?)

ESRI: if someone would like the sample REST query to test out, please let me know and I'll PM you with the urls.

Re: SelectFeatures query slow when using only ObjectIds param

DianaBenedict — Wed, 14 Aug 2013 22:08:33 GMT

I can tell you for a fact that a COMPLETE table scan is done. I have had our DBA trace the SQL call and I am 100% sure this is occuring. Maybe it has to with the fact that our SDE data uses ST_Geometry (Oracle Database) data type but I DO NOT use objectIds though this should be a more efficient way to do this.