[Building Sakai] FW: I am seeing on slow query related to samigo
Kusnetz, Jeremy
JKusnetz at APUS.EDU
Tue Jul 30 09:55:31 PDT 2013
FYI, we discovered when this query is being generated.
When an instructor goes to grade submitted assessemnts, they click on the number of submitted assessments and then they get a screen with a list of students. On top there is a ribbon bar, "Submission Status|Total Scores|Questions|Statistics|Item Analysis|Export". The long query happens when the click the "Questions" link.
-----Original Message-----
From: azeckoski at gmail.com [mailto:azeckoski at gmail.com] On Behalf Of Aaron Zeckoski
Sent: Tuesday, July 30, 2013 10:05 AM
To: Kusnetz, Jeremy; Karen Tsao; samigo-team at collab.sakaiproject.org
Cc: sakai-dev at collab.sakaiproject.org
Subject: Re: FW: I am seeing on slow query related to samigo
Copying in the samigo team list.
-AZ
On Tue, Jul 30, 2013 at 9:44 AM, Kusnetz, Jeremy <JKusnetz at apus.edu> wrote:
> We just upgraded to Sakai 2.9.x (close to 2.9.3 RC01).
>
>
>
> I’m seeing some pretty long queries coming from Samigo, taking upwards
> of 30 seconds or more. We did not see this in 2.8.
>
>
>
> I’m not sure if we can fix it with an index, or the sql needs to be
> refactored.
>
>
>
> select publishedi0_.ITEMID as ITEMID212_, publishedi0_.SECTIONID as
> SECTIONID212_, publishedi0_.ITEMIDSTRING as ITEMIDST3_212_,
> publishedi0_.SEQUENCE as SEQUENCE212_, publishedi0_.DURATION as
> DURATION212_, publishedi0_.TRIESALLOWED as TRIESALL6_212_,
> publishedi0_.INSTRUCTION as INSTRUCT7_212_, publishedi0_.DESCRIPTION
> as DESCRIPT8_212_, publishedi0_.TYPEID as TYPEID212_,
> publishedi0_.GRADE as GRADE212_, publishedi0_.SCORE as SCORE212_,
> publishedi0_.DISCOUNT as DISCOUNT212_, publishedi0_.HINT as HINT212_,
> publishedi0_.HASRATIONALE as HASRATI14_212_,
> publishedi0_.PARTIAL_CREDIT_FLAG as PARTIAL15_212_,
> publishedi0_.STATUS as STATUS212_, publishedi0_.CREATEDBY as
> CREATEDBY212_, publishedi0_.CREATEDDATE as CREATED18_212_,
> publishedi0_.LASTMODIFIEDBY as LASTMOD19_212_,
> publishedi0_.LASTMODIFIEDDATE as LASTMOD20_212_ from
> SAM_PUBLISHEDITEM_T publishedi0_ where publishedi0_.ITEMID in (select
> publishedi1_.ITEMID from SAM_PUBLISHEDITEM_T publishedi1_,
> SAM_ASSESSMENTGRADING_T assessment2_, SAM_ITEMGRADING_T itemgradin3_
> where
> assessment2_.PUBLISHEDASSESSMENTID=267345 and assessment2_.FORGRADE=1
> and
> publishedi1_.SECTIONID=853460 and
> itemgradin3_.ASSESSMENTGRADINGID=assessment2_.ASSESSMENTGRADINGID and
> publishedi1_.ITEMID=itemgradin3_.PUBLISHEDITEMID);
>
>
>
> *************************** 1. row ***************************
>
> id: 1
>
> select_type: PRIMARY
>
> table: publishedi0_
>
> type: ALL
>
> possible_keys: NULL
>
> key: NULL
>
> key_len: NULL
>
> ref: NULL
>
> rows: 8351034
>
> Extra: Using where
>
> *************************** 2. row ***************************
>
> id: 2
>
> select_type: DEPENDENT SUBQUERY
>
> table: publishedi1_
>
> type: eq_ref
>
> possible_keys: PRIMARY,FK53ABDCF6895D4813
>
> key: PRIMARY
>
> key_len: 8
>
> ref: func
>
> rows: 1
>
> Extra: Using where
>
> *************************** 3. row ***************************
>
> id: 2
>
> select_type: DEPENDENT SUBQUERY
>
> table: itemgradin3_
>
> type: ref
>
> possible_keys: FKB68E675667B430D5,SAM_ITEMGRADING_ITEM_I
>
> key: SAM_ITEMGRADING_ITEM_I
>
> key_len: 8
>
> ref: func
>
> rows: 5
>
> Extra: Using where
>
> *************************** 4. row ***************************
>
> id: 2
>
> select_type: DEPENDENT SUBQUERY
>
> table: assessment2_
>
> type: eq_ref
>
> possible_keys: PRIMARY,SAM_PUBLISHEDASSESSMENT_I
>
> key: PRIMARY
>
> key_len: 8
>
> ref: sakai_100.itemgradin3_.ASSESSMENTGRADINGID
>
> rows: 1
>
> Extra: Using where
>
> 4 rows in set (0.00 sec)
>
>
>
>
>
> mysql> show create table SAM_PUBLISHEDITEM_T\G
>
> *************************** 1. row ***************************
>
> Table: SAM_PUBLISHEDITEM_T
>
> Create Table: CREATE TABLE `SAM_PUBLISHEDITEM_T` (
>
> `ITEMID` bigint(20) NOT NULL AUTO_INCREMENT,
>
> `SECTIONID` bigint(20) NOT NULL,
>
> `ITEMIDSTRING` varchar(255) DEFAULT NULL,
>
> `SEQUENCE` int(11) DEFAULT NULL,
>
> `DURATION` int(11) DEFAULT NULL,
>
> `TRIESALLOWED` int(11) DEFAULT NULL,
>
> `INSTRUCTION` varchar(4000) DEFAULT NULL,
>
> `DESCRIPTION` varchar(4000) DEFAULT NULL,
>
> `TYPEID` bigint(20) NOT NULL,
>
> `GRADE` varchar(80) DEFAULT NULL,
>
> `SCORE` float DEFAULT NULL,
>
> `DISCOUNT` float DEFAULT NULL,
>
> `HINT` varchar(4000) DEFAULT NULL,
>
> `HASRATIONALE` bit(1) DEFAULT NULL,
>
> `STATUS` int(11) NOT NULL,
>
> `CREATEDBY` varchar(255) NOT NULL,
>
> `CREATEDDATE` datetime NOT NULL,
>
> `LASTMODIFIEDBY` varchar(255) NOT NULL,
>
> `LASTMODIFIEDDATE` datetime NOT NULL,
>
> `PARTIAL_CREDIT_FLAG` bit(1) DEFAULT NULL,
>
> PRIMARY KEY (`ITEMID`),
>
> KEY `FK53ABDCF6895D4813` (`SECTIONID`),
>
> CONSTRAINT `FK53ABDCF6895D4813` FOREIGN KEY (`SECTIONID`) REFERENCES
> `SAM_PUBLISHEDSECTION_T` (`SECTIONID`)
>
> ) ENGINE=InnoDB AUTO_INCREMENT=9829483 DEFAULT CHARSET=utf8
>
> 1 row in set (0.00 sec)
>
>
>
> mysql> show create table SAM_ASSESSMENTGRADING_T\G
>
> *************************** 1. row ***************************
>
> Table: SAM_ASSESSMENTGRADING_T
>
> Create Table: CREATE TABLE `SAM_ASSESSMENTGRADING_T` (
>
> `ASSESSMENTGRADINGID` bigint(20) NOT NULL AUTO_INCREMENT,
>
> `PUBLISHEDASSESSMENTID` bigint(20) NOT NULL,
>
> `AGENTID` varchar(255) NOT NULL,
>
> `SUBMITTEDDATE` datetime DEFAULT NULL,
>
> `ISLATE` bit(1) NOT NULL,
>
> `FORGRADE` bit(1) NOT NULL,
>
> `TOTALAUTOSCORE` float DEFAULT NULL,
>
> `TOTALOVERRIDESCORE` float DEFAULT NULL,
>
> `FINALSCORE` float DEFAULT NULL,
>
> `COMMENTS` varchar(4000) DEFAULT NULL,
>
> `GRADEDBY` varchar(255) DEFAULT NULL,
>
> `GRADEDDATE` datetime DEFAULT NULL,
>
> `STATUS` int(11) NOT NULL,
>
> `ATTEMPTDATE` datetime DEFAULT NULL,
>
> `TIMEELAPSED` int(11) DEFAULT NULL,
>
> `ISAUTOSUBMITTED` bit(1) DEFAULT NULL,
>
> `LASTVISITEDPART` int(11) DEFAULT NULL,
>
> `LASTVISITEDQUESTION` int(11) DEFAULT NULL,
>
> PRIMARY KEY (`ASSESSMENTGRADINGID`),
>
> KEY `SAM_PUBLISHEDASSESSMENT_I` (`PUBLISHEDASSESSMENTID`),
>
> KEY `SAM_ASSGRAD_AID_PUBASSEID_T`
> (`AGENTID`,`PUBLISHEDASSESSMENTID`)
>
> ) ENGINE=InnoDB AUTO_INCREMENT=3243761 DEFAULT CHARSET=utf8
>
> 1 row in set (0.00 sec)
>
>
>
> mysql> show create table SAM_ITEMGRADING_T\G
>
> *************************** 1. row ***************************
>
> Table: SAM_ITEMGRADING_T
>
> Create Table: CREATE TABLE `SAM_ITEMGRADING_T` (
>
> `ITEMGRADINGID` bigint(20) NOT NULL AUTO_INCREMENT,
>
> `ASSESSMENTGRADINGID` bigint(20) NOT NULL,
>
> `PUBLISHEDITEMID` bigint(20) NOT NULL,
>
> `PUBLISHEDITEMTEXTID` bigint(20) NOT NULL,
>
> `AGENTID` varchar(255) NOT NULL,
>
> `SUBMITTEDDATE` datetime DEFAULT NULL,
>
> `PUBLISHEDANSWERID` bigint(20) DEFAULT NULL,
>
> `RATIONALE` varchar(4000) DEFAULT NULL,
>
> `AUTOSCORE` float DEFAULT NULL,
>
> `OVERRIDESCORE` float DEFAULT NULL,
>
> `COMMENTS` varchar(4000) DEFAULT NULL,
>
> `GRADEDBY` varchar(255) DEFAULT NULL,
>
> `GRADEDDATE` datetime DEFAULT NULL,
>
> `REVIEW` bit(1) DEFAULT NULL,
>
> `ATTEMPTSREMAINING` int(11) DEFAULT NULL,
>
> `LASTDURATION` varchar(36) DEFAULT NULL,
>
> `ANSWERTEXT` text,
>
> `ISCORRECT` bit(1) DEFAULT NULL,
>
> PRIMARY KEY (`ITEMGRADINGID`),
>
> KEY `FKB68E675667B430D5` (`ASSESSMENTGRADINGID`),
>
> KEY `SAM_ITEMGRADING_ITEM_I` (`PUBLISHEDITEMID`),
>
> KEY `SAM_ITEMGRADING_ITEMTEXT_I` (`PUBLISHEDITEMTEXTID`),
>
> KEY `SAM_ITEMGRADING_PUBANS_I` (`PUBLISHEDANSWERID`),
>
> CONSTRAINT `FKB68E675667B430D5` FOREIGN KEY (`ASSESSMENTGRADINGID`)
> REFERENCES `SAM_ASSESSMENTGRADING_T` (`ASSESSMENTGRADINGID`)
>
> ) ENGINE=InnoDB AUTO_INCREMENT=72666685 DEFAULT CHARSET=utf8
>
> 1 row in set (0.00 sec)
>
> This message is private and confidential. If you have received it in
> error, please notify the sender and remove it from your system.
--
Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
More information about the sakai-dev
mailing list