Changeset 39151 for trunk/ippToPsps/jython/forcedgalaxybatch.py

Timestamp:

Nov 17, 2015, 12:55:41 PM (11 years ago)

Author:

eugene

Message:

add index for forced galaxy batches

File:

• trunk/ippToPsps/jython/forcedgalaxybatch.py (modified) (5 diffs)

trunk/ippToPsps/jython/forcedgalaxybatch.py

@@ -72 +72 @@
 
     '''
+    Generate ippObjID and make it an index 
+    '''
+    def addIppObjID (self, tableName):
+        sql = "ALTER TABLE " + tableName + " ADD COLUMN ippObjID BIGINT"
+        self.scratchDb.execute(sql)
+        
+        sql = "CREATE INDEX ippObjID_index ON " + tableName + " ( ippObjID ) ";
+        self.scratchDb.execute(sql)
+
+        sql = "UPDATE " + tableName + " set ippObjID = (OBJ_ID + (CAT_ID << 32))"
+        self.scratchDb.execute(sql)
+
+    '''
     Overriden from batch base class to import directly from DVO for forced galaxy shape parameters
     '''
@@ -101 +114 @@
             self.logger.infoPair("0 rows for ","cpq table")
             return False
+
+        # add ippObjID columns and index them:
+        self.addIppObjID (cptTableName)
+        self.addIppObjID (cpqTableName)
+
         return True
 
@@ -178 +196 @@
             # sqlLine.group("a." + filter[1] + "GalDecErr",       "b.DEC_ERR")
             
-            sql = sqlLine.makeEquals("WHERE a.ippObjID = (b.OBJ_ID + (b.CAT_ID << 32)) AND a.galModelType = b.MODEL_TYPE AND b.Photcode = " + str(photcode))
+            sql = sqlLine.makeEquals("WHERE a.ippObjID = b.ippObjID AND a.galModelType = b.MODEL_TYPE AND b.Photcode = " + str(photcode))
             
             self.logger.debugPair("cpq sql: ", sql)
             
             self.scratchDb.execute(sql)
+
+            ## # check on the status as we go:
+            ## sql = "select count(*) as N, galModelType, nFilter, count(*)*nFilter as S from ForcedGalaxyShape group by galModelType, nFilter"
+            ## rs = self.scratchDb.executeQuery(sql)  
+            ## 
+            ## while rs.next():
+            ##     N = rs.getInt(1)
+            ##     M = rs.getInt(2)
+            ##     F = rs.getInt(3)
+            ##     S = rs.getInt(4)
+            ##     
+            ##     print "N: %d, TYPE: %d, nFilter: %d, Sum: %d" % (N, M, F, S)
 
     '''
@@ -207 +237 @@
 
             sqlLine.group("objID",             "EXT_ID")
-            sqlLine.group("ippObjID",          "OBJ_ID + (CAT_ID << 32)")
+            sqlLine.group("ippObjID",          "ippObjID")
             sqlLine.group("surveyID",          str(self.surveyID))
             sqlLine.group("randomForcedGalID", "RAND("+str(self.batchID)+")")
@@ -215 +245 @@
             sql = sqlLine.makeRaw(") SELECT ", " FROM " + cptTableName)
 
-            print "--- sql: " + sql
+            # print "--- sql: " + sql
 
             self.scratchDb.execute(sql)