Slowdown with DataCore.GetScalar 17.4 beta

This forum is for programmers who have questions about the source code.
Post Reply
User avatar
wjstarck
Posts: 935
Joined: Tue Jul 31, 2007 7:18 am
Location: Keller, TX
Contact:

Slowdown with DataCore.GetScalar 17.4 beta

Post by wjstarck » Thu Jan 04, 2018 2:41 pm

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
Cheers,

Bill Starck, DDS
Big Idea Software, LLC
Developer, EASy(Electronic Anesthesia System) for Open Dental
817-807-1709
TX, USA

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Slowdown with DataCore.GetScalar 17.4 beta

Post by jsalmon » Fri Jan 05, 2018 1:00 pm

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.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
wjstarck
Posts: 935
Joined: Tue Jul 31, 2007 7:18 am
Location: Keller, TX
Contact:

Re: Slowdown with DataCore.GetScalar 17.4 beta

Post by wjstarck » Fri Jan 05, 2018 1:21 pm

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
Cheers,

Bill Starck, DDS
Big Idea Software, LLC
Developer, EASy(Electronic Anesthesia System) for Open Dental
817-807-1709
TX, USA

Post Reply