I am trying to improve the speed of my android database inserts. What I am currently doing is generate a string like:
SELECT ? as title, ? as musician_id, ? as album_id, ? as genre
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?
And then executing it with
SQLiteDatabase database = //initialized in some way
String insertQuery; // the string of the query above
String [] parameters; // the parameters to use in the insertion.
database.execSQL(insertQuery.toString(), parameters);
I am getting the following error when I try to insert about 2000 rows:
Caused by: android.database.sqlite.SQLiteException: too many SQL variables (code 1): , while compiling: INSERT INTO songs (title, musician_id, album_id, genre)
SELECT ? as title, ? as musician_id, ? as album_id, ? as genre
UNION SELECT ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?
When I try to insert about 200 rows everything works fine.
I suppose it is obvious - I am trying to pass in too many variables in a single execSQL
. Does anyone know what is the limit so that I can split the rows I insert in appropriate batches?
The limit is hardcoded in sqlite3.c and is set to 999. Unfortunately it can be changed but only at compile time. Here are the relevant snippets:
/* ** The maximum value of a ?nnn wildcard that the parser will accept. */ #ifndef SQLITE_MAX_VARIABLE_NUMBER # define SQLITE_MAX_VARIABLE_NUMBER 999 #endif /* ** The datatype ynVar is a signed integer, either 16-bit or 32-bit. ** Usually it is 16-bits. But if SQLITE_MAX_VARIABLE_NUMBER is greater ** than 32767 we have to make it 32-bit. 16-bit is preferred because ** it uses less memory in the Expr object, which is a big memory user ** in systems with lots of prepared statements. And few applications ** need more than about 10 or 20 variables. But some extreme users want ** to have prepared statements with over 32767 variables, and for them ** the option is available (at compile-time). */ #if SQLITE_MAX_VARIABLE_NUMBER<=32767 typedef i16 ynVar; #else typedef int ynVar; #endif
I am trying to improve the speed of my android database inserts. What I am currently doing is generate a string like:
Did you think about use TRANSACTION
? I suggest you to use it instead of yours approach. I think use UNION
clause is not a "win" at all and there is better and mainly safer way how to achieve it.
db.beginTransaction();
try {
for (int i = 0 ; i < length ; i++ ) { // or another kind of loop etc.
// make insert actions
}
db.setTransactionSuccessful(); // now commit changes
}
finally {
db.endTransaction();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With