Changeset 61470 in vbox for trunk/src/VBox/ValidationKit/testmanager/core/testresults.py

Timestamp:

Jun 5, 2016 4:06:40 AM (9 years ago)

Author:

vboxsync

Message:

testresults.py: apparently left outer joins are more pleasing to the postgresql setup on tindertux. doesn't matter for unique foreign columns.

File:

• trunk/src/VBox/ValidationKit/testmanager/core/testresults.py (modified) (5 diffs)

trunk/src/VBox/ValidationKit/testmanager/core/testresults.py

@@ -973 +973 @@
                   '          LIMIT %s OFFSET %s\n' % (cMaxRows, iStart,);
 
+        # Note! INNER JOIN TestBoxesWithStrings performs miserable compared to LEFT OUTER JOIN. Doesn't matter for the result
+        #       because TestSets.idGenTestBox is a foreign key and unique in TestBoxes.  So, let's do what ever is faster.
         sQuery += '       ) AS TestSets\n' \
-                  '            INNER JOIN TestBoxesWithStrings\n' \
+                  '       LEFT OUTER JOIN TestBoxesWithStrings\n' \
                   '                    ON TestSets.idGenTestBox     = TestBoxesWithStrings.idGenTestBox' \
                   '       LEFT OUTER JOIN Builds AS TestSuiteBits\n' \
@@ -1180 +1182 @@
         found in all test results.
         """
-
+        # Note! INNER JOIN TestBoxesWithStrings performs miserable compared to LEFT OUTER JOIN. Doesn't matter for the result
+        #       because TestSets.idGenTestBox is a foreign key and unique in TestBoxes.  So, let's do what ever is faster.
         self._oDb.execute('SELECT TestBoxesWithStrings.*\n'
                           'FROM   ( SELECT idTestBox         AS idTestBox,\n'
@@ -1188 +1191 @@
                           '         GROUP BY idTestBox\n'
                           '       ) AS TestBoxIDs\n'
-                          '       INNER JOIN TestBoxesWithStrings\n'
-                          '               ON TestBoxesWithStrings.idGenTestBox = TestBoxIDs.idGenTestBox\n'
+                          '       LEFT OUTER JOIN TestBoxesWithStrings\n'
+                          '                    ON TestBoxesWithStrings.idGenTestBox = TestBoxIDs.idGenTestBox\n'
                           'ORDER BY TestBoxesWithStrings.sName\n' );
         aoRet = []
@@ -1202 +1205 @@
         """
 
+        # Using LEFT OUTER JOIN instead of INNER JOIN in case it performs better, doesn't matter for the result.
         self._oDb.execute('SELECT TestCases.*\n'
                           'FROM   ( SELECT idTestCase         AS idTestCase,\n'
@@ -1209 +1213 @@
                           '         GROUP BY idTestCase\n'
                           '       ) AS TestCasesIDs\n'
-                          '       INNER JOIN TestCases ON TestCases.idGenTestCase = TestCasesIDs.idGenTestCase\n'
+                          '       LEFT OUTER JOIN TestCases ON TestCases.idGenTestCase = TestCasesIDs.idGenTestCase\n'
                           'ORDER BY TestCases.sName\n' );
+
         aoRet = [];
         for aoRow in self._oDb.fetchAll():