27

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?

Boris Strandjev
  • 46,145
  • 15
  • 108
  • 135
  • 1
    You should have a look at the documentation here: http://www.sqlite.org/limits.html – ebarrenechea Mar 09 '13 at 15:51
  • @ebarrenchea I am almost certain the limit is not imposed by SQLite rather from the Android SQLite API. Have you found any indication of the opposite? – Boris Strandjev Mar 09 '13 at 15:54
  • 1
    You could approach this with a binary search: 2000 doesn't work, so try 1000, then 500 (or 1500)... – Sam Mar 09 '13 at 16:00
  • Hmm, I searched for "variable" in http://www.sqlite.org/limits.html and found: "SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999." It's item #9: "Maximum Number Of Host Parameters In A Single SQL Statement" Start your search with 998, 999, 1000. Ironically 999 is the _last_ number (symmetrically) a binary search from 2000 will hit. – Sam Mar 09 '13 at 16:10
  • 2
    @BorisStrandjev It seems that this error message comes from the sqlite3.c as you can see here: https://raw.github.com/android/platform_external_sqlite/master/dist/sqlite3.c – ebarrenechea Mar 09 '13 at 16:38
  • By the way, @Sam is right, the limit is set to 999 and it's hard coded in the source so unless it's changed there that is the limit. – ebarrenechea Mar 09 '13 at 16:40
  • @ebarrenchea Good find! I suggest posting an answer using two snippets from that link: 1) where SQLITE_MAX_VARIABLE_NUMBER is defaulted to 999, 2) the passage about "extreme user want to have prepared statements with over 32767 variables" (Yikes!). – Sam Mar 09 '13 at 16:49
  • @ebarrenchea Now I have also verified the number 999 in empirical testing. I insist you post an answer to the question so that I can accept you. – Boris Strandjev Mar 09 '13 at 16:54

2 Answers2

42

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
ebarrenechea
  • 3,775
  • 1
  • 31
  • 37
  • 4
    By only changed at compile time, is that compile time of the Android OS then? So there's no way to change this for an Android app that's getting this error? If not, I'll have to check the size of my IN clause parameters and be sure they never exceed this 999 limit, separating them into separate queries to join. – Jeff Lockhart Jun 15 '15 at 07:13
  • 1
    Actually the limit is 998, since parameter number starts in 1, according to the documentation, and validated with an End2End test, from the docs: "The numbers normally begin with 1 and increase by one with each new parameter." https://www.sqlite.org/limits.html – Akhha8 Feb 09 '23 at 22:55
16

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();
}
Simon Dorociak
  • 33,374
  • 10
  • 68
  • 106
  • Do you think the transaction is going to speed up my inserts? – Boris Strandjev Mar 09 '13 at 16:51
  • @BorisStrandjev if i dont, i wouldn't make an answer. If you want to be more "sure" about it, you can make some tests when you for example will perform 5 000 - 50 000 inserts with and without use of transaction and measure a time. – Simon Dorociak Mar 09 '13 at 16:55
  • 1
    This is the most useful thing that I have ever learned in SO. Really! You can see how transaction compares to few other database approaches in my benchmarking project: https://code.google.com/p/gdg-sofia-prototypes/source/browse/#svn%2Ftrunk%2FAndroidPersistenceBenchmark. fastest yet. – Boris Strandjev Mar 09 '13 at 17:03