1

I worked in android app that read large text file from server (20 MB) line by line , After each line insert into sqlite DB some data extracted from this line and so on .

the problem is, this application takes one hour approximately to finish reading this file . note : there are 400,000 line in this file .

how could i fast this operation to take 3 or 4 minutes ? Thanks for your help .

    public void readArData() {
    try {
        URL url = new URL("http://10.0.2.2/xy.txt");
        BufferedReader in = new BufferedReader(new   
    inputStreamReader(url.openStream()));
        String str;
        while ((str = in.readLine()) != null) {
            // insert into Sqlite DB
        }
        in.close();
    } catch (MalformedURLException e) {
    } catch (IOException e) {
    }

}
Samy Louize Hanna
  • 821
  • 2
  • 8
  • 15

3 Answers3

2

Are you sure 3 - 4 minutes is an achievable amount of time? I ask because you have to:

1) Download the file contents 2) Parse the file contents 3) Insert into a database

Some of these operations can occur simultaneously but #1 is highly dependent on the network speed and 2/3 are highly dependent on what sort of system resources you have available.

Instead, can we ask why you need to pull all this data down to the phone and drop it in a database? Is there some issue with your app querying a database hosted on the webserver that holds the .txt? What sort of access to the .txt do you have? Can you modify it on the server so parsing can be done quicker?

In short, I think we need more data to analyze your issue. Can you post some examples of lines from the .txt and the code you use to parse it?

Grambot
  • 4,370
  • 5
  • 28
  • 43
  • 1
    You might want to consider preprocessing the file on a server and then copying that onto the phone. I think it is possible to copy a fully loaded SQLite database onto the phone from a server. – zostay Dec 14 '11 at 21:58
  • 1
    +1, if you have 400,000 lines and want to do it in 4 minutes, that's 100,000 SQL insertions every minute, 1666 per second. Seems a lot to ask from a mobile device, especially if you have to do some sort of string processing as well. – kabuko Dec 14 '11 at 22:01
1

Text files are highly compressible so use the gzip compression available on Android.

Other options include:

  • splitting your dataset into multiple files and again using gzip download them separately

  • seeding the database during the install and creating a synchronization method to update newer records and bring the mobile version up to date.\

  • remove redundant data using an efficient database design and only downloading what's required

UPDATE

To clarify:

  1. zip the txt file on the server.
  2. change your android code to download the zip file to local storage on device.
  3. unzip the local copy to local storage.

You should now have a copy of your txt file on local storage so you can delete the local zip file.

  1. Read the local txt file and insert database records.

The major differences in this approach are

a. that you are downloading less data b. your download is not interrupted by sql inserts.

You can also try using a transaction to improve insert speed as described in Android SQLite database: slow insertion

db.beginTransaction();
for (entry : listOfEntries) {
    db.insert(entry);
}
db.setTransactionSuccessful();
db.endTransaction();
Community
  • 1
  • 1
Moog
  • 10,193
  • 2
  • 40
  • 66
  • ok i will try this but Reading from internal storage is faster than reading from server ?mean is there a big different between two operation (reading from internal storage or from server) ? – Samy Louize Hanna Dec 14 '11 at 22:23
  • the bigger difference here is the use of gzip. do that, 6-7 times faster. that's the first thing you should do. – tacone Dec 15 '11 at 00:48
  • as @tacone says, the gzip will make a vast difference, but I think the inserts are causing a bottleneck which is making the problem worse. – Moog Dec 15 '11 at 12:06
  • I encourage you to read to read the point "3" of my answer. That also may help much. – tacone Dec 15 '11 at 18:09
  • I have done same thing but sql file is too large, while reading this file it thrown OOM error. Please let me know if you have solution for that. Thank you. – Krunal Shah May 02 '18 at 05:08
  • @KrunalShah see option 1 – Moog May 04 '18 at 18:01
0
  1. make sure you have 20mb of space in the internal storage or sd card.
  2. dowload the file all at once, better if zipped. (as it will weight circa 3-4mb)
  3. then you can open it and parse it. Use queries that insert more than row a time (check the INSERT sqlite syntax).
  4. delete the file.

this approach will also allow you to resume an interrupted download, which is not bad.

tacone
  • 11,371
  • 8
  • 43
  • 60
  • that's exactly the same as the update I provided yesterday. Please avoid posting duplicate answers. – Moog Dec 16 '11 at 13:40
  • actually performance wise the 3rd point is probably the most important. You didn't provide anything like that until your last edit. – tacone Dec 16 '11 at 17:43