Android Question Avoiding an ANR warning - Advice Needed

[wonder]

Greetings!

Due to the amount of data, accessing (DBUtils) and processing* my SQLite DB is causing an ANR warning on some devices.
While there is some code optimization to be done, I would like such operations to be performed in a background thread, as recommended in this article: http://developer.android.com/training/articles/perf-anr.html

What would be the best approach?
- Move everything into a Service Module?
- Use the Threading Lib?
- Rewrite DBUtils to asynchronously access the DB?
- Other?

Rewriting DBUtils doesn't seem plausible to me because I also want the data processing to be done in the background.

*Initializing a fairly amount of variables and arrays based on the data loaded from the DB.

 

[DonManfred]

I would suggest using a Service or maybe the new CallSubExtended lib from the Probundle as from @Informatix
You can probably use the new starter service to do the things in.

 

[cimperia]

First thing I would do is to make sure the SQL is fully optimized. It's where the bottle neck usually is.

If you have already done that, then I second DonManfred's suggestion of using a service.

 

[Roycefer]

Services don't necessarily run in another thread. My tests show that the Starter Service definitely doesn't. To prove this to your satisfaction, run the following code in Activities, Services, Threads, etc...:

Sub LogThreadName(messageObj As Object)
    Dim message As String = messageObj
    Dim r As Reflector
    r.Target = r.RunStaticMethod("java.lang.Thread", "currentThread", Null, Null)
    Log("Mess: " & message)
    Log("Calling Thread Name: " & r.RunMethod("getName"))
End Sub

I predict that Erel's advice will be to do everything asynchronously that can be done so (the SQL library has asynchronous methods) and only as a last resort should you consider using the Threading library.

 

[eurojam]

If you have a lot of Insert and Delete statements on your database, the databasefile may be larger then needed. That's the point where you can use VACUUM to free database space. See https://sqlite.org/lang_vacuum.html for more information.

 

[wonder]

If you have a lot of Insert and Delete statements on your database, the databasefile may be larger then needed. That's the point where you can use VACUUM to free database space. See https://sqlite.org/lang_vacuum.html for more information.

There isn't a single Insert or Delete statement in my code, the only operation I'm using is DBUtil's ExecuteMemoryTable.

 

[Erel]

I predict that Erel's advice will be to do everything asynchronously that can be done so (the SQL library has asynchronous methods) and only as a last resort should you consider using the Threading library.

+1

The Threading library will not solve any problem here (assuming that the bottle neck is the SQL engine). You can use the asynchronous methods which use a pool of threads internally.

However the first thing to do is to optimize the queries.
1. Are you using indices to access the data?
2. How many records are you loading at once? Are they all needed?

 

[wonder]

+1

The Threading library will not solve any problem here (assuming that the bottle neck is the SQL engine). You can use the asynchronous methods which use a pool of threads internally.

However the first thing to do is to optimize the queries.
1. Are you using indices to access the data?
2. How many records are you loading at once? Are they all needed?

The total number of tables being accessed is no more than 10 and the tables themselves are fairly small, the largest one has no more than 30 records. Worst case scenario, I'm importing 300 records. I will time the DB access and the data processing to find where the bottleneck is. I'm almost sure it's on the data processing. My code structure goes like this:

'Pseudo-code

Open SQLite DB
Load MasterTable 'SQLQuery via DBUtils ExecMemoryTable

For i = To (numberOfRecords - 1)
    Process MasterTable.Record(i)
        Case "Background"
            Load BackgroudsTable 'SQLQuery via DBUtils ExecMemoryTable
            Get Background(MasterTable(i)).Properties
        Case "Foreground"
            Load ForegroudsTable 'SQLQuery via DBUtils ExecMemoryTable
            Get Foreground(MasterTable(i)).Properties
        Case "Actor"
            Load ActorsTable 'SQLQuery via DBUtils ExecMemoryTable
            Get Actor(MasterTable(i)).Properties
        ...
Next

Setup GameAssets 'Based on the obtained data

Edit: Indeed, I might be able to transfer some of the heavy work done by B4X code into SQL queries.

 

[Erel]

So the problem is somewhere else. Loading a few hundred records from a small database is a very quick operation.

Make sure to measure the performance in release mode.

Add some performance measurements to your code:

Dim n As Long = DateTime.Now
...
Log("1: " & (DateTime.Now - n))

 

[wonder]

I will do so, as soon as I get home.

 

[mc73]

Sure it's the sqlite quering causing the anr? Somehow, I think it could be the screen's construction afterwards, if any.

 

[wonder]

I've found the bottleneck. It's not on the DB access. Turns out the problem is on my data processing code.
I will restructure it from scratch and as soon as I have it done, I will reevaluate the need of running it on a separate thread.