Slowdown with DataCore.GetScalar 17.4 beta

[wjstarck]

Hello-

I have had this method on a form working fine for several years now:

Code: Select all

		
private void listProv_SelectedIndexChanged(object sender, EventArgs e) {
            listLocation.SelectedItems.Clear();
            provNum = GetAnesthProviderNum(listProv.SelectedItem.ToString());
            DataTable table = AnesthLocations.RefreshCache();
            locationCur = new AnesthLocation();
            for (int j = 0; j < table.Rows.Count; j++){
                locationCur.LocationNum = PIn.Long(table.Rows[j][0].ToString());
                for (int i = 1; i < listLocation.Items.Count + 1; i++){
                    if (AnesthLocations.GetLocationNumFromProvTable(provNum, locationCur.LocationNum) == true) {
                        listLocation.SetSelected(j, true);
                    }
                }
            }

}

AnesthLocations.GetLocationNumFromProvTable references this method in another class like so:

Code: Select all

        public static bool GetLocationNumFromProvTable(long providerNum, long locationNum ) {
            string command = "SELECT LocationNum FROM anesthprovider WHERE ProviderNum = '" + POut.Long(providerNum) + "' AND LocationNum = '" + POut.Long(locationNum) + "'";
            try {
                locationNum = Convert.ToInt64(DataCore.GetScalar(command));
                return true;
            }
            catch { return false;} 
        }

The form has a provider LIstBox and a location ListBox so the user can assign certain locations to providers by control clicking. It's used with the OD clinics feature to filter dropdowns on another form so the user is only presented with those providers that work in particular clinics.

In 17.4 beta, loading the form is now taking 6 seconds. The issue seems to be with this line

Code: Select all

locationNum = Convert.ToInt64(DataCore.GetScalar(command));

but I'm not sure why, as it has alway worked just fine before

UPDATE: Looks like the issue began sometime in 17.3.x branch, perhaps about a week ago

[jsalmon]

I would suspect the act of calling the DB so much (double for loop w/ db call nested inside) before I would suspect DataCore.GetScalar(). If you put a breakpoint on your AnesthLocations.GetLocationNumFromProvTable() line and it litterally takes 6 seconds to execute then I would suspect you have poor indexing on your anesthprovider table. You should have indexes for all FK columns on that table. Maybe even consider a multi-column index solution with your ProvNum / LocationNum columns since this has the potential to get hit very hard depending on the size of your loops.

[wjstarck]

Well, this is odd.

I let it sit for a few hours and the problem has vanished. Maybe some quirk with VS 2015 debugger IDK.

Thanks for taking the time to look at it Jason