0

I have a this function below which is working fine:

I use it because in a synchronisation process I have to insert a lot of rows (sometimes more than 30 000 in the same sync) in different tables and for some of them, the value I recieve from my Json object is not the real one. So before inserting the row, I have to replace the value with another. That is why I use this function: 

public void myFunction( String tableName, String attrName, ContentValues currentVal ) { 

    SQLiteDatabase database = this.getReadableDatabase();
    long realValue = -1;

    Cursor c = database.rawQuery("SELECT value FROM "+tableName+" WHERE "+tableName+".other_value = " + currentVal.getAsString(attrName), null);

    if( c.moveToFirst() ) { 
        realValue = c.getLong(0);
    }
    if( c != null ) c.close();

    currentVal.put( attrName, realValue );  

}

My problem is the Cursor... The c.moveToFirst() function affects the speed of the process.

I did researches and I found so many explanations like :

https://stackoverflow.com/a/15738831/5778152

I specify that I use indexes and I already try transactions.

So, what I wanted to do now (or to know) is: Is there a way to perform something like:

currentVal.put( attrName, SELECT value FROM "+tableName+" WHERE "+tableName+".other_value = " + currentVal.getAsString(attrName) );

Because with this trick I will not have to iterate into my Cursor (which is actually my problem) and when I will insert the content of the ContentValues it will automatically process the sub-query to retrieve the real value to insert.

I also thought about creating a temporary table. I insert the values founded in my Json object without changing them and when I've inserted all the values for the current table, I copy them into the real table unsing in the same time a sub-query to relpace the incorrect values by the correct ones.

Any ideas, suggestions, snippets, ... will be appreciated

Community
  • 1
  • 1
Nicolas Cortell
  • 659
  • 4
  • 16
  • The best approach I've tried inserting over 100,000 elements onto SQLite is using it through NDK layer, and using some simple native code to access and write onto DB which is quite fast in comparison to application layer writing. – zIronManBox Jan 19 '16 at 13:18
  • Inserts do not take much time, the problem is in the replacement part because until now I didn't found how to go through this without using the `cursor.moveToFirst()` function – Nicolas Cortell Jan 19 '16 at 13:25
  • You must finally `cursor.close()` after every operation. Then reopen `cursor` object. Are you doing it? – zIronManBox Jan 19 '16 at 13:34
  • Are you using the *right* indexes? Show the output of [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html) for the SELECT query. – CL. Jan 19 '16 at 13:43
  • Yes, I'm using the right indexes. In my question I said it was for 30 000 rows but this amount of rows is almost the same for each tables (about 15). So the sync operation is taking between 40min to 2hours... Sorry, I can't show the output of the EXPLAIN QUARY PLAN but the bottleneck is the `cursor.moveToFirst()` – Nicolas Cortell Jan 19 '16 at 13:55
  • Why can't you show the EQP for that query? – CL. Jan 19 '16 at 14:31
  • Because I will have to execute a some scriptsbefore starting the sync process and it's not possible actually. Really sorry... – Nicolas Cortell Jan 19 '16 at 14:40
  • You don't need the real DB or real data; what matters is the DB structure. – CL. Jan 19 '16 at 14:48
  • Hum, ok. And you also don't need it for each different queries? Because queries can be different depending on the parameters of the function... – Nicolas Cortell Jan 19 '16 at 14:50
  • I want to see the EQP output for those queries that are too slow for you. (And what did you measure?) – CL. Jan 19 '16 at 14:53
  • All of them are too slow for me :D 1 by 1 it's not that slow but when you have 30 000 values to insert for 15 different tables it's taking long. I give you the EQP asap – Nicolas Cortell Jan 19 '16 at 14:55
  • How slow is "not that slow"? – CL. Jan 19 '16 at 14:57
  • Think it was something like 0.03s but I didn't measure it, it was a colleague (my tables contains between 1 000 000 and 5 000 000 rows) – Nicolas Cortell Jan 19 '16 at 15:03

0 Answers0