Changeset 40827 for trunk

Timestamp:

Jul 3, 2019, 2:27:13 PM (7 years ago)

Author:

tdeboer

Message:

improve query speed of unpublished solar system exposure WW diffs

File:

• trunk/ippScripts/scripts/night_report.pl (modified) (2 diffs)

trunk/ippScripts/scripts/night_report.pl

@@ -699 +699 @@
     $query .= "  ) AS expall";
     $query .= "  LEFT JOIN";
-    $query .= "  (";
-    $query .= "    SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate";
-    $query .= "    FROM diffRun";
-    $query .= "    JOIN diffInputSkyfile using (diff_id)";
-    $query .= "    JOIN publishRun on (diffRun.diff_id = stage_id)";
-    $query .= "    JOIN warpRun ON ( warp1 = warp_id or warp2 = warp_id)";
-    $query .= "    JOIN fakeRun using (fake_id)";
-    $query .= "    JOIN camRun using (cam_id)";
-    $query .= "    JOIN camProcessedExp using (cam_id)";
-    $query .= "    JOIN chipRun using (chip_id)";
-    $query .= "    JOIN rawExp using (exp_id)";
-    $query .= "    WHERE stack2 IS NULL";
-##  $query .= "    AND exp_id > $refExpID"; -- adding this restriction here slows the query way down1
-    $query .= "    AND stack2 IS NULL";
-    $query .= "    AND diffRun.data_group LIKE '%.$dataGroupDate'";
-    $query .= "    GROUP BY exp_name";
-    $query .= "   ) AS exppub";
+    $query .= "  (SELECT * FROM (";
+    $query .= "  (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM";
+    $query .= "    diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)";
+    $query .= "    JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)";
+    $query .= "    JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
+    $query .= "    WHERE rawExp.dateobs like '$date%' AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
+    $query .= "    AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')";
+    $query .= "  UNION";
+    $query .= "  (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM";
+    $query .= "    diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)";
+    $query .= "    JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)";
+    $query .= "    JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
+    $query .= "    WHERE rawExp.dateobs like '$date%' AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
+    $query .= "    AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')";
+    $query .= "  ) AS diffgather";
+    $query .= "  GROUP BY diffgather.exp_name)";
+    $query .= "  AS exppub"; 
     $query .= " ON expall.exp_name = exppub.exp_name";
     $query .= " WHERE exppub.exp_name is NULL";
@@ -793 +793 @@
     $query .= "     GROUP BY groupobj.chunk";
     $query .= "   ) AS group4";
-    $query .= "   on groupchunk.chunk=group4.chunk";
+    $query .= "   on groupchunk.chunk=group4.chunk ORDER BY dateobs";
  
     my $result = &mysql_select ($query);