Changeset 7912


Ignore:
Timestamp:
Feb 18, 2021, 8:02:06 AM (10 months ago)
Author:
Nicklas Nordborg
Message:

References #2237: Implement extension mechanism for query filtering

Implemented functionality for creating a temporary table with ID values that should improve speed when executing queries with IN or NOT IN having a large list of values. The IdListRestriction has been updated to use this functionality when the list if bigger than 1000 items.

Location:
trunk/src/core/net/sf/basedb
Files:
5 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/core/net/sf/basedb/core/DbControl.java

    r7715 r7912  
    3232import net.sf.basedb.core.data.GroupData;
    3333import net.sf.basedb.core.data.UserData;
     34import net.sf.basedb.core.dbengine.DbEngine;
    3435import net.sf.basedb.core.hibernate.SessionWrapper;
    3536import net.sf.basedb.core.hibernate.StatelessSessionWrapper;
     
    3940import net.sf.basedb.core.log.ManualLogEntry;
    4041import net.sf.basedb.util.ClassUtil;
     42import net.sf.basedb.util.FileUtil;
    4143import net.sf.basedb.util.extensions.ClientContext;
    4244import net.sf.basedb.util.extensions.ExtensionsInvoker;
     
    5961import java.lang.reflect.Constructor;
    6062import java.lang.reflect.InvocationTargetException;
     63import java.sql.PreparedStatement;
     64import java.sql.SQLException;
    6165
    6266/**
     
    12731277 
    12741278  /**
     1279    Create a temporary table for holding given list of ID values.
     1280    The intention is that this should be used in a query to speed
     1281    up the execution. Note that not all databases may support this
     1282    feature and that alternate solutions are needed. The temporary
     1283    table is automatically deleted when the transaction ends.
     1284   
     1285    @param idList List of ID values that should be inserted
     1286    @return The name of the temporary table or null if not supported
     1287    @since 3.18
     1288  */
     1289  public String createTempIdTable(Set<Integer> idList)
     1290  {
     1291    String tableName = "Tmp"+uniqueRandom();
     1292    DbEngine dbEngine = HibernateUtil.getDbEngine();
     1293    String createSql = dbEngine.getCreateTemporaryIdTable(tableName);
     1294    if (createSql == null) return null;
     1295   
     1296    PreparedStatement insert = null;
     1297    try
     1298    {
     1299      org.hibernate.Session session = getHibernateSession();
     1300      HibernateUtil.createSqlQuery(session, createSql).executeUpdate();
     1301     
     1302      String insertSql = "INSERT INTO " + dbEngine.getQuotedName(tableName) +
     1303        " ("+dbEngine.getQuotedName("id")+") VALUES (?)";
     1304      insert = HibernateUtil.getConnection(session).prepareStatement(insertSql);
     1305     
     1306      for (Integer id : idList)
     1307      {
     1308        insert.setInt(1, id);
     1309        insert.addBatch();
     1310      }
     1311      insert.executeBatch();
     1312    }
     1313    catch (SQLException ex)
     1314    {
     1315      throw new DatabaseException(ex);
     1316    }
     1317    finally
     1318    {
     1319      FileUtil.close(insert);
     1320    }
     1321    return tableName;
     1322  }
     1323 
     1324  /**
    12751325    Main purpose is to write a warning to the log file in case some user code that
    12761326    created a DbControl instance never called the close() method before the DbControl
  • trunk/src/core/net/sf/basedb/core/dbengine/DbEngine.java

    r7263 r7912  
    303303  */
    304304  public String makeSafeCreateTable(String sql, String catalog, String schema, String table);
     305 
     306  /**
     307    Generate SQL for creating a temporary table with a single 'id' column that is
     308    the primary key. The table need to be automatically deleted
     309    when the transaction ends. If the database doesn't support this
     310    null should be returned.
     311    Note! This method is experiment and may change in a future API.
     312    A default implementation that return null is provided.
     313   
     314    @param table The name of the temporary table
     315    @return SQL statement or null
     316    @since 3.18
     317  */
     318  public default String getCreateTemporaryIdTable(String table)
     319  {
     320    return null;
     321  }
     322 
    305323 
    306324  /**
  • trunk/src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java

    r7263 r7912  
    255255    return "ANALYZE " + schema + getQuotedName(table);
    256256  }
     257 
     258  /**
     259    CREATE TEMPORARY TABLE &lt;table&gt; (id integer PRIMARY KEY) ON COMMIT DROP
     260    @since 3.18
     261  */
     262  @Override
     263  public String getCreateTemporaryIdTable(String table)
     264  {
     265    return "CREATE TEMPORARY TABLE " + getQuotedName(table) +
     266        " (" + getQuotedName("id") + " integer PRIMARY KEY) ON COMMIT DROP";
     267  }
     268
     269 
    257270  /**
    258271    Returns FALSE.
  • trunk/src/core/net/sf/basedb/core/query/IdListRestriction.java

    r7911 r7912  
    4646  private Set<Integer> idList;
    4747  private final Expression idExpression;
     48  private String tmpTable;
    4849 
    4950  public IdListRestriction()
     
    7879  public String toQl(Query query, DbControl dc)
    7980  {
    80     String idString = idList.size() == 0 ? "0" : Values.getString(idList, ", ", true);
    81     return "(" + idExpression.toQl(query, dc) + (notIn ? " NOT IN " : " IN ") +"(" + idString + "))";
     81    if (tmpTable == null && idList.size() > 1000)
     82    {
     83      tmpTable = dc.createTempIdTable(idList);
     84    }
     85    String hql = idExpression.toQl(query, dc) + (notIn ? " NOT IN (" : " IN (");
     86    if (tmpTable != null)
     87    {
     88      hql += "native('select [id] from [" + tmpTable + "]')";
     89    }
     90    else
     91    {
     92      hql += idList.size() == 0 ? "0" : Values.getString(idList, ", ", true);
     93    }
     94    return "("+ hql + "))";
    8295  }
    8396
  • trunk/src/core/net/sf/basedb/util/FileUtil.java

    r7310 r7912  
    243243  }
    244244
     245  /**
     246    Close a {@link AutoCloseable} without throwing an exception.
     247    @param c The object to close
     248    @since 3.18
     249  */
     250  public static void close(AutoCloseable c)
     251  {
     252    if (c == null) return;
     253    try
     254    {
     255      c.close();
     256    }
     257    catch (Throwable t)
     258    {}
     259  }
    245260 
    246261  /**
Note: See TracChangeset for help on using the changeset viewer.