Menu ▾ ▴

Reduced INSERT performance in 5.0.0

General Discussion
WFR
2020-06-25
2021-02-10

  • WFR

    WFR - 2020-06-25

    Hi,
    I have recently updated UCanAccess from 4.0.4 to 5.0.0 and have observed a reduced INSERT performance in our application. I have compared both sources and cannot pinpoint any change that is responsible.

    I can reproduce the behaviour as follows:

    import java.io.File;
import java.sql.*;
import java.time.Duration;
import java.time.Instant;

public class Main {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); /* often not required for Java 6 and later (JDBC 4.x) */

        new File("test.accdb").delete(); // to clear previous test run results

        // Set up Database
        Connection conn= DriverManager.getConnection("jdbc:ucanaccess://test.accdb;newDatabaseVersion=V2010");
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        st.executeUpdate("CREATE TABLE [Test] ([ID] SMALLINT NOT NULL, [a] VARCHAR(255), [b] VARCHAR(255), [c] VARCHAR(255), [d] VARCHAR(255), [e] VARCHAR(255), [f] VARCHAR(255), [g] VARCHAR(255), [h] VARCHAR(255), [i] VARCHAR(255));");
        conn.commit();
        st.close();

        // Set up prepared statement
        PreparedStatement prepSt = conn.prepareStatement("INSERT INTO [Test] VALUES (?,?,?,?,?,?,?,?,?,?);");
        for (short i = 0; i < 5000; i++){
            prepSt.setShort(1, i);
            for (int col = 2; col <= 10; col++)
                prepSt.setString(col, "lorem ipsum");
            prepSt.executeUpdate();
        }
        prepSt.close();

        // Commit inserts (on 4.0.4: approx. 200ms, on 5.0.0: approx. 7000ms)
        Instant start = Instant.now();
        conn.commit();
        Instant finish = Instant.now();
        System.out.println(Duration.between(start, finish).toMillis());
        conn.close();
    }
}

    Executing with UCanaccess 5.0.0 the commit takes approx. 7000ms, on 4.0.4 it was only 200ms. Do you have any explanation as to where this comes from?

    POM for 4.0.4:

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>groupId</groupId>
    <artifactId>04-UCanAccessBug</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <dependencies>
    <dependency>
        <groupId>net.sf.ucanaccess</groupId>
        <artifactId>ucanaccess</artifactId>
        <version>4.0.4</version>
    </dependency>
    <dependency>
        <groupId>com.healthmarketscience.jackcess</groupId>
        <artifactId>jackcess</artifactId>
        <version>3.0.1</version>
    </dependency>
    <dependency>
        <groupId>org.hsqldb</groupId>
        <artifactId>hsqldb</artifactId>
        <version>2.5.0</version>
    </dependency>
    </dependencies>


</project>

    POM for 5.0.0

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>groupId</groupId>
    <artifactId>04-UCanAccessBug</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <dependencies>
    <dependency>
        <groupId>net.sf.ucanaccess</groupId>
        <artifactId>ucanaccess</artifactId>
        <version>5.0.0</version>
    </dependency>
    <dependency>
        <groupId>com.healthmarketscience.jackcess</groupId>
        <artifactId>jackcess</artifactId>
        <version>3.0.1</version>
    </dependency>
    <dependency>
        <groupId>org.hsqldb</groupId>
        <artifactId>hsqldb</artifactId>
        <version>2.5.0</version>
    </dependency>
    </dependencies>


</project>

    Edit: Windows 10, Java 1.8.251

     

    Last edit: WFR 2020-06-25

Log in to post a comment.