Select Reviewer Performance Issue

[marct]

Greetings,
We are seeing significant performance problems in the ‘Select Reviewers’ function, which is in the getReviewersForArticle() method within classes/submission/sectionEditor/SectionEditorSubmissionDAO.inic.php. We have just under 100 reviewers and when the editors click the link, the app hangs for > 3 minutes while it performs the complex query with numerous joins.

This issue has been mentioned before: viewtopic.php?f=9&t=6750. We are running the latest version 2.3.4, which should have all the patches applied to address this issue. We compared our DB to a clean install DB and we have all the correct table indexes.

Does anyone have any suggestions or hacks to speed up this query?

Thanks

[marct]

To follow up on this: We have tried several hacks to eliminate all of the accessory information brought in by the joins but are not familiar enough with the underlying objects and templates and keep getting errors. Does anyone have examples of optimizing this code or specifically this query?

Are there any extra database indexes we could add? We think we have all of the "out of the box" indexes already.

Thank you.

[asmecher]

Hi marct,

Are you using review forms? I recently did some optimization work around that -- see http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=6439. Try that and if it doesn't help I can look into it further.

Regards,
Alec Smecher
Public Knowledge Project Team

[marct]

Thanks very much for the suggestion - we applied the patch but it did not solve the issue.

As best we can tell, the query that is the issue is on line 780 of lasses/submission/sectionEditor/SectionEditorSubmissionDAO.inc.php within the getReviewersForArticle() method. For some reason this method is taking several minutes to complete its query.

[asmecher]

Hi marct,

Could you log the query (either by temporarily turning on "debug" in config.inc.php or by using MySQL's slow query log) and try running a DESCRIBE for it through MySQL?

Regards,
Alec Smecher
Public Knowledge Project Team

[ramon]

Hello all,
I'm not sure if this problem is related, so I'll post it here...

A university, running OJS with 19 journals hosted. They have recently upgraded from a pretty old 2.2 to 2.3.4.
We believe the patches were applied after upgrading the database.
They have sent us a dump of their database, which we installed on a fresh 2.3.4 and proceeded as the support from UFG told us, patched after installing.
Some patches did not apply cleanly, such as the "Signoffs fix" patch.
They must have made a few mistakes while upgrading, although they claim there were no database error messages while upgrading.
While they have some style issues, which may be caused by the differences in 2.3.4, they reported that after upgrade a specific journal is crashing the db while attempting to assign reviewers.

This query took over 5min to execute in our test server:

Code: Select all

(mysql): SELECT DISTINCT u.user_id, u.last_name, ar.review_id, AVG(ra.quality) AS average_quality, COUNT(ac.review_id) AS completed, COUNT(ai.review_id) AS incomplete, MAX(ac.date_notified) AS latest, AVG(ac.date_completed-ac.date_notified) AS average FROM users u LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id) LEFT JOIN review_assignments ac ON (ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) LEFT JOIN review_assignments ai ON (ai.reviewer_id = u.user_id AND ai.date_completed IS NULL) LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = 10216 AND ar.round = '1') LEFT JOIN roles r ON (r.user_id = u.user_id) LEFT JOIN articles a ON (ra.submission_id = a.article_id) LEFT JOIN controlled_vocabs cv ON (cv.assoc_type = 4096 AND cv.assoc_id = u.user_id AND cv.symbolic = 'interest') LEFT JOIN controlled_vocab_entries cve ON (cve.controlled_vocab_id = cv.controlled_vocab_id) LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = cve.controlled_vocab_entry_id) WHERE u.user_id = r.user_id AND r.journal_id = '12' AND r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id

Then the following query is loaded, and the only difference is the limit 0,3000 as far as I can tell, and took over 5min as well.

Code: Select all

(mysql): SELECT DISTINCT u.user_id, u.last_name, ar.review_id, AVG(ra.quality) AS average_quality, COUNT(ac.review_id) AS completed, COUNT(ai.review_id) AS incomplete, MAX(ac.date_notified) AS latest, AVG(ac.date_completed-ac.date_notified) AS average FROM users u LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id) LEFT JOIN review_assignments ac ON (ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) LEFT JOIN review_assignments ai ON (ai.reviewer_id = u.user_id AND ai.date_completed IS NULL) LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = 10216 AND ar.round = '1') LEFT JOIN roles r ON (r.user_id = u.user_id) LEFT JOIN articles a ON (ra.submission_id = a.article_id) LEFT JOIN controlled_vocabs cv ON (cv.assoc_type = 4096 AND cv.assoc_id = u.user_id AND cv.symbolic = 'interest') LEFT JOIN controlled_vocab_entries cve ON (cve.controlled_vocab_id = cv.controlled_vocab_id) LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = cve.controlled_vocab_entry_id) WHERE u.user_id = r.user_id AND r.journal_id = '12' AND r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id ORDER BY u.last_name ASC LIMIT 0,3000


Then, OJS stopped responding at this query:

Code: Select all

(mysql): UPDATE sessions SET user_id = 21157, ip_address = '192.168.0.254', user_agent = 'Mozilla/5.0 (Windows NT 5.1; rv:2.0) Gecko/20100101 Firefox/4.0', created = 1300999069, last_used = 1301002135, remember = 0, data = 'username|s:12:\"carlacercomp\";userId|i:21157;enrolmentReferrer|a:1:{i:0;s:7:\"editors\";}signedInAs|s:1:\"1\";', acting_as = 0 WHERE session_id = '3bseban0891q86j66att08vc07'


Any ideas?
PS: Thank you for all past and future support provided!!

[ramon]

Hello all,

Anyone with ideas or has experienced such a thing?
If PKP members are willing, I can PM the DB dump.

[asmecher]

Hi Ramón,

I've passed this along to Matt and he'll follow up soon. The problem is with the Reviewing Interests join.

Regards,
Alec Smecher
Public Knowledge Project Team

[mcrider]

Hi Ramon,

Can you try applying this patch and letting us know if it speeds up the query for you?

Cheers,
Matt

[ramon]

Hello all,

The patch did work, but it brought all 123 reviewers available, although in config.inc.php items_per_page = 25.
Is this normal?

When disabling debug and stacktrace the query runs very fast.

I'll forward the patch to the Journal so they can test and see if it works on their end.
I'll keep you posted.

[jamilatta]

Ramon,

try to use this code, you can improve the code:

function &getReviewersForArticle($journalId, $articleId, $round, $searchType = null, $search = null, $searchMatch = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
$paramArray = array('interests', $articleId, $round, $journalId, RoleDAO::getRoleIdFromPath('reviewer'));
$searchSql = '';

if (isset($search)) switch ($searchType) {
case USER_FIELD_USERID:
$searchSql = 'AND user_id=?';
$paramArray[] = $search;
break;
case USER_FIELD_FIRSTNAME:
$searchSql = 'AND LOWER(first_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
break;
case USER_FIELD_LASTNAME:
$searchSql = 'AND LOWER(last_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
break;
case USER_FIELD_USERNAME:
$searchSql = 'AND LOWER(username) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
break;
case USER_FIELD_EMAIL:
$searchSql = 'AND LOWER(email) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
break;
case USER_FIELD_INTERESTS:
$paramArray = array('interests', $articleId, $round, ASSOC_TYPE_USER, 'interest', $journalId, RoleDAO::getRoleIdFromPath('reviewer') );

$searchSql = 'AND LOWER(cves.setting_value) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');

$result = &$this->retrieveRange(
'SELECT DISTINCT
u.*,
a.reviewer_id
FROM users u
LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?)
LEFT JOIN roles r ON (r.user_id = u.user_id)
LEFT JOIN review_assignments a ON (a.reviewer_id = u.user_id AND a.cancelled = 0 AND a.submission_id = ? AND a.round = ?)

LEFT JOIN controlled_vocabs cv ON (cv.assoc_type = ? AND cv.assoc_id = u.user_id AND cv.symbolic = ?)
LEFT JOIN controlled_vocab_entries cve ON (cve.controlled_vocab_id = cv.controlled_vocab_id)
LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = cve.controlled_vocab_entry_id)

WHERE u.user_id = r.user_id AND
r.journal_id = ? AND
r.role_id = ? ' . $searchSql . '
ORDER BY last_name, first_name',
$paramArray, $rangeInfo
);

break;
case USER_FIELD_INITIAL:
$searchSql = 'AND (LOWER(last_name) LIKE LOWER(?) OR LOWER(username) LIKE LOWER(?))';
$paramArray[] = $search . '%';
$paramArray[] = $search . '%';
break;
}
if($searchType != USER_FIELD_INTERESTS){
$result = &$this->retrieveRange(
'SELECT DISTINCT
u.*,
a.reviewer_id
FROM users u
LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?)
LEFT JOIN roles r ON (r.user_id = u.user_id)
LEFT JOIN review_assignments a ON (a.reviewer_id = u.user_id AND a.cancelled = 0 AND a.submission_id = ? AND a.round = ?)
WHERE u.user_id = r.user_id AND
r.journal_id = ? AND
r.role_id = ? ' . $searchSql . '
ORDER BY last_name, first_name',
$paramArray, $rangeInfo
);
}

$returner = &new DAOResultFactory($result, $this, '_returnReviewerUserFromRow');
return $returner;
}

[marct]

This code snippet resolved the issue for us. Performance is now fixed. Thanks very much!