Could I be having scaling issues

ckacoroski · Post by **ckacoroski** » Fri Jul 06, 2007 4:22 pm

Hi,

I am running into problems opening a tab on a form. The tab opens onto a reference list and it just hangs until it times out. I checked the tables and I have the following:

ChangeLog: 87K records
Class_ref: 53K records
Group_ref: 11K records
Staff: 2.8K records
Student: 20K records
Student_ref: 156K records

The structure has staff and student accounts. Each account has a form with 4 tabs (basic data, groups, classes, and changes). The groups, classes, and changes tabs basically list the groups, classes or changes associated with the account. Currently the changes tab works. The groups tab is slow, but seems to work, and the classes tab hangs 100% of the time.

Any help in debugging is appreciated. I did not experience this with my smaller test dataset.

I am running against a mysql database using InnoDB tables.

cheers,

ski

tford · Post by **tford** » Fri Jul 06, 2007 5:29 pm

ski,

What values do you have for 'Show ___ item' for the reference elements in question?

Tom

ckacoroski · Post by **ckacoroski** » Fri Jul 06, 2007 5:57 pm

Tom,

I am still new and this and do not understand what you are asking for. What do you mean by 'Show ____ item'?

thanks for your help.

ski

ckacoroski · Post by **ckacoroski** » Fri Jul 06, 2007 9:25 pm

Definitely scaling issues. I created a smaller test database with about 500 staff, 1000 students, and the groups/classes they need. The table sizes in BASTEST are (in records):

ChangeLog: 17K
Class: 1.4K
Class_ref: 6.2K
Group: 0.3K
Group_ref: 7.6K
Staff: 0.5K
Staff_ref: 7.4K
Student: 1.0K
Student_ref: 20.7K

I did a search for all students, opened one student's classes tab, and then just scrolled between students. It took 12 - 14 seconds change to the next student. When I had the students group tab open, it took between 5 - 6 seconds to change students. The basic data tab (no reference attributes) took less than a second to change to the next student. The changelog tab also took less than a second.

The Group and Class tabs each have 4 reference attributes on them and one of these four attributes are a matching attribute. The changelog tab reference attribute is not a matching attribute.

I really need help on how to speed up the query as when I load in the entire 20K students, it is unusable.

Let me know what you need to help resolve this or what I can do to speed things up.

cheers,

ski

ckacoroski · Post by **ckacoroski** » Fri Jul 06, 2007 9:39 pm

Ok, More testing. The matching attribute is the problem If I remove it from the classes tab and just leave in the attributes that are not matching, it only takes a few seconds to load a student. If I remove the attributes that are not matching and leave in the matching attribute, then it takes 12 - 13 seconds to display a student. Using the show processlist command from mysql, the query that is jamming things up is:

SELECT Class.GidNumber,Class.ID,Class.State,Class.GroupName,Class.BASVERSION,Class.BASTIMESTAMP FROM BASTESTDOMAINDIRMAN_CLASS AS Class LEFT JOIN BASTESTDOMAINDIRMAN_CLASS_REF AS ER_Class_Class_Members ON Class.ID=ER_Class_Class_Members.ID LEFT JOIN BASTESTDOMAINDIRMAN_STAFF AS Class_Members_Staff ON ER_Class_Class_Members.RID=Class_Members_Staff.ID AND ER_Class_Class_Members.FIELD_NAME='Members' LEFT JOIN BASTESTDOMAINDIRMAN_STUDENT AS Class_Members_Student ON ER_Class_Class_Members.RID=Class_Members_Student.ID AND ER_Class_Class_Members.FIELD_NAME='Members' WHERE (((Class_Members_Student.ID=414865 AND Class_Members_Student.ID IS NOT NULL)) OR ((Class_Members_Staff.ID=414865 AND Class_Members_Staff.ID IS NOT NULL)))

I checked both the classes and student objects and cannot find anything that I could index that may make a difference.

I look forward to seeing what you can figure out on this one.

ski

ckacoroski · Post by **ckacoroski** » Fri Jul 06, 2007 9:40 pm

PS: all attributes in the previous post are reference attributes.

aware_support2 · Post by **aware_support2** » Sat Jul 07, 2007 4:03 am

If you use the dropdown presentation option for your reference attributes, and the number of displayed items in the dropdown is large, this may take a long time as the system currently puts all the items into the dropdown list. The solution is to change the presentation option to the Table Of Items where only the first portion of the list is shown on the form.

The next version of Aware IM should have reference dropdowns behaving similarly to the Table Of Items in that only the first portion of a list is displayed with scrolling controls.

ckacoroski · Post by **ckacoroski** » Sat Jul 07, 2007 4:24 am

I am not using drop down. I am using the table of items and showing between 5 - 20 items in the table at a time.