Derek Battams <[EMAIL PROTECTED]> wrote:
> The average run of the worker thread produces ~1100 log entries
...
> If no external process is to be called on a given run then the
> average run time is ~2-5 seconds
What you're saying doesn't make any sense:
$ java -cp .:sqlitejdbc-v042-native.jar -Djava.library.path=. Test
inserting 1000 entries took 0.018 seconds
$ java -cp sqlitejdbc-v042-nested.jar:. Test
inserting 1000 entries took 0.018 seconds
Let's try even more:
$ java -cp sqlitejdbc-v042-nested.jar:. Test
inserting 100000 entries took 1.51 seconds
Here's the code:
import java.sql.*;
public class Test {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection(
"jdbc:sqlite:test.db");
conn.createStatement().executeUpdate(
"create table log (c1, c2, c3);");
PreparedStatement prep = conn.prepareStatement(
"insert into log values (?, ?, ?);");
double time = (double)System.currentTimeMillis();
conn.setAutoCommit(false);
for (int i=0; i < 100000; i++) {
prep.setString(1, "hello world, this is a test");
prep.setString(2, "here is some more text, let's not go easy.");
prep.setString(3, "even more text");
prep.addBatch();
}
prep.executeBatch();
conn.commit();
time = (System.currentTimeMillis() - time) / 1000;
System.out.println("inserting 100000 entries took "+time
+" seconds");
}
}
Clearly the speed issues have nothing at all to do with SQLite. I
admit I'm running this on a Core 2 Duo, but to turn 0.018 seconds into
2-5 seconds would probably take a Pentium II.
You're problem is whatever processing you're doing for each line
inside your loop. The solution is really easy: use the addBatch() /
executeBatch() code as in the above example. It all gets stored in
memory, and only sent to SQLite when you call executeBatch(). The
memory usage isn't a problem, whatever you're using as a servlet
container is sure to be such a memory hog as to make it look
insignificant.
Again: the only legitimate need for a long RESERVED lock in SQLite is
an insert or update statement containing slow sub-selects that are
doing complex joins. Slow where clauses don't count, use an index.
d.
--~--~---------~--~----~------------~-------~--~----~
Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en
To unsubscribe, send email to [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
