Project

General

Profile

Actions
Copy link

Bug #2747

open

very slow SQL query in CTF results page

Added by Anonymous over 10 years ago. Updated over 10 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
-
Target version:
-
Start date:
04/28/2014
Due date:
% Done:

0%

Estimated time:
Affected Version:
Appion/Leginon 2.2.0
Show in known bugs:
No
Workaround:

Description

There is a query in the CTF results page that is giving me problems. It is extremely slow, and not only that, seems to lock the AcquisitionImageData table which can block data collection.

SELECT ctfdata.* 
FROM ( SELECT i.`filename`, 
              i.`DEF_id` AS imageid, 
              p.`name`, 
              s.`defocus`, 
              unix_timestamp(i.`DEF_timestamp`) as unix_timestamp, 
              2*ABS(ctf.defocus1-ctf.defocus2)/(ctf.defocus1+ctf.defocus2) AS difference, 
              ctf.* 
       FROM `ApCtfData` AS ctf 
       LEFT JOIN leginondb.`AcquisitionImageData` AS i ON (i.`DEF_id` = ctf.`REF|leginondata|AcquisitionImageData|image`) 
       LEFT JOIN leginondb.`PresetData` AS p ON (p.DEF_id = i.`REF|PresetData|preset`) 
       LEFT JOIN leginondb.`ScopeEMData` AS s ON (i.`REF|ScopeEMData|scope` = s.`DEF_id`) 
       LEFT JOIN leginondb.`ViewerImageStatus` AS viewer ON viewer.`REF|AcquisitionImageData|image` = ctf.`REF|leginondata|AcquisitionImageData|image` 
       WHERE ctf.`REF|leginondata|AcquisitionImageData|image` IN ( SELECT a.`DEF_id` 
                                                                                                         FROM leginondb.`AcquisitionImageData` AS a 
                                                                                                         WHERE a.`REF|SessionData|session`='6395' 
                                                                                                         AND ABS(s.`SUBD|stage position|a`) < 0.9 
                                                                                                         AND ( viewer.status IS NULL OR viewer.status != 'hidden' ) ) 
       ORDER BY coalesce(resolution_80_percent,999) ASC , 
                       coalesce(resolution_50_percent,999) ASC, 
                       coalesce(ctf.`cross_correlation`,(ctf.`confidence`*ctf.`confidence_d`),999) DESC ) ctfdata 
 WHERE ctfdata.confidence > 0.2 || ctfdata.confidence_d > 0.2 
 GROUP BY ctfdata.imageid 
 ORDER BY ctfdata.unix_timestamp
Actions
Copy link
 #1

Updated by Anonymous over 10 years ago

getCTFStats in particledata.inc also generates a fairly slow query.

It turns out we were having problems with a particularly large ap* database that still had some legacy indexes. I manually added new indexes and the query completes 'fast enough' now.

OTH, I experimented with some alternate versions of the query that were much, much faster by avoiding all the sub-selections, etc. I wasn't sure they were a 100% drop-in replacement so I didn't change the code, but in general I think these queries could be a lot more efficient which would be good, since they cause MyISAM tables to get locked from updates/writes.

Actions
Copy link
 #2

Updated by Neil Voss over 10 years ago

  • Affected Version changed from Appion/Leginon 2.1.0 to Appion/Leginon 2.2.0

I played around with this, but this is something Denis wrote that has been patched.

Actions
Copy link

Also available in: Atom PDF