Android Question RDC, crashed on big data

[incendio]

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]

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

 

[incendio]

There is no log at all.

I think it wait too long to retrieve all data.

 

[incendio]

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]

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

 

[incendio]

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]

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

 

[incendio]

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

This is the codes to insert to sqlite.

                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]

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]

RAF? what is it?

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

 

[LucaMs]

"this" is RAF:

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

ExecNonQueryNonBatch will be more faster?

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

 

[Mahares]

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

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]

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]

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

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

Sorry, I need to joke

 

[incendio]

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

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]

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]

            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]

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]

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]

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

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

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]

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