Slowdown with DataCore.GetScalar 17.4 beta

This forum is for programmers who have questions about the source code.

Slowdown with DataCore.GetScalar 17.4 beta

Postby wjstarck » Thu Jan 04, 2018 3: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
TX, USA
User avatar
wjstarck
 
Posts: 758
Joined: Tue Jul 31, 2007 7:18 am
Location: Keller, TX

Re: Slowdown with DataCore.GetScalar 17.4 beta

Postby jsalmon » Fri Jan 05, 2018 2: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
jsalmon
 
Posts: 1420
Joined: Tue Nov 30, 2010 1:33 pm

Re: Slowdown with DataCore.GetScalar 17.4 beta

Postby wjstarck » Fri Jan 05, 2018 2: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
TX, USA
User avatar
wjstarck
 
Posts: 758
Joined: Tue Jul 31, 2007 7:18 am
Location: Keller, TX


Return to Developers

Who is online

Users browsing this forum: No registered users and 8 guests