Efficient way to Parsing and manipulating string

Question

I'm not sure i'm using the efficient way to manipulate large number of strings. I need to open a large file (~35kb) and parse and manipulate every line, ex:

a1="Name surname"
a2="Name surname"
a3="Name surname"
...
a800="Name surname"

I need to insert into a sqlite db every Name surname with the current id as index.

while ((s = d.readLine()) != null){
                        String[] name = s.split("\"");

                        String[] arrIdTemp = s.split("=");
                        String[] arrtId = arrIdTemp[0].split("a");

                        db.execSQL("INSERT INTO " + Constant.DB.DB_NAME
                                + " Values ('" + arrtId[1] + "','" + name[1]
                                + "');");

                    }

In this way i create 3 string arrays and for the first 100 lines is quite fast, but at the end the whole operation is slowing down, maybe because of a large use of memory? Is there a better solution to split and manipulate every line?

Logcat output says lots of:

01-27 14:46:50.554: D/dalvikvm(2541): GC_CONCURRENT freed 1778K, 68% free 3606K/11079K, external 4662K/5822K, paused 4ms+7ms

Any reason you're not using Properties?http://docs.oracle.com/javase/6/docs/api/java/util/Properties.html — weltraumpirat, Jan 27 '13 at 13:29
Insert more than one data set in one SQL command. => a lot fewer DB calls, which should improve the speed. — MrSmith42, Jan 27 '13 at 13:30
As an aside, don't constructor your SQL like that either - use parameters for the values, rather than embedding them. — Jon Skeet, Jan 27 '13 at 13:31
Your suggestion is to save all parameters (~800 key,value) in an object and at the end update the sql? — StarsSky, Jan 27 '13 at 13:34
first load the text file in an array with your splitted data, then for each value couple, write it to the db. In this way you will understand if the "slowing down" is due to the text file loading or to the db write. I suggest even 2 progress bar in order to see if your impression is true. Post the result of this test in order for us to be able to suggest you the solution (if you will still have problem) — Gaucho, Jan 27 '13 at 13:44
@StarsSky The idea is you should batch the updates a bit. And 35kBytes isn't anywhere close to a "large" batch, even for Android. (Displaying a photo on the screen probably uses loads more memory than that. — millimoose, Jan 27 '13 at 13:51
Now i understand that ~800 operations is a large waste of time. Batch the updates by using transaction like http://stackoverflow.com/a/2877122/2015318 ? — StarsSky, Jan 27 '13 at 13:56
Also note that creating new objects over and over again is a very expensive thing to do. Try to rececly the String objects in your loop. — Dan, Jan 27 '13 at 14:05
@Dan How exactly would you recycle String objects in Java? They're immutable. Besides, the OP's code doesn't really cause all that much avoidable GC pressure. (The SQL statement construction could be removed using parameters but that's really about it.) — millimoose, Jan 27 '13 at 14:11
@StarsSky Transactions and update batching are two not really related objects. Statements made in a transaction aren't batched by default and get processed immediately from the view of your connection. I'm not sure if you can batch updates in Android actually (short of constructing a large statement with many `VALUES` clauses which might not really be an improvement), at least I can't find it in the API docs. As Gaucho said, you should primarily split the two so you can find out *which* part is slow, then figure out how to optimise that. (And/or use a profiler.) — millimoose, Jan 27 '13 at 14:12
i edited my answer. Thanks all of you, i reduced time from 60seconds to 3 seconds! — StarsSky, Jan 27 '13 at 14:40

score 0 · Accepted Answer · answered Jan 27 '13 at 18:12

[1° SOLUTION]

I split the two operations and use transactions. From about 60 seconds, now i need only 2-3 seconds.

This is what i did:

//List of query
ArrayList<String> queryList = new ArrayList<String>();
...
while ((s = d.readLine()) != null){
                        String[] name = s.split("\"");

                        String[] arrIdTemp = s.split("=");
                        String[] arrtId = arrIdTemp[0].split("a");

                        queryList.add("INSERT INTO " + Constant.DB.DB_NAME
                                + " Values ('" + arrtId[1] + "','" + name[1]
                                + "');");

                    }

...

if (queryList!=null && queryList.size()>0){
                Iterator<String> iter = queryList.iterator();
                db.beginTransaction();
                try {
                    while (iter.hasNext()) {
                         db.execSQL(iter.next());
                        }
                    db.setTransactionSuccessful();
                } finally {
                    db.endTransaction();
                }
            }

if ther's a better solution or improvements on what i did, please tell me.

Some versions of SQL allow for a bulk insert, e.g. MSSQL has [BULK INSERT](http://msdn.microsoft.com/en-us/library/ms188365.aspx) and also see [this SO question](http://stackoverflow.com/questions/12365992/sql-bulk-insert-statement) — user949300, Jan 27 '13 at 18:16

Efficient way to Parsing and manipulating string

1 Answers1