Changeset 39528 for trunk/ippTools/share/addtool_find_diff_id_dvo.sql

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

File:

• trunk/ippTools/share/addtool_find_diff_id_dvo.sql (modified) (1 diff)

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
       )