Changeset 20841 for trunk/ippTools/share/magicdstool_completed_runs.sql

Timestamp:

Nov 25, 2008, 5:21:13 PM (17 years ago)

Author:

bills

Message:

Add the stage_id and cam_id to magicDSRun. This vastly reduces the
number of JOINs that we have to do

File:

• trunk/ippTools/share/magicdstool_completed_runs.sql (modified) (6 diffs)

trunk/ippTools/share/magicdstool_completed_runs.sql

@@ -7 +7 @@
     magicDSRun.magic_ds_id
     FROM magicDSRun
-    JOIN magicRun USING (magic_id)
-    JOIN magicInputSkyfile USING(magic_id)
-    JOIN diffRun USING(diff_id)
-    JOIN diffInputSkyfile
-        ON diffInputSkyfile.diff_id = diffRun.diff_id
-        AND diffInputSkyfile.skycell_id = diffRun.skycell_id
-        -- Want input warps only AND diffInputSkyfile.warp_id IS NOT NULL
-        AND diffInputSkyfile.template = 0
-    JOIN warpRun USING(warp_id)
-    JOIN fakeRun USING(fake_id)
-    JOIN camRun USING(cam_id)
-    JOIN camProcessedExp USING(cam_id)
-    JOIN rawImfile ON magicRun.exp_id = rawImfile.exp_id
+    JOIN rawImfile ON stage_id = rawImfile.exp_id
     LEFT JOIN magicDSFile
         ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
@@ -37 +25 @@
     magicDSRun.magic_ds_id
     FROM magicDSRun
-    JOIN magicRun USING(magic_id)
-    JOIN magicInputSkyfile USING(magic_id)
-    JOIN diffRun USING(diff_id)
-    JOIN diffInputSkyfile
-        ON diffInputSkyfile.diff_id = diffRun.diff_id
-        AND diffInputSkyfile.skycell_id = diffRun.skycell_id
-        -- Want input warps only
-        AND diffInputSkyfile.warp_id IS NOT NULL
-        AND diffInputSkyfile.template = 0
-    JOIN warpRun USING(warp_id)
-    JOIN fakeRun USING(fake_id)
-    JOIN camRun USING(cam_id)
-    JOIN camProcessedExp USING(cam_id)
-    JOIN chipRun USING(chip_id)
-    JOIN chipProcessedImfile USING(chip_id)
-
+    JOIN chipProcessedImfile ON stage_id = chip_id
     LEFT JOIN magicDSFile
         ON magicDSFile.magic_ds_id = magicDSRun.magic_ds_id
@@ -60 +33 @@
         AND magicDSRun.stage = 'chip'
     GROUP BY
-        magicDSRun.magic_ds_id,
-        chipRun.chip_id
+        magic_ds_id,
+        chip_id
     HAVING
         COUNT(chipProcessedImfile.class_id) = COUNT(magicDSFile.component)
@@ -70 +43 @@
     magicDSRun.magic_ds_id
     FROM magicDSRun
-    JOIN magicRun USING (magic_id)
-    JOIN magicInputSkyfile USING(magic_id)
-    JOIN diffRun USING(diff_id)
-    JOIN diffInputSkyfile
-        ON diffInputSkyfile.diff_id = diffRun.diff_id
-        AND diffInputSkyfile.skycell_id = diffRun.skycell_id
-        -- Want input warps only
-        AND diffInputSkyfile.warp_id IS NOT NULL
-        AND diffInputSkyfile.template = 0
-    JOIN warpSkyfile using(warp_id)
-    JOIN warpRun USING(warp_id)
+    JOIN warpSkyfile on stage_id = warp_id
     LEFT JOIN magicDSFile
         ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
@@ -91 +54 @@
     GROUP BY
         magicDSRun.magic_ds_id,
-        warpRun.warp_id
+        warp_id
     HAVING
         COUNT(warpSkyfile.skycell_id) = COUNT(magicDSFile.component)
@@ -102 +65 @@
     JOIN magicRun USING (magic_id)
     JOIN magicInputSkyfile USING(magic_id)
+    -- Do we really need to join back to diffInputSkyfile here?
     JOIN diffRun USING(diff_id)
     JOIN diffSkyfile USING(diff_id)