id,summary,reporter,owner,description,type,status,priority,milestone,component,version,resolution,keywords,cc 797,Enhance performance for LOWESS and Medin-ratio plug-ins,Nicklas Nordborg,Nicklas Nordborg,"The performance tests (#294) shows that the LOWESS plug-in has bad performance. It is does not gain much if the indexes are dropped (#796). I think the problem is that the number of selects grows when the ""blockgroup size"" parameter get lower. Below are the SQL that is executed as part of the LOWESS plug-in. We should also check if the Median-ratio plug-in has the same behaviour. {{{ -- Get min and max block - one time only ~ 1s SELECT MAX(`raw`.`block`) AS `max`, MIN(`raw`.`block`) AS `min` FROM `base2dynamic`.`D4Spot` `spt` INNER JOIN `base2dynamic`.`D4Filter` AS `flt` ON `flt`.`cube` = `spt`.`cube` AND `flt`.`column` = `spt`.`column` AND `flt`.`position` = `spt`.`position` LEFT JOIN `base2dynamic`.`D4RawParents` AS `rawmap` ON `rawmap`.`cube` = `spt`.`cube` AND `rawmap`.`column` = `spt`.`column` AND `rawmap`.`position` = `spt`.`position` LEFT JOIN `RawDataGenePix` AS raw ON raw.id = rawmap.`rawdata_id` WHERE `spt`.`cube` = 1 AND `spt`.`layer` = 1 AND `flt`.`filter` = 2 AND `spt`.`column` = 1 AND ((`spt`.`ch1` > 0.0) AND (`spt`.`ch2` > 0.0)) -- Count number of spots in block group - one time for each block group ~ 0,5 s SELECT COUNT(*) FROM `base2dynamic`.`D4Spot` `spt` INNER JOIN `base2dynamic`.`D4Filter` AS `flt` ON `flt`.`cube` = `spt`.`cube` AND `flt`.`column` = `spt`.`column` AND `flt`.`position` = `spt`.`position` LEFT JOIN `base2dynamic`.`D4RawParents` AS `rawmap` ON `rawmap`.`cube` = `spt`.`cube` AND `rawmap`.`column` = `spt`.`column` AND `rawmap`.`position` = `spt`.`position` LEFT JOIN `RawDataGenePix` AS raw ON raw.id = rawmap.`rawdata_id` WHERE `spt`.`cube` = 1 AND `spt`.`layer` = 1 AND `flt`.`filter` = 2 AND `spt`.`column` = 1 AND ((`spt`.`ch1` > 0.0) AND (`spt`.`ch2` > 0.0)) AND (1 <= `raw`.`block` AND `raw`.`block` < 2) -- Load A and M for each spot in spot group - one time for each block group ~ 2s SELECT (LOG((`spt`.`ch1` / `spt`.`ch2`)) / 0.6931471805599453) AS `m`, (LOG(SQRT((`spt`.`ch1` * `spt`.`ch2`))) / 2.302585092994046) AS `a`, `raw`.`block` AS `block` FROM `base2dynamic`.`D4Spot` `spt` INNER JOIN `base2dynamic`.`D4Filter` AS `flt` ON `flt`.`cube` = `spt`.`cube` AND `flt`.`column` = `spt`.`column` AND `flt`.`position` = `spt`.`position` LEFT JOIN `base2dynamic`.`D4RawParents` AS `rawmap` ON `rawmap`.`cube` = `spt`.`cube` AND `rawmap`.`column` = `spt`.`column` AND `rawmap`.`position` = `spt`.`position` LEFT JOIN `RawDataGenePix` AS raw ON raw.id = rawmap.`rawdata_id` WHERE `spt`.`cube` = 1 AND `spt`.`layer` = 1 AND `flt`.`filter` = 2 AND `spt`.`column` = 1 AND ((`spt`.`ch1` > 0.0) AND (`spt`.`ch2` > 0.0)) AND (1 <= `raw`.`block` AND `raw`.`block` < 2) ORDER BY a ASC -- Load ch(1), ch(2) and A for each spot in spot group - one time for each block group ~ 1s SELECT `spt`.`column` AS `column`, `spt`.`position` AS `position`, `spt`.`ch1` AS `ch1`, `spt`.`ch2` AS `ch2`, (LOG(SQRT((`spt`.`ch1` * `spt`.`ch2`))) / 2.302585092994046) AS `a` FROM `base2dynamic`.`D4Spot` `spt` INNER JOIN `base2dynamic`.`D4Filter` AS `flt` ON `flt`.`cube` = `spt`.`cube` AND `flt`.`column` = `spt`.`column` AND `flt`.`position` = `spt`.`position` LEFT JOIN `base2dynamic`.`D4RawParents` AS `rawmap` ON `rawmap`.`cube` = `spt`.`cube` AND `rawmap`.`column` = `spt`.`column` AND `rawmap`.`position` = `spt`.`position` LEFT JOIN `RawDataGenePix` AS raw ON raw.id = rawmap.`rawdata_id` WHERE `spt`.`cube` = 1 AND `spt`.`layer` = 1 AND `flt`.`filter` = 2 AND `spt`.`column` = 1 AND ((`spt`.`ch1` > 0.0) AND (`spt`.`ch2` > 0.0)) AND (1 <= `raw`.`block` AND `raw`.`block` < 2) ORDER BY a ASC }}} ",enhancement,closed,critical,BASE 2.5,coreplugins,,fixed,,