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

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

 

[Alex_197]

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

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

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.