Performance of SQL inserts with Liquibase

Question

In our project we are using Liquibase 4.6.1 and install the cumulative (initial) state of DB and test data within SQL scripts with many INSERTs. We do an installation for MySQL and for Oracle, for MySQL it is Okay, we use multiple inserts like:

INSERT INTO `${schema-user.name}`.`TABLE`
( `FIELD_1`, ...,  `FIELD_N` )
VALUES
( 0, ..., 0 ),
( 99, ..., 99 );

That is why an installation of a schema A is about 5 seconds on local PC with MySQL instance in a docker container.

However, there is no multiple insert command for Oracle, so generally we just do inserting one by one. And that is quite painful: on the same PC, with an Oracle instance inside a container it's about 30 secs.

From what I have tried:

INSERT ALL

INSERT ALL
    INTO "${schema-user.name}".TABLE (FIELD_1,..., FIELD_N)
    VALUES (1, .., N)
    ...
    INTO "${schema-user.name}".TABLE (FIELD_1,..., FIELD_N)
    VALUES (1, .., N)
SELECT 1 FROM DUAL
/

SELECT WITH:

insert /*+ APPEND NOLOGGING */ into "${schema-user.name}".TABLE (FIELD_1,..., FIELD_N)
with vals as (
    select 1, .., N from dual union all
    select 1, .., N from dual
    )
select * from vals
/

The approaches above do not give any improvement.

The specific of schema A is what tables mostly have no any indexes/constrains except PK and there are no triggers on the tables. So I don't see here a problem of many indexes/triggers which could impact the performance.

From Liquibase's logs I see, that LB reads SQL file and does something quite long before it start to apply those particular INSERTs like the following:

[2022-02-10 17:56:03] FINE [liquibase.changelog] Reading ChangeSet: cumulative/cumulative.root.yml::1643287602-install-test-data-schema-a::ppraulov
[2022-02-10 17:56:03] FINE [liquibase.resource] Closing duplicate stream for file:/home/ppraulov/path/schema-a.test-data.sql
[2022-02-10 17:56:03] FINE [liquibase.configuration] No configuration value for liquibase.fileEncoding found
[2022-02-10 17:56:03] FINE [liquibase.configuration] Configuration liquibase.fileEncoding is using the default value of UTF-8

[2022-02-10 17:53:44] FINE [liquibase.executor] Executing with the 'jdbc' executor
[2022-02-10 17:53:44] FINE [liquibase.executor] 1 row(s) affected
[2022-02-10 17:53:44] FINE [liquibase.database] Executing Statement: INSERT INTO "schema-a"."TABLE"
...

As the last measure we have an option of installation of test data from CSV files, which is faster, however, it is still far from the desirable level of 5 secs, which we have for MySQL installation.

So my question is what would be possible ways to improve performance of installation on Oracle using SQL scripts?

user_1156452 · Answer

If you want to use Liquibase in the Oracle toolkit, e.g. SQLcl, we allow you to batchload rows to your table in a changeLog using a LOAD command, that rips through a csv file.

I talk about this in detail here

Concepts: SQLcl is free. We have taken the community edition of Liquibase, and enhanced the Oracle support. It's using the 4.6.x library, but also has access to ALL of our features in SQLcl, a more modern take on SQLPlus.

SO if you wanted to use the SQLcl (from SQL Developer) features...like generating DDL, loading rows to a table...that would look like this:

And it's batching the rows via JDBC, will be MUCH faster than row-by-row inserts.

Disclaimer: I'm a product manager at Oracle and SQLcl is one of my products.

Performance of SQL inserts with Liquibase

1 Answers