Android Question RDC, crashed on big data

incendio · Aug 9, 2015

Hi guys,

I have a problem when trying to retrieve large data with RDC.
Got message from app "Unfortunately My app has closed"

Data is about 62000 rows by 11 columns. Using my windows program, it took about 1 minute to retrieve all data.

Any idea how to fix it?

EDITED :
See post #19 for Summary

DonManfred · Aug 9, 2015

Look in the log when your app crashes. Maybe the unfiltered log. There must be more informations about the crash

incendio · Aug 9, 2015

There is no log at all.

I think it wait too long to retrieve all data.

incendio · Aug 9, 2015

Testing with about 40000 data, it could run without a crash. Took about 41 secs to complete the process.

My app has 2 process :
1) Retrieve data from Server with RDC, took about 15-20 secs to finish
2) insert this data into sqlite in memory, +/- took about 25-30 sec to finish

With more data it would crash. Is there any tips to speed up the process? I have already used transaction on sqlite.

Only 40000 data is not good, my data is much more bigger than that.

rboeck · Aug 9, 2015

You could use blocks of 10.000 records and repeat, till you are ready?!

incendio · Aug 9, 2015

In order to do that, there must be a physical index, while my data came from stored procedure that has no physical table nor index.

DonManfred · Aug 9, 2015

Did it work for all rows if you JUST download the data (not storing it into sqlite at this time)?

incendio · Aug 9, 2015

Without storing to sqlite, it worked. It took about 25-30 secs for about 60000 data.

This is the codes to insert to sqlite.

B4X:

                Main.PUB_SQL_MEM.BeginTransaction
   Main.PUB_SQL_MEM.ExecNonQuery("delete from MEM_SLS_RPT")
      For Each records() As Object In result.Rows                       
         Dim ListOfMaps As List
         ListOfMaps.Initialize
         Dim m As Map
         m.Initialize
         m.Put("AREA", records(0))
         m.Put("CTY", records(1))
         m.Put("DPT",records(2))
         m.Put("COUNTER",records(3))
         m.Put("MNTH",records(4))
         m.Put("TYP",records(5))
         m.Put("SUB_TYP",records(6))
         m.Put("GRP",records(7))
         m.Put("QTY",records(8))
                       
         ListOfMaps.Add(m)
         DBUtils.InsertMaps(Main.PUB_SQL_MEM, "MEM_SLS_RPT", ListOfMaps)
     Next
     Main.PUB_SQL_MEM.TransactionSuccessful                       
Main.PUB_SQL_MEM.EndTransaction

DonManfred · Aug 9, 2015

You maybe should split your inserts after you downloaded all items.
Fo ex.
Store the data in a global variable or use RAF to store it as object.

Use a timer then to read the data
- insert for ex 100 datasets from the data and remove the data you inserted from the list. Store the list again to be smaller when running the next timer tick....
- Check in timer tick if the list of items is empty. If so you are finished with import

incendio · Aug 9, 2015

RAF? what is it?

DBUtils use ExecNonQuery2, do you have any experience if ExecNonQueryNonBatch will be more faster?

LucaMs · Aug 9, 2015

"this" is RAF:

This too
https://www.b4x.com/android/help/randomaccessfile.html

incendio said:
ExecNonQueryNonBatch will be more faster?

I don't know, but that ...NonBatch makes me think the opposite.

Mahares · Aug 9, 2015

Just a thought: Have you tried to avoid maps and lists and use a code like this:

B4X:

Dim MyQuery as string ="INSERT INTO MEM_SLS_RPT VALUES(?,?,?,?,?,?,?,?)"
Main.PUB_SQL_MEM.BeginTransaction
  Main.PUB_SQL_MEM.ExecNonQuery("delete from MEM_SLS_RPT")
  For Each records() As Object In result.Rows
      Main.PUB_SQL_MEM.ExecNonQuery2(MyQuery,records)
  Next
  Main.PUB_SQL_MEM.TransactionSuccessful
  Main.PUB_SQL_MEM.EndTransaction

By the way, by RAF I think Manfred is referring to Random Access File lib.
EDIT: @LucaMs: Of course I know you like to spice it up with humor all the time thanks to your cheerful nature.

incendio · Aug 9, 2015

So, using RAF mean writing data to an internal storage, would it be much slower than using sqlite in memory?

Yes I will try to directly insert data without using list and map.

Took another test, on server side, stored procedure returns 60K data in about 2-3 secs, RDC downloaded in about 20-30 secs( via JDBC & Internet) Pretty fast but will got problem when the app fully running, caused the data, I guest will about 300K-400K per year.

Pitty, httputils & sql operations not support multi thread.

LucaMs · Aug 9, 2015

Mahares said:
By the way, by RAF I think Manfred is referring to Random Access File lib.

LucaMs said:
This too
https://www.b4x.com/android/help/randomaccessfile.html

Sorry, I need to joke

incendio · Aug 10, 2015

Mahares said:
Just a thought: Have you tried to avoid maps and lists and use a code like this:

B4X:

Dim MyQuery as string ="INSERT INTO MEM_SLS_RPT VALUES(?,?,?,?,?,?,?,?)" Main.PUB_SQL_MEM.BeginTransaction Main.PUB_SQL_MEM.ExecNonQuery("delete from MEM_SLS_RPT") For Each records() As Object In result.Rows Main.PUB_SQL_MEM.ExecNonQuery2(MyQuery,records) Next Main.PUB_SQL_MEM.TransactionSuccessful Main.PUB_SQL_MEM.EndTransaction

By the way, by RAF I think Manfred is referring to Random Access File lib.
EDIT: @LucaMs: Of course I know you like to spice it up with humor all the time thanks to your cheerful nature.

Just tried this code, I would say that it works pretty well.
It took only 5-7 secs to finish.

I think, it was DBUtils that made a crash. I would says for 60K data (depend also by a number of columns) it would crashed.

LucaMs · Aug 10, 2015

incendio said:
I think, it was DBUtils that made a crash. I would says for 60K data (depend also by a number of columns) it would crashed.

I have to apologize because I wrote almost without reading (yesterday was a bad day).

I suppose that the crash is instead Android that closes the app (due to the delay, however).

Your code was not perfect. It used rightly transactions; however, the code sends to DBUtils.InsertMaps only one record at a time and InsertMaps opens and closes a transaction every time.

InsertMaps receives a list of maps where each map is a record.

In short, if you add all the records to ListOfMaps and then you pass the list to the routine, the problem should be solved.

[your code with the change]

B4X:

            Main.PUB_SQL_MEM.ExecNonQuery("delete from MEM_SLS_RPT")

      Dim ListOfMaps As List
      ListOfMaps.Initialize

      For Each records() As Object In result.Rows       

         Dim m As Map
         m.Initialize

         m.Put("AREA", records(0))
         m.Put("CTY", records(1))
         m.Put("DPT",records(2))
         m.Put("COUNTER",records(3))
         m.Put("MNTH",records(4))
         m.Put("TYP",records(5))
         m.Put("SUB_TYP",records(6))
         m.Put("GRP",records(7))
         m.Put("QTY",records(8))
       
         ListOfMaps.Add(m)

     Next

     DBUtils.InsertMaps(Main.PUB_SQL_MEM, "MEM_SLS_RPT", ListOfMaps)

[A DoEvents inserted before Next could help]

[For fussiness and for better readability, I would use objRecordFields() instead of records(), lstRecords instead of ListOfMaps and mapRecord instead of m]

[P.S. Wow, but... I'm a good developer! Good news - self Like -]

incendio · Aug 10, 2015

I used Mahares style for 2 reason, it is faster & less line to type

But thanks anyway for your tip, this is new for me that InsertMaps could take a whole records, I thought it was only take single record.

LucaMs · Aug 10, 2015

incendio said:
I used Mahares style for 2 reason, it is faster & less line to type

But thanks anyway for your tip, this is new for me that InsertMaps could take a whole records, I thought it was only take single record.

Ok but, please, try my code, I would like to know if the crash happens.

(you should add a Try-Catch to @Mahares ' code )

incendio · Aug 10, 2015

Data has grows a little bit above 62K, now is around 62,1K, here is what happen
1) Testing on Genimotion with 1GB RAM, RDC crashed before reaching inserting codes, here is the log

B4X:

java.lang.OutOfMemoryError: [memory exhausted]
at dalvik.system.NativeStart.main(Native Method)

2) Testing on Genimotion with 2GB RAM, first run is OK, second run (without quit from application), app jush crashed without any Log

3) Placing DoEvents in the loop is bad idea, no crashed but seem runs forever. Placing DoEvents outside the loop, sometimes got the error

B4X:

an error has occurred in sub:mainmenu_jobdone(java line:947)
Android.View.InflateException:Binary XML file line #51:Error Inflating Class <unknown>

Sometimes got a message from OS, something like 'Program is not responding, do you want to close it?

Congrat to LucaMs, your code is even faster, it took only 2 secs to finish.

But unfortunately, problems remains, it was RDC that crashed on a big data.

Is there any setting to increase memory for RDC without increase memory in hardware?

MaFu · Aug 10, 2015

Try to add
SetApplicationAttribute(android:largeHeap, "true")
in the manifest editor.

Android Question RDC, crashed on big data

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency