SELECT huge memory usage compared to native library

[GoogleCodeExporter]

First of all, thnaks for providing us this very useful library!

I use version 3.7.7.1 in my project.
I have a table that contains almost 1 million rows (but my problem is identical 
with less data). My schema is:

CREATE TABLE "/" (id INTEGER, parentid INTEGER, taskid INTEGER, name TEXT, 
chunk INTEGER, changestatus INTEGER, ispartial INTEGER, data BLOB );

I run the following query :
SELECT * from mytable

Then I iterate through the results using a DataReader.

Using the native Sqlite, the memory used by my app is below 35MB.
Using csharp-sqlite, my app uses a lot of memory, a little bit more than the 
total sqlite database size. This makes me assume that, maybe, csharp-sqlite 
loads the whole table in memory when I ran my query.

Is this an expected behaviour? 
Would it be because some temporary tables are involved (although there is no 
sorting or joins in my query), and these tables default to be stored on disk 
with native sqlite, while csharp-sqlite keeps them in memory?
Does your C# implementation has a different memory allocation implementation 
that could explain that?

Here is the code I use:

    SqliteConnection indexDbConn = new SqliteConnection();
    indexDbConn.ConnectionString = "Version=3,Synchronous=off,data source=file:t32100.idx";
    string rootDriveName = "/";
    string query = "SELECT * FROM \""+rootDriveName+"\"";
    using(System.Data.IDbCommand itemC =  indexDbConn.CreateCommand()){
        itemC.CommandText = query;
        System.Data.IDataReader reader = itemC.ExecuteReader();
        while(reader.Read()){
            // processing
        }
    }

I've tried to set up these options before running my query, but they didn't 
help:
PRAGMA cache_size=500
PRAGMA temp_store=1

What steps will reproduce the problem?
1. run a similar code
2. watch the memory usage grow until the process use >= queried database file 
size
3. replace the "using" statement to use Mono.Data.Sqlite, and the connection 
string to use "URI=" instead of "data source="
4. run the code again. This time, the memory usage stays very low before, 
during and after the query

What is the expected output? What do you see instead?
I wouldn't mind if csharp-sqlite used more memory than the native version, but 
I wasn't expecting it to look like if it put the whole table in memory.

What version of the product are you using? On what operating system?
3.7.7.1 on Linux (mono 2.10.9). But same problem using .NET 4.0 on windows XP.

Please provide any additional information below.
Let me know anything I can do to help the diagnosis.

Thanks for your help!

Original issue reported on code.google.com by bonsou...@gmail.com on 7 Aug 2012 at 7:41

[GoogleCodeExporter]

The native version manages it's memory via a private memory allocation/recovery 
routines. The C# versions allow the operating system to manage memory via the 
garbage collector.  I would not be surprised to see the behavior you mention.

There is an experimental C# compiler flag, that tries to reuse memory, rather 
than allocate/release it.  Try with the SQLITE_POOL_MEM flag set

Original comment by noah.hart@gmail.com on 7 Aug 2012 at 8:59

• Changed state: NoAction

[GoogleCodeExporter]

Thanks for your help, I'll rebuild the library with this option.
I'll let you know if it mitigates the problems!

Original comment by bonsou...@gmail.com on 8 Aug 2012 at 6:24