Android Question How to speedup SQLite database process

[ducphu]

Hi all,

When my activity is paused, I need to write all the information to the sqlite database. The code is:

Sub Activity_Pause (UserClosed As Boolean)
    'Write map to database
    If Main.SQL.IsInitialized = False Then Main.SQL.Initialize(File.DirInternal, "database.db3", False)
    Main.SQL.ExecNonQuery("UPDATE general SET play_status = 2 WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET move_cap_lb = " & "'" & move_cap_lb.tag & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET score = " & "'" & score & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET bonus = " & "'" & bonus & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET level = " & "'" & level & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET speed = " & "'" & speed & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET total_virus = " & "'" & total_virus & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET remain_virus = " & "'" & remain_virus & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET cap_orientation = " & "'" & cap_orientation & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET cap_direction = " & "'" & cap_direction & "'" & " WHERE id = 0")
    Main.SQL.ExecNonQuery("UPDATE general SET stop_top = " & "'" & stop_top & "'" & " WHERE id = 0")
   
    Dim col, j_string As String
    For i = 0 To 7
        For j = 0 To 15
            col = "Col" & i
            j_string = j
            Main.SQL.ExecNonQuery("UPDATE map_tag SET " & "'" & col & "'" & " = " & "'" & map(i,j).Tag & "'" & " WHERE Row = " & "'" & j_string & "'")
            Main.SQL.ExecNonQuery("UPDATE map_text SET " & "'" & col & "'" & " = " & "'" & map(i,j).Text & "'" & " WHERE Row = " & "'" & j_string & "'")
            Main.SQL.ExecNonQuery("UPDATE move_map_tag SET " & "'" & col & "'" & " = " & "'" & move_map(i,j).Tag & "'" & " WHERE Row = " & "'" & j_string & "'")
            Main.SQL.ExecNonQuery("UPDATE move_map_text SET " & "'" & col & "'" & " = " & "'" & move_map(i,j).Text & "'" & " WHERE Row = " & "'" & j_string & "'")
        Next
    Next
   
    Main.SQL.Close
   
    If AnimSetA.isStarted Then
        AnimSetA.Cancel
        playing_anim = "A"
    End If
    If AnimSetB.isStarted Then
        AnimSetB.Cancel
        playing_anim = "B"
    End If
    For i = 0 To 7
        For j = 0 To 15
            If AnimSetCD(i,j).isStarted Then
                AnimSetCD(i,j).Cancel
                playing_anim_cd(i,j) = "1"
            End If
            If AnimSetE(i,j).isStarted Then
                AnimSetE(i,j).Cancel
                playing_anim_e(i,j) = "1"
            End If
        Next
    Next
   
    drop_btn.Enabled = False
    rotate_btn.Enabled = False
    left_btn.Enabled = False
    right_btn.Enabled = False
       
    pause_btn.Visible = False
    pause_btn.Enabled = False
    pause_btn.SendToBack
   
    play_btn.Visible = True
    play_btn.Enabled = True
    play_btn.BringToFront
   
    game_pause = True

End Sub

When the above code is run, it take few sec to finish and hence the activity takes few sec until it responds (e.g close) Is there way to improve the above code to make it more responsive? Thank you

 

[sorex]

you should use transactions then everything gets queued and executed at once.

do a search on transaction.

 

[sorex]

https://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content

int eh first post and just above BLOB are transaction examples

 

[Roycefer]

You should be using the asynchronous calls so that the database operations don't block the main thread. You might also consider using the batch processing mechanism, though that probably won't speed the overall time it takes it to execute.

Take a look at SQL.AddToBatch and SQL.ExecNonQueryBatch

 

[ducphu]

Thank sorex and Roycefer,

Using transaction helped.

 

[Mahares]

Main.SQL.ExecNonQuery("UPDATE general SET play_status = 2 WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET move_cap_lb = " & "'" & move_cap_lb.tag & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET score = " & "'" & score & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET bonus = " & "'" & bonus & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET level = " & "'" & level & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET speed = " & "'" & speed & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET total_virus = " & "'" & total_virus & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET remain_virus = " & "'" & remain_virus & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET cap_orientation = " & "'" & cap_orientation & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET cap_direction = " & "'" & cap_direction & "'" & " WHERE id = 0")
Main.SQL.ExecNonQuery("UPDATE general SET stop_top = " & "'" & stop_top & "'" & " WHERE id = 0")

You can additionally optimize your above code by having only one parameterized query SQL statement as shown below, instead of eleven statements. Otherwise, you can easily get caught in the single quotes spider web and have a hard time finding your way out:

Main.SQL.ExecNonQuery2("UPDATE general SET play_status=?, move_cap_lb=?, score=?, bonus=?, " _
& "level=?, speed=?, total_virus=?, remain_virus=?, cap_orientation=?, cap_direction=?, stop_top=?  " _
& " WHERE id=?", Array As Object(2, move_cap_lb.tag, score, bonus, level, speed, total_virus, remain_virus, _
cap_orientation, cap_direction, stop_top, 0 ))