Dear All,
I have been struggling with the performance of insertion in sqlite.
Here we have a very simple case :
A table with an integer autoincrement primary key and a text field that is
unique.
CREATE TABLE my (id PRIMARY KEY, url);
CREATE UNIQUE INDEX myurl ON my(url);
My application requires inserting up to 10 million records in batches of 20
thousand records.
I use :
A. PRAGMA SYNCHRONOUS = OFF
B. Prepared Statement
C. setAutoCommit(false), then to true at the end of the batch.
Using the above, the insertion starts off at a good speed, then drastically
slows down as more records are inserted.
It goes like this :
The first four inserstions (first 4*20K -> 60K records)
0 took 786
1 took 944
2 took 1001
3 took 1303
After the first 1 Million records
50 took 2560
51 took 1921
55 took 1900
53 took 3990
After the 2nd million records
2 took 22393
3 took 16789
4 took 29419
5 took 13993
By the end of the the 3rd million records the insertion time goes up to 30
seconds per 20K records.
My app is running from a java code and I am using SqliteJDBC 0.37 (the latest)
on Sun JDK 1.6.0_02 on CentOS 5.0 4GB Ram, two duo core cpu server with
raid10 sata-II harddisk.
I know I might be stretching sqlite far beyond its limits, I just want to
verify that there is nothing more that can be done to make a case for sqlite
in this scenario. I am not sure whats the best next thing to do either.
Your feedback and input will be highly appreciated,
- Kefah.
P.S. here is a snippit of the java code :
Class.forName("org.sqlite.JDBC");
Connection connection =
DriverManager.getConnection("jdbc:sqlite:./my.db");
long total_time=0;
int iterations = 50;
int records_per_iteration = 20000;
for(int i=0; i<iterations; i++) {
long time = System.currentTimeMillis();
connection.createStatement().execute("PRAGMA synchronous =
OFF;");
connection.setAutoCommit(false);
PreparedStatement insert = connection.prepareStatement("insert
or ignore into my(url) values(?);");
for(int j=0; j<records_per_iteration; j++) {
Random random = new Random((long)
(i*j*System.currentTimeMillis()));
String value = Double.toHexString(random.nextDouble())
+ Long.toHexString(time);
insert.setString(1, value);
insert.executeUpdate();
}
connection.commit();
insert.close();
connection.setAutoCommit(true);
connection.createStatement().execute("PRAGMA synchronous =
FULL;");
long iteration_time = (System.currentTimeMillis()-time);
System.out.println(i+ "\t took \t " + iteration_time);
total_time+=iteration_time;
}
System.out.println("Hello : Average : " +
(total_time*1.0/iterations));
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
