[Building Sakai] Heavy Samigo query

[Kusnetz, Jeremy]

FYI, comparing the code in 2.6.3 to 2.8 it appears that the first query
has been fixed in 2.8 (it adds querying by the SITE_ID), but this one
below still doesn't have any kind of where and will return all rows for
SAM_PUBLISHEDFEEDBACK_T, which in our case is currently over 50K and
will return all this data each time a user enters the tool.

 

From: Kusnetz, Jeremy 
Sent: Wednesday, October 12, 2011 9:15 AM
To: Kusnetz, Jeremy; sakai-dev
Subject: RE: [Building Sakai] Heavy Samigo query

 

Here is another one:

 

EXPLAIN 

select publishedf0_.ASSESSMENTID as col_0_0_,
publishedf0_.FEEDBACKDELIVERY as col_1_0_,
publishedf0_.FEEDBACKAUTHORING as col_2_0_, publishedf0_.EDITCOMPONENTS
as col_3_0_, publishedf0_.SHOWQUESTIONTEXT as col_4_0_,
publishedf0_.SHOWSTUDENTRESPONSE as col_5_0_,
publishedf0_.SHOWCORRECTRESPONSE as col_6_0_,
publishedf0_.SHOWSTUDENTSCORE as col_7_0_,
publishedf0_.SHOWSTUDENTQUESTIONSCORE as col_8_0_,
publishedf0_.SHOWQUESTIONLEVELFEEDBACK as col_9_0_,
publishedf0_.SHOWSELECTIONLEVELFEEDBACK as col_10_0_,
publishedf0_.SHOWGRADERCOMMENTS as col_11_0_,
publishedf0_.SHOWSTATISTICS as col_12_0_ from SAM_PUBLISHEDFEEDBACK_T
publishedf0_

______ Sub-Part 1 _______

Select Type: SIMPLE      

      Table: publishedf0_

Partitions:             

       Type: ALL         

 Poss. Keys:             

      Index:             

 Key Length:             

  Index Ref:             

  Row Count: 45745       

    Special:             

 

Press e to explain, f for full query, o for optimized query

 

I'm worried that these queries are not even doing any kind of where's so
they are just doing full table scans.

 

Jeremy Kusnetz | Sr. Systems Engineer

 

American Public University System 
American Military University  |  American Public University
661 S George Street, Charles Town, WV 25414 
T 304-885-5333 | M 703-967-5212 |  jkusnetz at apus.edu| www.apus.edu
<http://www.apus.edu> 

 

From: sakai-dev-bounces at collab.sakaiproject.org
[mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Kusnetz,
Jeremy
Sent: Wednesday, October 12, 2011 9:13 AM
To: sakai-dev
Subject: [Building Sakai] Heavy Samigo query

 

One of the queries that we see piling up during high load is the
following (this is from innotop).  We heavily utilize Samigo, with
thousands of active classrooms, many with a dozen or more assessments.
This is on CLE 2.6.3

 

EXPLAIN 

select publishede0_.ASSESSMENTID as col_0_0_, publishede0_.SCORINGTYPE
as col_1_0_, publisheda1_.SUBMISSIONSALLOWED as col_2_0_ from
SAM_PUBLISHEDEVALUATION_T publishede0_, SAM_PUBLISHEDACCESSCONTROL_T
publisheda1_ where publishede0_.ASSESSMENTID=publisheda1_.ASSESSMENTID

_____________ Sub-Part 1 ______________  __________________ Sub-Part 1
__________________

Select Type: SIMPLE                      Select Type: SIMPLE


      Table: publishede0_                      Table: publisheda1_


 Partitions:                              Partitions:


       Type: ALL                                Type: eq_ref


 Poss. Keys: PRIMARY,FK94CB245F9482C945   Poss. Keys:
PRIMARY,FK2EDF39E09482C945         

      Index:                                   Index: PRIMARY


 Key Length:                              Key Length: 8


  Index Ref:                               Index Ref:
sakai_300.publishede0_.ASSESSMENTID

  Row Count: 45874                         Row Count: 1


    Special:                                 Special:


 

Press e to explain, f for full query, o for optimized query

 

Jeremy Kusnetz | Sr. Systems Engineer

 

American Public University System 
American Military University  |  American Public University
661 S George Street, Charles Town, WV 25414 
T 304-885-5333 | M 703-967-5212 |  jkusnetz at apus.edu| www.apus.edu
<http://www.apus.edu> 

 

This message is private and confidential. If you have received it in
error, please notify the sender and remove it from your system.

This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20111014/506c1e1a/attachment.html