Why SQLite is so slow with JDBC?

Question

A read a lot of stuff, like here: SQLite queries much slower using JDBC than in Firefox SqliteManager plugin

But I have no clue what's the problem. The case is, I have a SQLite database (from an Android tablet) and a not too large table (~50.000 rows in it) If I run a "select * from table" for example in Sqlite Manager it takes 0.11 sec, correct.

But... if I do it in a Java program (with SQLite JDBC) it takes 20 minutes!!! Not kidding.

Somebody (somewhere) said it depends on the versions. But my question is how?

Because this command: "SELECT sqlite_version()" gives different results on the same .db file in every case:

• in a very old sqlite manager it gives 3.6.19
• in Sqlite Studio 3.15
• and in with the newest .exe from sqlite.org it gives 3.23.1 So it's not a database related thing, I think it's the version of the sqlite3.exe used.

I can change the JDBC driver all day long (I did it a few times), but how would I know which I needed?

Anybody with any thought? I'm totally stucked with it.

EDIT: Okay, so JDBC jars are from here: https://bitbucket.org/xerial/sqlite-jdbc/downloads/

And my code is really basic, at first I just wanted to measure the speed.

        Class.forName("org.sqlite.JDBC");
        Connection c1 = DriverManager.getConnection("jdbc:sqlite:" + "c:\\database.db");

        PreparedStatement stmt1 = c1.prepareStatement("select * from table1;");
        ResultSet rs = stmt1.executeQuery();
        String script = "insert into table1 values ";
        while (rs.next()) {
            script += "(";
            script += rs.getInt(1) + ", '" + rs.getString(2) + "', '" + rs.getString(3) + "'";
            script += "),";
        }
        stmt1.close();
        c1.close();

And the executeQuery() row takes 20 minutes.

Answer 1

You are create a String with 50k rows, this means you are creating 50k * 5 String (each concatenation create a new String instance. This is what kills your performance.

while (rs.next()) {
    script += "(";
    script += rs.getInt(1) + ", '" + rs.getString(2) + "', '" + rs.getString(3) + "'";
    script += "),";
}

I noticed that you don't excute the String script, so if you just want to create a String, use a StringBuilder

StringBuilder script = new StringBuilder("insert into table1 values ");
    while (rs.next()) {
        script.append("(")
              .append(rs.getInt(1)).append(", '")
              .append(rs.getString(2)).append("', '")
              .append(rs.getString(3)).append("'")
          .append("),");
    }

script.setLength(script.length() - 1); //to remove the last comma.

String query = script.toString();

The StringBuilder prevent the huge number of String instance created for nothing.

If you want to insert those values after that, use a PreparedStatement directly instead of building a query :

PreparedStatement psInsert = c1.prepareStatement("insert into table1 values (?,?,?)");
while (rs.next()) {
    psInsert.setInt(1, rs.getInt(1));
    psInsert.setString(2, rs.getString(2));
    psInsert.setString(2,rs.getString(3));

    psInsert.execute();
}

Then if you want to improve this, use the batch system to send small block of insert. Using Statement.addBatch() and Statement.executeBatch()

 while (rs.next()) {
    psInsert.setInt(1, rs.getInt(1));
    psInsert.setString(2, rs.getString(2));
    psInsert.setString(2,rs.getString(3));

    psInsert.addBatch();
    if(batchSize++ > 100){ //Execute every 100 rows
        psInsert.executeBatch();
        batchSize = 0;
    }
}

if(batchSize > 0){ //execute the remainings data
      psInsert.executeBatch();
}

StringBuilder Benchmark

Not an official one, just a Duration of a simple execution

LocalTime start = LocalTime.now();
StringBuilder sb = new StringBuilder("Foo;");
for(int i = 0; i < 50_000; i++){
    sb.append("Row").append(i).append(";\n");
}
System.out.println(Duration.between(start, LocalTime.now()).toNanos());
String s = sb.toString();
System.out.println(s.substring(0, 50));

This takes 15 nanoseconds

LocalTime start = LocalTime.now();
String s = "Foo;";
for(int i = 0; i < 50_000; i++){
    s += "Row" + i + ";\n";
}
System.out.println(Duration.between(start, LocalTime.now()).toMillis());
System.out.println(s.substring(0, 50));

This takes >6 seconds

Answer 2

In a successful application, we use sqlite as database. In our application, we also use JPA, and define the database as a persistence unit, within the Java resources directory:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="jpa" transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <properties>
            <property name="javax.persistence.jdbc.url" value="jdbc:sqlite:/ourdata/mySqliteDB.db" />
             <property name="javax.persistence.jdbc.driver" value="org.sqlite.JDBC" />
             <property name="eclipselink.logging.level" value="SEVERE"/>
             <property name="eclipselink.jdbc.cache-statements" value="true"/>
             <property name="eclipselink.weaving" value="false"/>
             <property name="eclipselink.weaving.fetch-groups" value="false"/>
             <property name="showSql" value="false"/>
        </properties>
    </persistence-unit>
</persistence>

We have no time-dragging access issues with sqlite.

When accessing large database tables, it is generally known that an index needs to be defined for each column used in an sql-query, in order to ensure fast query response times. This holds also for the JPA (typically the 'findall' query).