Android Question RDC, DBRequestManager in background

[alan dodd]

Hello.
I am using RDC and DBRequestManager among other things to 'download' a table from a mysql server to local sqlite database. It works alright, but I need to execute the job in 'background'.
The table should download (to update local data) lets say every 30 seconds (I am using a timer to trigger the download). Download takes roughly 5 seconds for 200 records. During the download, the app gets unresponsive, I can't click on any button, or anything else. As the download finishes, all is normal again. This is not acceptable for the end user, as he must use the other functions of the program all the time.
Is this normal or should the job execute transparently and I am missing something?

'*******************************************
'*******************************************
'  REQMANAGER JOBDONE - CENTRAL ROUTINE
'*******************************************
'here we return after reqManager has done
'its job
'*******************************************
Sub JobDone(Job As HttpJob)

If Job.Success = False Then
  Log("Error: " & Job.ErrorMessage)
  Else
  Log("exporting/importing.....")
  If Job.JobName = "DBRequest" Then
    If Job.Tag = PerTag Then
      Dim table As DBResult = reqManager.HandleJob(Job)
      Dim LOM As List
      Dim temp As Double
      LOM.Initialize
    For Each row() As Object In table.Rows
        Dim m As Map
        Dim i As Int
        m.Initialize
        i=0
        For Each record As Object In row
          m.Put(table.Columns.GetKeyAt(i),record)
          i=i+1
        Next 
        LOM.Add(m)
    Next
    Logd("got LOM permessi")
    Log("Job Tag: " & Job.Tag)
    TabUtils.EmptyPerdataloc  'now we can safely delete data from table
    DBUtils.InsertMaps(SQL,"perdataloc",LOM) 'and put in new data
    Log("finish local perdataloc")
  End If
End If
Job.Release
End If
......



Sub Import 'this is where I make the call from timer (or from buttonclick, same result)
import_permessi("%")
End Sub


Sub import_permessi(Name As String)
  CmdImp.Initialize
  CmdImp.Name="select_permessi"
  CmdImp.Parameters=Array As Object(Name)
  Connect
  reqManager.ExecuteQuery(CmdImp,0,PerTag)
End Sub

Sub EmptyPerdataloc
Dim query As String
  query = "DELETE from perdataloc"
  SQLX.ExecNonQuery(query)
End Sub

'this is the line in config of java server

sql.select_permessi=SELECT * FROM permessi

'the table has 70 fields of mixed varchar and integers

p.s.
I think RDC is not the culprit.
The java server tells me the query took 45 msecs;
Handlejob in dbrequestmanager takes 1500 msecs!
Then dbutils.insertmaps takes the rest of the time to transfer data from table (as dbresult coming from handlejob) to SQLite table.
How can I have all this run in the background?

Thank you.
Alan

 

[Erel]

Add some log messages and find which part is the slow one.

 

[alan dodd]

Hi, Erel.
The whole process blocks UI until it has finished.
The timing I showed in the post was coming from log messages I inserted in the code; I am not sure if the code published is optimized.

BUT... I found the solution in the mean time.
I wrote another app with all the importing of the db done in a service module. The service gets started at boot time.
It restarts every 30 seconds, does its job and quits.
The main app now works concurrently without a hickup. It can read the same db that the service updates without problems.
This was not the case if I put the service in the same app.
Thanks.

My other concern is : will the sd card wear out if I continually empty and fill the tables?
I read about using sqlite in ram. But in that case, can I share the same 'ram' db between the service app and the main app?
(so as to write data from the service and then read it from the other app)
Alan

 

[alan dodd]

Hi, Erel.
I read about using sqlite in ram. But in that case, can I share the same 'ram' db between the service app and the main app?
(so as to write data from the service and then read it from the other app)
Alan

seems not possible...
....
This allows separate database connections to share the same in-memory database. Of course, all database connections sharing the in-memory database need to be in the same process.
....
see http://www.sqlite.org/inmemorydb.html

 

[Erel]

Not sure about the SD card (doesn't seem like a real issue to me).

The SQL library supports asynchronous operations. Assuming that this is the slow part then this can also be a solution.

 

[alan dodd]

I'm seeing if I can disable journalling, as the journal file I think is the one that gets written most frequently
PRAGMA database.journal_mode = MEMORY can I do this in an ExecNonQuery?

PRAGMA synchronous = OFF can I put this in an ExecNonQuery? - is this what you mean when you say that SQL library supports asynchronous operation?

cheers

 

[Erel]

is this what you mean when you say that SQL library supports asynchronous operation?

No. See the asynchronous tasks here: http://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content

You can set the pragma with ExecNonQuery. Though it will probably not do anything significant.

Make sure to first find the slow part and only then try to optimize it. Test in Release mode with a real device.