Android Question Cursor window allocation of 2048 kb failed

[jpvniekerk]

I have a SQLite database and get an error when running a certain subroutine.

The error is:
android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed. # Open Cursors=963 (# cursors opened by this proc=963)

The code is:

  Dim Rows As Int
   Dim Data As List
   Data.Initialize
   Rows = Cursor1.RowCount

'Fill Data
    For row = 0 To Rows - 1
        Cursor1.Position = row
        Dim values(19) As String
        Dim cur2 As Cursor
        Dim cur As Cursor
        cur2 = SQL1.ExecQuery("SELECT * from Ticklist where TickID = "&Cursor1.GetString("TickID"))
        cur2.Position = 0
        'Bird Name
        Tmp = Cursor1.GetString("BirdID")        'BirdID
        cur = SQL1.ExecQuery2("SELECT ClemEng, ClemSci from BirdList WHERE BirdID = ?", Array As String(Tmp))
        cur.Position = 0

         '... fill values() with getstring2 commands...
        values(0) = cur.GetString2(0)
         '...
        values(3) = cur2.GetString2(7)
         '... etc

        Data.Add(values)
        cur.Close
        cur2.Close
    Next

When I have less than ± 900 rows in cursor1, everything works fine, but with a lot of data (looks like more than ± 960 rows) I get the error.

I thought that cur.close and cur2.close would prevent multiple cursors opening and filling the Android allocation, but it seems I still get too many cursors opened.

How can I fix this issue?

 

[stevel05]

Maybe this will help: http://stackoverflow.com/questions/...base-in-android-cursor-size-is-limited-to-1mb

 

[jpvniekerk]

Thanks, but this was not the problem. My whole database is only 500KB, and the tables used in this specific subroutine is less than 20% of that - so maybe 100KB.

I have found the problem to be multiple use of the same cursor inside the loop. I had:
cur = SQL1.ExecQuery2(SELECT some stuff from a table...)
and then later in the loop I redefined cur
cur = SQL1.ExecQuery2(SELECT other stuff from another table...)
at the end of the loop I cleared cur, but it did not solve the problem.

When I changed the second instance to cur1 (dim'ed upfront) and cleared it inside the end of the loop, the problem went away.

This almost looks like a bug to me... if you redefine a cursor before clearing it, it seems to stay in memory, even if you clear it afterwards.

To summarize:
This runs into a problem with a big loop size (> 500 records in my case)

Dim Rows As Int
   Rows = Cursor1.RowCount

'Fill Data
    For row = 0 To Rows - 1
        Cursor1.Position = row
        Dim cur As Cursor
        cur = SQL1.ExecQuery2(SELECT some stuff from a table...)
        cur.Position = 0
          ' do something with cur
        cur = SQL1.ExecQuery2(SELECT other stuff from another table...)
        cur.Position = 0
          ' do something with cur
        cur.Close
    Next

This works fine even with more than 6000 records:

Dim Rows As Int
   Rows = Cursor1.RowCount

'Fill Data
    For row = 0 To Rows - 1
        Cursor1.Position = row
        Dim cur As Cursor
        Dim cur1 As Cursor
        cur = SQL1.ExecQuery2(SELECT some stuff from a table...)
        cur.Position = 0
          ' do something with cur
        cur1 = SQL1.ExecQuery2(SELECT other stuff from another table...)
        cur1.Position = 0
          ' do something with cur1
        cur.Close
        cur1.Close 
    Next

Moral of the story: Don't redefine a cursor inside a loop - it creates a memory leak!

 

[stevel05]

It's difficult to say without knowing the structure of your data but, if you are reading data from multiple related tables, I would suggest that you look at using the SQLite JOIN method and read it all in one query.

 

[Erel]

I agree with @stevel05. Your code is very inefficient. The power of SQL comes from the ability to extract data from multiple tables (join).

Based on the error message you are not closing all the cursors.