Android SQLite Insert Performance
I might just be hitting native performance issues with SQLite, but there seems to be significant overhead associated with inserting in SQLite even using Transaction processing and pre-compiled statements. Or perhaps I am using them incorrectly.
I have a requirement to read a file line by line (via a URL) and create rows. The mechanism is generic, so any table can be created (within reason) and any number of rows added. I am seeing pretty decent performance for larger inserts, but with smaller ones it seems I have a minimum of +400ms. Since I may have over 100 of these to load, that small overhead is resulting in large load times.
For example some timings:
34 records Test #1 ----> 490 ms
36238 records Test #2 ----> 3021 ms
4 records Test #3 ----> 520 ms
Here is the code (I cut out all the try/catches and other code to boil it down to the actual insert code):
InputStream input = new BufferedInputStream(url.openStream());
// Create the file reader
BufferedReader br = new BufferedReader(new InputStreamReader(input, "UTF-8"));
StringBuffer insertSql = null;
// Build the SQL to bind
insertSql = new StringBuffer("INSERT INTO " + fileName + " (");
String sep = "";
insertSql.append("[" + getDbColumnNames().replaceAll(" ", "").replaceAll(",", "],[") + "]");
String columns = getDbColumnNames().split(",");
insertSql.append(") VALUES (");
for (@SuppressWarnings("unused") String col : columns) {
insertSql.append(sep.trim() + "?");
sep = ",";
}
insertSql.append(");");
this.open();
sqlDB.beginTransaction();
SQLiteStatement stmt = = sqlDB.compileStatement(insertSql.toString());
String line = "";
// Read the file line by line
while ((line = br.readLine()) != null) {
String tokens = line.split(",");
// Build the bindings and insert the data
int bindcnt = 1;
for (String token : tokens) {
stmt.bindString(bindcnt++, token.trim());
}
long entryID = stmt.executeInsert();
if (entryID < 0) {
success = false;
}
stmt.clearBindings();
}
sqlDB.setTransactionSuccessful();
sqlDB.endTransaction();
I have verified that the read buffer overhead is not significant for most of the reads.
android performance sqlite insert asked Nov 20 at 22:56
Stephen McCormick
732826
add a comment |
I might just be hitting native performance issues with SQLite, but there seems to be significant overhead associated with inserting in SQLite even using Transaction processing and pre-compiled statements. Or perhaps I am using them incorrectly.
I have a requirement to read a file line by line (via a URL) and create rows. The mechanism is generic, so any table can be created (within reason) and any number of rows added. I am seeing pretty decent performance for larger inserts, but with smaller ones it seems I have a minimum of +400ms. Since I may have over 100 of these to load, that small overhead is resulting in large load times.
For example some timings:
34 records Test #1 ----> 490 ms
36238 records Test #2 ----> 3021 ms
4 records Test #3 ----> 520 ms
Here is the code (I cut out all the try/catches and other code to boil it down to the actual insert code):
InputStream input = new BufferedInputStream(url.openStream());
// Create the file reader
BufferedReader br = new BufferedReader(new InputStreamReader(input, "UTF-8"));
StringBuffer insertSql = null;
// Build the SQL to bind
insertSql = new StringBuffer("INSERT INTO " + fileName + " (");
String sep = "";
insertSql.append("[" + getDbColumnNames().replaceAll(" ", "").replaceAll(",", "],[") + "]");
String columns = getDbColumnNames().split(",");
insertSql.append(") VALUES (");
for (@SuppressWarnings("unused") String col : columns) {
insertSql.append(sep.trim() + "?");
sep = ",";
}
insertSql.append(");");
this.open();
sqlDB.beginTransaction();
SQLiteStatement stmt = = sqlDB.compileStatement(insertSql.toString());
String line = "";
// Read the file line by line
while ((line = br.readLine()) != null) {
String tokens = line.split(",");
// Build the bindings and insert the data
int bindcnt = 1;
for (String token : tokens) {
stmt.bindString(bindcnt++, token.trim());
}
long entryID = stmt.executeInsert();
if (entryID < 0) {
success = false;
}
stmt.clearBindings();
}
sqlDB.setTransactionSuccessful();
sqlDB.endTransaction();
I have verified that the read buffer overhead is not significant for most of the reads.
android performance sqlite insert asked Nov 20 at 22:56
Stephen McCormick
732826
A smaller number of inserts will have a relatively similar fixed overhead (writing to the disk) as a large number on inserts. Can't you combine the smaller ones into a larger (or a number of larger) inserts (transaction wise)?
– MikeT
Nov 21 at 1:54
@MikeT Unfortunately not. They are architected to be in their own table. So bottom line is that there is a 300ms overhead for small inserts, no matter what?
– Stephen McCormick
Nov 21 at 17:08
add a comment |
I might just be hitting native performance issues with SQLite, but there seems to be significant overhead associated with inserting in SQLite even using Transaction processing and pre-compiled statements. Or perhaps I am using them incorrectly.
I have a requirement to read a file line by line (via a URL) and create rows. The mechanism is generic, so any table can be created (within reason) and any number of rows added. I am seeing pretty decent performance for larger inserts, but with smaller ones it seems I have a minimum of +400ms. Since I may have over 100 of these to load, that small overhead is resulting in large load times.
For example some timings:
34 records Test #1 ----> 490 ms
36238 records Test #2 ----> 3021 ms
4 records Test #3 ----> 520 ms
Here is the code (I cut out all the try/catches and other code to boil it down to the actual insert code):
InputStream input = new BufferedInputStream(url.openStream());
// Create the file reader
BufferedReader br = new BufferedReader(new InputStreamReader(input, "UTF-8"));
StringBuffer insertSql = null;
// Build the SQL to bind
insertSql = new StringBuffer("INSERT INTO " + fileName + " (");
String sep = "";
insertSql.append("[" + getDbColumnNames().replaceAll(" ", "").replaceAll(",", "],[") + "]");
String columns = getDbColumnNames().split(",");
insertSql.append(") VALUES (");
for (@SuppressWarnings("unused") String col : columns) {
insertSql.append(sep.trim() + "?");
sep = ",";
}
insertSql.append(");");
this.open();
sqlDB.beginTransaction();
SQLiteStatement stmt = = sqlDB.compileStatement(insertSql.toString());
String line = "";
// Read the file line by line
while ((line = br.readLine()) != null) {
String tokens = line.split(",");
// Build the bindings and insert the data
int bindcnt = 1;
for (String token : tokens) {
stmt.bindString(bindcnt++, token.trim());
}
long entryID = stmt.executeInsert();
if (entryID < 0) {
success = false;
}
stmt.clearBindings();
}
sqlDB.setTransactionSuccessful();
sqlDB.endTransaction();
I have verified that the read buffer overhead is not significant for most of the reads.
android performance sqlite insert asked Nov 20 at 22:56
Stephen McCormick
732826
I might just be hitting native performance issues with SQLite, but there seems to be significant overhead associated with inserting in SQLite even using Transaction processing and pre-compiled statements. Or perhaps I am using them incorrectly.
I have a requirement to read a file line by line (via a URL) and create rows. The mechanism is generic, so any table can be created (within reason) and any number of rows added. I am seeing pretty decent performance for larger inserts, but with smaller ones it seems I have a minimum of +400ms. Since I may have over 100 of these to load, that small overhead is resulting in large load times.
For example some timings:
34 records Test #1 ----> 490 ms
36238 records Test #2 ----> 3021 ms
4 records Test #3 ----> 520 ms
Here is the code (I cut out all the try/catches and other code to boil it down to the actual insert code):
InputStream input = new BufferedInputStream(url.openStream());
// Create the file reader
BufferedReader br = new BufferedReader(new InputStreamReader(input, "UTF-8"));
StringBuffer insertSql = null;
// Build the SQL to bind
insertSql = new StringBuffer("INSERT INTO " + fileName + " (");
String sep = "";
insertSql.append("[" + getDbColumnNames().replaceAll(" ", "").replaceAll(",", "],[") + "]");
String columns = getDbColumnNames().split(",");
insertSql.append(") VALUES (");
for (@SuppressWarnings("unused") String col : columns) {
insertSql.append(sep.trim() + "?");
sep = ",";
}
insertSql.append(");");
this.open();
sqlDB.beginTransaction();
SQLiteStatement stmt = = sqlDB.compileStatement(insertSql.toString());
String line = "";
// Read the file line by line
while ((line = br.readLine()) != null) {
String tokens = line.split(",");
// Build the bindings and insert the data
int bindcnt = 1;
for (String token : tokens) {
stmt.bindString(bindcnt++, token.trim());
}
long entryID = stmt.executeInsert();
if (entryID < 0) {
success = false;
}
stmt.clearBindings();
}
sqlDB.setTransactionSuccessful();
sqlDB.endTransaction();
I have verified that the read buffer overhead is not significant for most of the reads.
android performance sqlite insert android performance sqlite insert asked Nov 20 at 22:56
Stephen McCormick
732826
asked Nov 20 at 22:56
Stephen McCormick
732826
asked Nov 20 at 22:56
Stephen McCormick
732826
asked Nov 20 at 22:56
Stephen McCormick
732826
asked Nov 20 at 22:56
Stephen McCormick
732826
732826
A smaller number of inserts will have a relatively similar fixed overhead (writing to the disk) as a large number on inserts. Can't you combine the smaller ones into a larger (or a number of larger) inserts (transaction wise)?
– MikeT
Nov 21 at 1:54
@MikeT Unfortunately not. They are architected to be in their own table. So bottom line is that there is a 300ms overhead for small inserts, no matter what?
– Stephen McCormick
Nov 21 at 17:08
add a comment |
A smaller number of inserts will have a relatively similar fixed overhead (writing to the disk) as a large number on inserts. Can't you combine the smaller ones into a larger (or a number of larger) inserts (transaction wise)?
– MikeT
Nov 21 at 1:54
@MikeT Unfortunately not. They are architected to be in their own table. So bottom line is that there is a 300ms overhead for small inserts, no matter what?
– Stephen McCormick
Nov 21 at 17:08
A smaller number of inserts will have a relatively similar fixed overhead (writing to the disk) as a large number on inserts. Can't you combine the smaller ones into a larger (or a number of larger) inserts (transaction wise)?
– MikeT
Nov 21 at 1:54
A smaller number of inserts will have a relatively similar fixed overhead (writing to the disk) as a large number on inserts. Can't you combine the smaller ones into a larger (or a number of larger) inserts (transaction wise)?
– MikeT
Nov 21 at 1:54
@MikeT Unfortunately not. They are architected to be in their own table. So bottom line is that there is a 300ms overhead for small inserts, no matter what?
– Stephen McCormick
Nov 21 at 17:08
@MikeT Unfortunately not. They are architected to be in their own table. So bottom line is that there is a 300ms overhead for small inserts, no matter what?
– Stephen McCormick
Nov 21 at 17:08
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53402837%2fandroid-sqlite-insert-performance%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53402837%2fandroid-sqlite-insert-performance%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
A smaller number of inserts will have a relatively similar fixed overhead (writing to the disk) as a large number on inserts. Can't you combine the smaller ones into a larger (or a number of larger) inserts (transaction wise)?
– MikeT
Nov 21 at 1:54
@MikeT Unfortunately not. They are architected to be in their own table. So bottom line is that there is a 300ms overhead for small inserts, no matter what?
– Stephen McCormick
Nov 21 at 17:08