Changeset 39528 for trunk/ippTools/share

Timestamp:

Apr 13, 2016, 11:38:40 AM (10 years ago)

Author:

heather

Message:

addtool is slow, the queries needed some optimization, this is much faster

Location:

trunk/ippTools/share

Files:

• addtool_checkminidvodbaddrun.sql (modified) (1 diff)
• addtool_find_diff_id_dvo.sql (modified) (1 diff)

trunk/ippTools/share/addtool_checkminidvodbaddrun.sql

@@ -15 +15 @@
         -- the second select grabs and counts all the addRuns with state
         -- of 'full'         
-   WHERE addRun.state = 'full' and minidvodb_group = minidbg
+   WHERE addRun.state = 'full' and minidvodb_group = '%s'
    GROUP BY minidvodb_name ) 
 AS foo2 

trunk/ippTools/share/addtool_find_diff_id_dvo.sql

@@ -12 +12 @@
                    JOIN rawExp using (exp_id) 
 WHERE diffRun.state = 'full' and diffSkyfile.quality = 0
-    AND diffRun.diff_id NOT IN (SELECT diffRun.diff_id
-       FROM addRun
-       JOIN diffRun on (diffRun.diff_id = addRun.stage_id and addRun.stage = 'diff') JOIN diffSkyfile on (diffRun.diff_id = diffSkyfile.diff_id)
+    AND diffInputSkyfile.diff_skyfile_id NOT IN (SELECT diffInputSkyfile.diff_skyfile_id
+    FROM 
+         diffInputSkyfile  
+    JOIN diffSkyfile     USING (diff_id, skycell_id) 
+    JOIN diffRun     using(diff_id, tess_id) 
+    JOIN skycell using (skycell_id, tess_id) 
+    left join addRun  ON (diff_id = stage_id and diff_skyfile_id = stage_extra1 and stage = "diff")
        WHERE addRun.stage = 'diff' AND %s
       )