iOS Question Very slow resultset access loop

[DeclanOK]

Is there anyway to speed this up. takes about 3 minutes for a row count of 1200

[CODE lang="b4x" title="query"]Dim size1,size2, i, z As Int

size1 = SQLliteQuick.ExecQuerySingleResult("SELECT COUNT(*) FROM main order by rowid")

Dim R As ResultSet = SQLliteQuick.ExecQuery("SELECT rowid, attempt, attempted FROM main order by rowid")

i = 0

Dim old_attempted(size1) As Int

Dim old_attempts(size1) As Int



Do While R.NextRow





If R.GetString("attempt") = "True" Then

old_attempts(i) = 1

Else

old_attempts(i) = 0

End If

'If R.GetString("attempt") = "False" Then old_attempts(i) = 0





If R.GetString("attempted") = "True" Then

old_attempted(i) = 1

Else

old_attempted(i) = 0

End If

Log( R.GetString("rowid") & " " & old_attempts(i) & " - " & old_attempted(i))



i = i +1



Loop

Log("done")

R.Close

[/CODE]

 

[Alex_197]

Is there anyway to speed this up. takes about 3 minutes for a row count of 1200

[CODE lang="b4x" title="query"]Dim size1,size2, i, z As Int

size1 = SQLliteQuick.ExecQuerySingleResult("SELECT COUNT(*) FROM main order by rowid")

Dim R As ResultSet = SQLliteQuick.ExecQuery("SELECT rowid, attempt, attempted FROM main order by rowid")

i = 0

Dim old_attempted(size1) As Int

Dim old_attempts(size1) As Int



Do While R.NextRow





If R.GetString("attempt") = "True" Then

old_attempts(i) = 1

Else

old_attempts(i) = 0

End If

'If R.GetString("attempt") = "False" Then old_attempts(i) = 0





If R.GetString("attempted") = "True" Then

old_attempted(i) = 1

Else

old_attempted(i) = 0

End If

Log( R.GetString("rowid") & " " & old_attempts(i) & " - " & old_attempted(i))



i = i +1



Loop

Log("done")

R.Close

[/CODE]

Try to apply indexes https://www.sqlitetutorial.net/sqlite-index/

 

[DeclanOK]

Wow, amazing thanks - now instant

 

[emexes]

Wow, amazing thanks - now instant

I'm surprised that SQLite would take 3 minutes to sort 1200 results (twice?).

Perhaps also try removing the order by rowid from the SELECT COUNT (*) FROM main query.

Lol, if that saves you another 1.5 minutes then you'll now be getting results from 90 seconds in the future, which you can parlay into $$$ on the forex market or at the racetrack. ?

 

[OliverA]

Is there anyway to speed this up. takes about 3 minutes for a row count of 1200

This makes no sense unless you run it in debug mode, and even then that seems a little bit slow. Reading 1200 records and sorting them should take no time, even w/o indexes. Can you produce a demo program that reproduces this result? It would allow other people to check out what is going on.