Loading data from enterprise geodatabase is slow in ArcGIS Pro 2.4 SDK

[JeffSauder]

I am working on creating an add-in for Pro that we use with ArcMap and is very popular, hence the need to get it working with Pro before we transition to using Pro exclusively.  The issue is that retrieving data from our enterprise database is extremely slow, in the sample code below I am loading combo boxes with data from the geodb but it is going extremely slow, it takes around 90 seconds to bring all this data in (to compare with the ArcMap add-in the combo boxes are filled almost immediately).  I don't consider it an immensely large amount of data (2036 N streets, 7391 S streets, 8416 E streets, 1208 W streets, and 1942 sudbivisions).  Again, the ArcMap add-in has this data (and more, this is only about half of what I need to load for the completed project). Being new to the SDK for Pro, I may be doing all this in a less than efficient way?  I thought about if I could have one "await" queuedtask that would run all the database queries at the same time, I don't even know if that's possible, but even if so, it would take at least 45 seconds.

I appreciate any feedback provided.  Thanks in advance for your assistance.

Jeffrey Sauder

GIS Administrator

Town of Gilbert, Arizona

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
//using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using ArcGIS.Core.CIM;
using ArcGIS.Core.Data;
using ArcGIS.Core.Geometry;
using ArcGIS.Desktop.Catalog;
using ArcGIS.Desktop.Core;
using ArcGIS.Desktop.Editing;
using ArcGIS.Desktop.Extensions;
using ArcGIS.Desktop.Framework;
using ArcGIS.Desktop.Framework.Contracts;
using ArcGIS.Desktop.Framework.Dialogs;
using ArcGIS.Desktop.Framework.Threading.Tasks;
using ArcGIS.Desktop.Mapping;

namespace ProAppModule1
{
/// <summary>
/// Interaction logic for Tools.xaml
/// </summary>
public partial class Tools : ArcGIS.Desktop.Framework.Controls.ProWindow
{
List<string> streetNamesN = new List<string>();
List<string> streetNamesS = new List<string>();
List<string> streetNamesE = new List<string>();
List<string> streetNamesW = new List<string>();
List<string> subdivNames = new List<string>();
DatabaseConnectionProperties connectionProperties = new DatabaseConnectionProperties(EnterpriseDatabaseType.SQLServer)
{
AuthenticationMode = AuthenticationMode.DBMS,
Instance = @"gisdb",
Database = "GBAGIS",
User = "ixxxxxx",
Password = "xxxxxx",
Version = "sde.DEFAULT"
};
public Tools()
{
InitializeComponent();
StartThis();
}
private async void StartThis()
{
AddrDirCbo.Items.Add("N");
AddrDirCbo.Items.Add("S");
AddrDirCbo.Items.Add("E");
AddrDirCbo.Items.Add("W");
AddrDirCbo.SelectedIndex = 0;

System.Windows.MessageBox.Show("Start");
streetNamesN = await GetDataForComboBoxes(0);
streetNamesS = await GetDataForComboBoxes(1);
streetNamesE = await GetDataForComboBoxes(2);
streetNamesW = await GetDataForComboBoxes(3);
subdivNames = await GetDataForComboBoxes(4);
System.Windows.MessageBox.Show("Done With GEODB");

await LoadComboBoxes(0);
await LoadComboBoxes(1);
await LoadComboBoxes(2);
await LoadComboBoxes(3);
await LoadComboBoxes(4);
System.Windows.MessageBox.Show("All Done");
}
async Task<List<string>> GetDataForComboBoxes(int inLayer)
{
string dbField = "";
string dbTable = "";
string dbWhere = "";
string dbSubFields = "";
string dbPostClause = "";
List<string> dataFromDB=new List<string>();

await ArcGIS.Desktop.Framework.Threading.Tasks.QueuedTask.Run(() =>
{
switch (inLayer)
{
case 0: //N streets
dbField = "TOG_STNAME";
dbTable = "gbagis.dbo.stnetg";
dbWhere = "SNG_ST_DIR = 'N' and TOG_STNAME IS NOT NULL";
dbSubFields = "DISTINCT TOG_STNAME";
dbPostClause = "ORDER BY TOG_STNAME";
break;
case 1: //S streets
dbField = "TOG_STNAME";
dbTable = "gbagis.dbo.stnetg";
dbWhere = "SNG_ST_DIR = 'S' and TOG_STNAME IS NOT NULL";
dbSubFields = "DISTINCT TOG_STNAME";
dbPostClause = "ORDER BY TOG_STNAME";
break;
case 2: //E streets
dbField = "TOG_STNAME";
dbTable = "gbagis.dbo.stnetg";
dbWhere = "SNG_ST_DIR = 'E' and TOG_STNAME IS NOT NULL";
dbSubFields = "DISTINCT TOG_STNAME";
dbPostClause = "ORDER BY TOG_STNAME";
break;
case 3: //W streets
dbField = "TOG_STNAME";
dbTable = "gbagis.dbo.stnetg";
dbWhere = "SNG_ST_DIR = 'W' and TOG_STNAME IS NOT NULL";
dbSubFields = "DISTINCT TOG_STNAME";
dbPostClause = "ORDER BY TOG_STNAME";
break;
case 4: //Subdivision
dbField = "ENG_NAME";
dbTable = "gbagis.dbo.recorded_plats";
dbWhere = "ENG_NAME !='' AND ENG_NAME IS NOT NULL";
dbSubFields = "DISTINCT ENG_NAME";
dbPostClause = "ORDER BY ENG_NAME";
break;
}
using (Geodatabase geodatabase = new Geodatabase(connectionProperties))
{
using (Table stTable = geodatabase.OpenDataset<Table>(dbTable))
{
QueryFilter queryFilter = new QueryFilter
{
WhereClause = dbWhere,
SubFields = dbSubFields,
PostfixClause = dbPostClause
};
using (RowCursor rowCursor = stTable.Search(queryFilter, false))
{
while (rowCursor.MoveNext())
{
using (Row row = rowCursor.Current)
{
dataFromDB.Add(Convert.ToString(row[dbField]));
}
}
}
}
}
});
return dataFromDB;
}
async Task LoadComboBoxes(int inTable)
{
//System.Windows.MessageBox.Show("Load");
switch(inTable)
{
case 0:
foreach (string sName in streetNamesN)
{
AddrStreetCboN.Items.Add(sName);
}
AddrStreetCboN.SelectedIndex = 0;
break;
case 1:
foreach (string sName in streetNamesS)
{
AddrStreetCboS.Items.Add(sName);
}
AddrStreetCboS.SelectedIndex = 0;
break;
case 2:
foreach (string sName in streetNamesE)
{
AddrStreetCboE.Items.Add(sName);
}
AddrStreetCboE.SelectedIndex = 0;
break;
case 3:
foreach(string sName in streetNamesW)
{
AddrStreetCboW.Items.Add(sName);
}
AddrStreetCboW.SelectedIndex = 0;
break;
case 4:
foreach(string sName in subdivNames)
{
SubdivCbo.Items.Add(sName);
}
SubdivCbo.SelectedIndex = 0;
break;
}
}