Changeset 6716

Timestamp:

Feb 4, 2015, 12:52:11 PM (9 years ago)

Author:

Nicklas Nordborg

Message:

Fixes #1917: Improve performance of AnnotationSet?.deleteEmptyAnnotationSets()

Changed the approach to finding empty annotation sets. Instead of using a main query with a set of subquires an initial query with a single left join is used. This gives us a list of potentially empty annotation sets. This list is then further narrowed down by executing additional queries (that also take the parameter limitation findings from #1914 into account). What remains in the list is then used for deleting.

The new implementation runs in less than a second on the same data set that previously took 40 minutes!

File:

• branches/3.4-stable/src/core/net/sf/basedb/core/AnnotationSet.java (modified) (4 diffs)

branches/3.4-stable/src/core/net/sf/basedb/core/AnnotationSet.java

@@ -23 +23 @@
 package net.sf.basedb.core;
 
+import java.util.ArrayList;
 import java.util.Collection;
 import java.util.Collections;
@@ -45 +46 @@
 import net.sf.basedb.core.snapshot.AnnotationTypeFilter;
 import net.sf.basedb.core.snapshot.SnapshotManager;
-
 import net.sf.basedb.core.data.AnnotationData;
 import net.sf.basedb.core.data.AnnotationSetData;
 import net.sf.basedb.core.data.AnnotationTypeData;
+import net.sf.basedb.core.hibernate.TypeWrapper;
 import net.sf.basedb.util.AnnotationUtil;
 import net.sf.basedb.util.filter.Filter;
@@ -203 +204 @@
   }
   
+  private static final int MAX_PARAMETERS_IN_QUERY = 10000;
   
   /**
@@ -219 +221 @@
     if (progress != null)
     {
-      progress.display(5, "Counting empty annotation sets...");
-    }
-    
-    String filter = "@COL not in (select [annotationset_id] from [Annotations])"+
-        " and @COL not in (select [annotationset_id] from [InheritedAnnotations])"+
-        " and @COL not in (select [annotationset_id] from [InheritedAnnotationSets])"+
-        " and @COL not in (select [inherited_id] from [InheritedAnnotationSets])";
-
-    // Count number of empty annotation sets
-    String countSql = "select count(*), [item_type] from [AnnotationSets] where "+
-        filter.replace("@COL", "[id]") + 
-        " group by [item_type]";
-    org.hibernate.Query countQuery = HibernateUtil.createSqlQuery(session, countSql);
-    List<Object[]> counts = (List<Object[]>)countQuery.list();
-    long totalCount = 0;
-    Map<String, Long> tables = new HashMap<String, Long>();
-    for (Object[] row : counts)
-    {
-      long count = ((Number)row[0]).longValue();
-      totalCount += count;
+      progress.display(5, "Loading annotation sets with no primary annotation...");
+    }
+    
+    // This SQL select annotationset id and type of item for annotation sets
+    // that has no primary annotations
+    String sql = "select [aa].[id], [aa].[item_type] from [AnnotationSets] [aa]"+
+        " left join [Annotations] [a] on [a].[annotationset_id]=[aa].[id]"+
+        " where [a].[annotationset_id] is null";
+    
+    org.hibernate.Query query = HibernateUtil.createSqlQuery(session, sql);
+    Map<Integer, Integer> possibleAnnotationSets = new HashMap<Integer, Integer>(10000);
+    List<Integer> allIds = new ArrayList<Integer>(10000);
+    for (Object[] row : (List<Object[]>)query.list())
+    {
+      Integer id = (Integer)row[0];
+      possibleAnnotationSets.put(id, (Integer)row[1]);
+      allIds.add(id);
+    }
+    if (progress != null)
+    {
+      progress.display(25, "Found " + possibleAnnotationSets.size() + " annotation sets without primary annotation");
+    }
+    if (possibleAnnotationSets.size() == 0) return 0;
+    
+    if (progress != null)
+    {
+      progress.display(30, "Checking for inherited annotations..." );
+    }
+    
+    // These SQL load annotation set ids that exists in one of three columns/tables
+    // Anything we find in theese tables must be removed from the possibleAnnotationSets map
+    String sql1 = "select [annotationset_id] from [InheritedAnnotations] where [annotationset_id] in (:ids)";
+    String sql2 = "select [annotationset_id] from [InheritedAnnotationSets] where [annotationset_id] in (:ids)";
+    String sql3 = "select [inherited_id] from [InheritedAnnotationSets] where [inherited_id] in (:ids)";
+    org.hibernate.Query query1 = HibernateUtil.createSqlQuery(session, sql1);
+    org.hibernate.Query query2 = HibernateUtil.createSqlQuery(session, sql2);
+    org.hibernate.Query query3 = HibernateUtil.createSqlQuery(session, sql3);
+    
+    // Divide the query into chunks since the database driver may not support
+    // an arbitrary large number of parameters
+    int startIndex = 0;
+    int endIndex = Math.min(MAX_PARAMETERS_IN_QUERY, allIds.size());
+    while (startIndex < allIds.size())
+    {
+      List<Integer> sublist = allIds.subList(startIndex, endIndex);
+      query1.setParameterList("ids", sublist, TypeWrapper.INTEGER.getHibernateType());
+      query2.setParameterList("ids", sublist, TypeWrapper.INTEGER.getHibernateType());
+      query3.setParameterList("ids", sublist, TypeWrapper.INTEGER.getHibernateType());
       
-      Item itemType = Item.fromValue((Integer)row[1]);
+      for (Integer id : (List<Integer>)query1.list())
+      {
+        possibleAnnotationSets.remove(id);
+      }
+      for (Integer id : (List<Integer>)query2.list())
+      {
+        possibleAnnotationSets.remove(id);
+      }
+      for (Integer id : (List<Integer>)query3.list())
+      {
+        possibleAnnotationSets.remove(id);
+      }
+    
+      startIndex = endIndex;
+      endIndex = Math.min(startIndex + MAX_PARAMETERS_IN_QUERY, allIds.size());
+    }
+    
+    if (progress != null)
+    {
+      progress.display(50, "Found " + possibleAnnotationSets.size() + " annotations sets without primary or inherited annotations");
+    }
+    if (possibleAnnotationSets.size() == 0) return 0;
+    
+    // Divide the remaining annotation set ids into one list per table to delete from
+    Map<String, List<Integer>> tables = new HashMap<String, List<Integer>>();
+    for (Map.Entry<Integer, Integer> entry : possibleAnnotationSets.entrySet())
+    {
+      Integer id = entry.getKey();
+      Item itemType = Item.fromValue(entry.getValue());
+      
       PersistentClass pClass = HibernateUtil.getClassMapping(itemType.getDataClass().getName());
       String table = pClass.getTable().getName();
+      List<Integer> idsInTable = tables.get(table);
+      if (idsInTable == null)
+      {
+        idsInTable = new ArrayList<Integer>();
+        tables.put(table, idsInTable);
+      }
+      idsInTable.add(id);
+    }
+    
+    int totalCount = possibleAnnotationSets.size();
+    // Now it is finally time to delete from the "AnnotationSets" table
+    // and nullify the reference from the item tables
+    String deleteSql = "delete from [AnnotationSets] where [id] in (:ids)";
+    org.hibernate.Query deleteQuery = HibernateUtil.createSqlQuery(session, deleteSql);
+    
+    for (Map.Entry<String, List<Integer>> entry : tables.entrySet())
+    {
+      String table = entry.getKey();
+      List<Integer> ids = entry.getValue();
+
+      String nullifySql = "update [" + table + "] set [annotationset_id] = null where [annotationset_id] in (:ids)";
+      org.hibernate.Query nullifyQuery = HibernateUtil.createSqlQuery(session, nullifySql);
+
+      startIndex = 0;
+      endIndex = Math.min(MAX_PARAMETERS_IN_QUERY, ids.size());
       
-      if (tables.containsKey(table)) count += tables.get(table);
-      tables.put(table, count);
-    }
-    if (progress != null) progress.append(" [" + totalCount + "]");
-      
-    // Unlink (set annotationset_id=null) on item tables
-    long numUnlinked = 0;
-    for (Map.Entry<String, Long> entry : tables.entrySet())
-    {
-      ThreadSignalHandler.checkInterrupted();
-      Long count = entry.getValue();
-      if (count > 0)
-      {
-        String table = entry.getKey();
+      while (startIndex < ids.size())
+      {
+        List<Integer> sublist = ids.subList(startIndex, endIndex);
+        
         if (progress != null)
         {
-          int percent = (int)(10+(60*numUnlinked)/totalCount);
-          progress.display(percent, "Unlinking " + count + " empty annotation sets from " + table + " table...");
+          int percent = (int)(60+(30*numDeleted)/totalCount);
+          progress.display(percent, "Unlinking " + ids.size() + " empty annotation sets from [" + table + "] table...");
         }
-        String nullifySql = "update [" + table + "] set [annotationset_id] = null where "+filter.replace("@COL", "[annotationset_id]");
-        org.hibernate.Query nullifyQuery = HibernateUtil.createSqlQuery(session, nullifySql);
-        int rowsUpdated = nullifyQuery.executeUpdate();
-        if (progress != null) progress.append(" ["+rowsUpdated + "]");
-        numUnlinked += rowsUpdated;
-      }
-    }
-      
-    if (totalCount > 0)
-    {
-      if (progress != null)
-      {
-        progress.display(75, "Deleting " + totalCount + " empty annotation sets...");
-      }
-      
-      String deleteSql = "delete from [AnnotationSets] where "+filter.replace("@COL", "[id]");
-      org.hibernate.Query deleteQuery = HibernateUtil.createSqlQuery(session, deleteSql);
-      numDeleted = deleteQuery.executeUpdate();
-      if (progress != null) progress.append(" ["+numDeleted + "]");
-    }
-      
+        
+        deleteQuery.setParameterList("ids", sublist, TypeWrapper.INTEGER.getHibernateType());
+        nullifyQuery.setParameterList("ids", sublist, TypeWrapper.INTEGER.getHibernateType());
+        nullifyQuery.executeUpdate();
+        deleteQuery.executeUpdate();
+        
+        numDeleted += sublist.size();
+        startIndex = endIndex;
+        endIndex = Math.min(startIndex + MAX_PARAMETERS_IN_QUERY, ids.size());
+      }
+    }
+    
+    if (progress != null)
+    {
+      progress.display(100, "Deleted " + numDeleted + " unused annotation sets");
+    }
+    
     return numDeleted;
   }