Hibernate selects identity column values before every insert

Question

I'm trying to insert 30 rows into the database, the ID generation strategy is IDENTITY and my database (Exasol) supports it. Before each insert, Hibernate selects identity value from the database, like this (from Hibernate logs):

Hibernate:
    SELECT
            COLUMN_IDENTITY 
        FROM
            EXA_ALL_COLUMNS 
        WHERE
            COLUMN_NAME='ENTRY_ID' 
            AND COLUMN_SCHEMA='TEST' 
            AND COLUMN_TABLE='CAMPAIGN'
Hibernate: 
    insert 
    into
        ecombi_mdm_test.CSV_ADCAMPAIGN
        (bla1, bla2, bla3, bla4, bla5, bla6, bla7, bla8, bla9, bla10, bla11, bla12, bla13, bla14, bla15, bla16, bla17, bla18, bla19, bla20, bla21, bla22, bla23, bla24, bla25, bla26, bla27, bla28, bla29, bla30) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate:
    SELECT
            COLUMN_IDENTITY 
        FROM
            EXA_ALL_COLUMNS 
        WHERE
            COLUMN_NAME='ENTRY_ID' 
            AND COLUMN_SCHEMA='TEST' 
            AND COLUMN_TABLE='CAMPAIGN'
Hibernate: 
    insert 
    into
        ecombi_mdm_test.CSV_ADCAMPAIGN
        (bla1, bla2, bla3, bla4, bla5, bla6, bla7, bla8, bla9, bla10, bla11, bla12, bla13, bla14, bla15, bla16, bla17, bla18, bla19, bla20, bla21, bla22, bla23, bla24, bla25, bla26, bla27, bla28, bla29, bla30) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

... and 28 more. In the end, it takes me 9 seconds to insert 30 rows.

This is how I configure my data source:

        configuration = new Configuration();
        configuration.setProperty("hibernate.connection.driver_class", properties.getProperty(...);
        configuration.setProperty("hibernate.connection.url", properties.getProperty(...);
        configuration.setProperty("hibernate.connection.username", properties.getProperty(...);
        configuration.setProperty("hibernate.connection.password", properties.getProperty(...);
        configuration.setProperty("hibernate.dialect", properties.getProperty(com.bla.exasol.ExasolDialect);
        configuration.setProperty("hibernate.show_sql", "true");
        configuration.setProperty("hibernate.format_sql", "true");
        configuration.setProperty("hibernate.default_schema", properties.getProperty(...);
        configuration.addAnnotatedClass(Some.class);

        ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
        SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

This is from my DAO class:

Session session = MultiTenantDBAccess.getSessionFactory().openSession();
Transaction transaction = null;

try {
    transaction = session.beginTransaction();
    for (T o : oList) {
        session.persist(o);
    }
    transaction.commit();
} catch (HibernateException e) {
    transaction.rollback();
    e.printStackTrace();
} finally {
    session.close();
}

And this is my DB's custom identity support class:

public class ExasolIdentityColumnSupport extends IdentityColumnSupportImpl {

    @Override
    public String appendIdentitySelectToInsert(String arg0) {
        return arg0;
    }

    @Override
    public String getIdentityColumnString(int type) throws MappingException {
        return type==Types.BIGINT ?
                "decimal(36, 0) identity not null" :
                "decimal(19, 0) identity not null";
    }

    @Override
    public String getIdentitySelectString(String table, String column, int type) throws MappingException {
        return "SELECT COLUMN_IDENTITY FROM EXA_ALL_COLUMNS WHERE COLUMN_NAME='"+column.toUpperCase()+"' AND COLUMN_SCHEMA='"+table.substring(0, table.indexOf(".")).toUpperCase()+"' AND COLUMN_TABLE='"+(table.substring(table.indexOf(".")+1)).toUpperCase()+"'";
    }

    @Override
    public boolean hasDataTypeInIdentityColumn() {
        return false;
    }

    @Override
    public boolean supportsIdentityColumns() {
        return true;
    }

    public boolean supportsInsertSelectIdentity() {
        return false;
    }

}

Is the problem in the fact that the DB I use doesn't support InsertSelectIdentity? Can't just Hibernate send 30 queries to DB without checking the id value before every insert? Can't I achieve in this case a better performance with Hibernate than 30 rows per 9 seconds?

score 1 · Answer 1 · answered Jan 21 '19 at 14:03

1

The id is needed because the entity is stored in the Session using the id as the key.

The only way to circumvent it is to use a database sequence if the DB supports using sequences.

answered Jan 21 '19 at 14:03

Vlad Mihalcea

142,745
71
566
911

So this is not a DB-specific issue? I mean, if you don't use sequences, it will always be around 30 rows per 9 second? – gdrt Jan 21 '19 at 14:06
Check out the IdentitySupport implementation of other Dialects which rely on JDBC to fetch the id using GENERATED_KEYS. Maybe this DB can do that too. – Vlad Mihalcea Jan 21 '19 at 14:34
OK, with sequence (like described [here](https://stackoverflow.com/a/24009231/3190576)), I was able to improve from 9 seconds to 5, but it's still really slow. – gdrt Jan 21 '19 at 14:38

score 0 · Answer 2 · answered Jan 22 '19 at 11:22

0

It's not a good idea to use columnar analytical DMBS for OLTP workload: select, insert or update single rows.

You may use MySQL / PostgreSQL / etc. to store job statuses and for general ORM sadness. And use Exasol for big data batches and large imports only.

answered Jan 22 '19 at 11:22

wildraid

126
4

Obviously, we don't use Exasol primarily for such small db transactions, it's just a complementary functionality. – gdrt Jan 22 '19 at 13:33

Hibernate selects identity column values before every insert

2 Answers2

Linked