search

leginon-org / leginon-redmine-archive

1 stars 0 forks source link

very slow SQL query in CTF results page #2747

Open leginonbot opened 8 months ago

leginonbot commented 8 months ago

Author Name: Anonymous (Anonymous) Original Redmine Issue: 2747, https://emg.nysbc.org/redmine/issues/2747 Original Date: 2014-04-28

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
leginonbot commented 8 months ago

Original Redmine Comment Author Name: Anonymous (Anonymous) Original Date: 2014-04-28T21:03:07Z

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.

leginonbot commented 8 months ago

Original Redmine Comment Author Name: Neil Voss (@vosslab) Original Date: 2014-07-16T19:55:03Z

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