4.0 slowness and compatibility

Hi all,

I’m trying to do an upgrade from 4.2.0 to the last version.

My context: SpringBoot 2.3.3 and Postgresql.

I faced 2 main issues, I tried to identified which version was responsible of each.

  • The first one is slowness
    Upgrading until 4.3.5 is transparent. From an empty DB it’s take 80s to create the schema and insert our referential Data.
    When moving to 4.4.0 the initialisation double. The duration increase from 80s to 160s.
    I don’t find a specific changeset that is responsible of the slowness, but it should be related to ‘loadData’ and prepareStatement.

Moving to 4.4.3 increase the slowness. Going to a duration of with a factor of 2, 4.5.0 is the same.

I do some test with a simple changelog file: a table creation and the loading of my biggest file (40’000 lines).
Using: usePreparedStatements: true takes 95s
Using: usePreparedStatements: false takes 19s
5 times slower with preparestatements (taht is the default value), seems to be strange.

  • The second one is: my existing script can’t be executed by the new version without changing my history.
    My ‘loadData’ changeset doesn’t have the usePreparedStatements parameter.
    Starting from the 4.5.0, the loadData can’t be run when one of the column is a numeric value.
    I have the following error:
Caused by: org.postgresql.util.PSQLException: ERROR: column "minimum_allocation" is of type double precision but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 215

I found 2 workaround:
a) add usePreparedStatements: false on all my loadData
b) add column definition in the loadData for all the numeric values:

        - loadData:
            encoding: UTF-8
            file: db/changelog/data/mydata.csv
            separator: ","
            tableName: MY_TABLE
                    columns:
                      - column:
                          header: MINIMUM
                          type: NUMERIC
                      - column:
                          header: MAXIMUM
                          type: NUMERIC

The solution b) can’t be use all the times as loadData may have to use prepare statement (CLOB, longtext, etc.).

But the very good news is: the solution b) solve my perf issue, boosting the original perf.

At he end, with usePreparedStatements: false everytime it’s possible and the last (4.6.2)decrease the duration from 35s down to 80s.
But why :

  1. PrepareStatement need to redefined the numeric type in th loadData ?
  2. The PrepareStatement default value very slow ?

I don’t find any post about performance issue or configuration adaption in the release notes.
Has someone faced to such behaviour ?

Regards,
Philippe

Hi @pkernevez ,

That is an interesting question. One of our developers might be able to provide you the correct answer for this. @NathanVoxland @MikeOlivas Can you jump into this one?