Android Question SQL Slow reading 5000 records...

[ronovar]

I have 5000 records in sqlite3 database and using SELECT on android box it takes about 30seconds to read from database...i im reading online to add index to columns...try this but is same..slow....how can i speed reading sql database?

 

[LucaMs]

Indexes are the only way to speed up access to DB records (but many people forget that indexes also slow down inserting).

It is better to choose number fields as indexes, instead of text fields.

 

[DonManfred]

upload you code. without seeing your code we just can guess.
Are you fetching a remote database or a local sqlite database on your device? Why are you fetching 3000 items? show 100 and reload more if the user need to see more...

 

[LucaMs]

show 100 and reload more if the user need to see more...

!

 

[Erel]

30 seconds for 5000 records is indeed slow. As @DonManfred wrote you should post the code.

 

[ronovar]

'DEFINE - Cursors
    Dim Cursor1 As Cursor
   
    'DISPLAY - Loading
    lblStatus.Text = "Loading From DataBase..."
   
    'INITIALIZE - DB
    SQL1.Initialize(File.DirDefaultExternal, Mac&".db", True)
   
    'QUERY - Total
    Total = SQL1.ExecQuerySingleResult("SELECT count(id) FROM table")

    'QUERY - Query
    Cursor1 = SQL1.ExecQuery("SELECT id, name, desc, year FROM table ORDER BY id DESC")

    'ADD - Items
    For i=0 To Cursor1.RowCount-1
        'INCREMENT - Cursor1
        Cursor1.Position = i
       
        'DISPLAY - Loading
        lblStatus.Text = "Loading From DataBase..."&Round((I)*100/Total)&"%"
       
        'UPDATE - ProgressBars
        prgStatus.Progress = Round((I)*100/Total)
       
        'DOEVENTS - Main
        DoEvents
    Next
   
    'DISPLAY - Loading OK
    lblStatus.Text = "Loading From DataBase...OK"

On server side in php i im using for INSERT

/* START - transaction */
$db->beginTransaction();

And

/* COMMIT - transaction */
$db->commit();

To insert 5000items in sqlite3 database on server it tooks 488ms. (Without beginTransaction it tok 13seconds)

Now i don't know how to use indexes to speed reading on android device...could i just insert on every field in sqlite3 database INDEX and then normal use SELECT like in above example? I try this but indexes not speed loading from sqlite3 database on android.

I test reading on two android box devices (dual core and quad core), and on same is slow...so problem is in the code.

 

[Mahares]

1. Get rid of this line:: DoEvents
2. You do not need the below line as it is the same as cursor1.Rowcount:
Total = SQL1.ExecQuerySingleResult("SELECT count(id) FROM table")
3. Only select the fields you want. if you do not need all these: id, name, desc, year
4. Initialize the database only on firsttime

 

[ronovar]

i need all this...abve is example fields name...i have 28 fields name in sql format database that is stored in android file.dirdefaultexternal folder...maybe is android rom slow?

Can you give me example code how to read indexes? On my mysql i added index to id field and then all runs faster...i know that android device have slow cpu but Amlogic S805 is the fastest cpu available for android and this must work without seconds to read all 5000items ( I know that i have intel androids but most people use cheap chinese amlogic boxes so i need to optimize it).

Do you have any code or idea how to speed?

I need to read all 5000items at apk start because this fills my ListView items...so when apk i sread user have 5000 items on screen and then can scrool usign remote control and don't need to wait to sql read next 14 items.

 

[DonManfred]

'DISPLAY - Loading
lblStatus.Text = "Loading From DataBase..."&Round((I)*100/Total)&"%"

'UPDATE - ProgressBars
prgStatus.Progress = Round((I)*100/Total)

'DOEVENTS - Main
DoEvents

As @Mahares already stated; remove these lines as you just use power for them.
Additional you are doing 10000 UI-changes for 5000 databaseentries (two changes each iteration) plus 5000 doevents.
It will be much faster if you dont use them.

 

[ronovar]

Thanks now is much mucj faster...but i don't now see progress bar because i remove doevents..is there another way to refresh progressbar and get progressbar on screen see how is loading in percentage?

 

[Mahares]

You can add the below line instead of DoEvents by itself. You can replace the 1000 with another number to suit your progressbar.
If i mod 1000 = 0 Then DoEvents

 

[DonManfred]

i suggest to update progress only every 500 items or so...

Something like

    For I=0 To Cursor1.RowCount
        If I mod 500 = 1 Then
    'DISPLAY - Loading
        Dim status As String = "Loading From DataBase..."&Round((I)*100/total)&"%"
        Dim progress As Float = Round((I)*100/total)
          CallSubDelayed3(Me,"UpdateUI",status,progress)
        End If
    Next
End Sub
Sub UpdateUI(statusinfo As String, progress As Float)
    Log($"UI-Update: ${statusinfo},${progress}"$)
    lblStatus.Text = statusinfo
    prgStatus.Progress = progress 
End Sub

 

[Erel]

Tip: Make sure to always test performance in release mode.