KFoster44

Query Performance

Discussion created by KFoster44 on Dec 20, 2018
Latest reply on Jul 25, 2019 by tim.sexton

Hello,

 

I'm working on some offline development and I'm hitting some pretty abysmal performance issues using the .net runtime API.  I have put together the following sample code to demonstrate the issue.  The following I have executed against a table with 67,000 records and my where clause is simply "1=1" to return the entire contents of the table.  Below are the performance numbers.  Is there something I'm missing here?  Obviously this sort of performance is unacceptable when querying large amounts of features in an offline database.  I'm using the 100.4.0 API currently with this test.

 

Direct Sqlite Connection

11.04 Seconds to iterate over all of the records.

 

.net Runtime API

4 Minutes and 53 Seconds to iterate over all of the records.

 

Here is the sample code.

 

Stopwatch directSqlSW = new Stopwatch();

Stopwatch runtimeAPISW = new Stopwatch();

int featuresFound = 0;

 

directSqlSW.Start();

//Find the features using direct Sqlite Connection

SQLiteConnection directSqliteConnection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", ((GeodatabaseFeatureTable)SearchAgainstFeatureLayer.FeatureTable).Geodatabase.Path));

directSqliteConnection.Open();

 

SQLiteCommand selectCommand = new SQLiteCommand(string.Format("SELECT * FROM {0} WHERE {1}", SearchAgainstFeatureLayer.FeatureTable.TableName, WhereClause), directSqliteConnection);

SQLiteDataReader reader = selectCommand.ExecuteReader();

while (reader.Read())

{

   //Read attributes

   Dictionary<string, object> attributes = new Dictionary<string, object>();

   foreach (Field field in SearchAgainstFeatureLayer.FeatureTable.Fields)

   {

      attributes.Add(field.Name, reader[field.Name]);

   }

   featuresFound++;

}

reader.Close();

directSqliteConnection.Close();

directSqlSW.Stop();

               

featuresFound = 0;

runtimeAPISW.Start();

//Find the features using .net runtime API

QueryParameters queryParams = new QueryParameters();

queryParams.WhereClause = WhereClause;

FeatureQueryResult results = await SearchAgainstFeatureLayer.FeatureTable.QueryFeaturesAsync(queryParams);

               

foreach (Feature feature in results)

{

   //Read attributes

   Dictionary<string, object> attributes = new Dictionary<string, object>();

   foreach (Field field in SearchAgainstFeatureLayer.FeatureTable.Fields)

   {

      attributes.Add(field.Name, feature.GetAttributeValue(field.Name));

   }

   featuresFound++;

}

runtimeAPISW.Stop();

Outcomes