Re: [sqlite] Reducing SQLite Memory footprint(!)

[Brown, Daniel]

Thanks Dennis,

I just tried with rebuilt data from SQLite Analyzer in SQLite 3.6.1, I'm
still seeing a memory usage that is roughly three times the size of the
source database file, looking at your changes to my test there doesn't
seem to be any fixes that would resolve that.

I can see the memory being released when I close the SQLite database in
the teardown stage of my test, so I'm fairly sure the memory is being
used by SQLite and the built in memory profiling would seem to support
that.  I haven't had to make any changes locally to get the PC version
of 3.6.1 compiling so I don't think that is the issue, could it be some
sort of configuration or library issue?  I'm building in Visual Studio
2005 SP1.

Daniel


-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Wednesday, August 20, 2008 3:45 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)

Brown, Daniel wrote:
> I just upgraded to the latest version (3.6.1) of the pre-processed C
> source code from the website, running the test again gives me similar
> results of 22.2 MB used and 24.55 MB high water from the same 9 MB
file.
> Is there any way it could be the file that is causing the extra memory
> usage?  I'll try rebuilding it next.
> 

I just tried your test code with sqlite 3.6.1 amalgamation source on Win

XP and I get the following output from a 17.5 MB (18,362,368 bytes) 
database file.

     Entries.db   Used: 18049 KB   High: 20357 KB

I had to make a few changes to your code to get it to run. I have copied

the modified code below. This was built with Dev-Cpp using GCC 3.4.2.

There must be some other issue with your program that is causing the 
inflated memory usage you are seeing.

Dennis Cote




#include <cstdlib>
#include <cstdio>
#include <sqlite3.h>

using namespace std;

int main(int argc, char *argv[])
{
        sqlite3* pDataBase = NULL;
     const char* ptail = NULL;
        sqlite3_stmt* pstatement = NULL;
        int result = -1;
        int cmdSize = 0;
        const int cmdBufferSize = 1024;
        char cmdBuffer[cmdBufferSize];
        const char* pdatabaseName = "file_db";
        const char* pfilename = argv[1];

        sqlite3_open( ":memory:", &pDataBase );

        // create the attach command
        cmdSize = sprintf( cmdBuffer, "ATTACH DATABASE '%s' AS %s",
pfilename, 
pdatabaseName );

        // attach the on-disk database with ATTACH filename.db AS
filename
        result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL, NULL );

        // You can enumerate all tables in a your on-disk-file in the
mentioned 
scenario by
        // doing a "SELECT tbl_name FROM filename.sqlite_master WHERE
type = 
'table'".
        cmdSize = sprintf( cmdBuffer, "SELECT tbl_name FROM
%s.sqlite_master 
WHERE type = 'table'", pdatabaseName );

        // prepare the statement
        result = sqlite3_prepare_v2( pDataBase, cmdBuffer, cmdSize, 
&pstatement, &ptail);

        while( sqlite3_step( pstatement) == SQLITE_ROW)
        {
                // Then do a CREATE TABLE tableName AS SELECT * FROM 
filename.tableName On each table in the file,
                // thus creating an in-memory copy of the DB and having
done a select 
on each table (i.e. you'll see how                      // much cache in
memory will be 
used, etc.)     

                // get the table name
                const unsigned char* pname = sqlite3_column_text(
pstatement, 0);

                // construct the command
                cmdSize = sprintf( cmdBuffer, "CREATE TABLE %s AS SELECT
* FROM 
%s.%s", pname, pdatabaseName, pname );

                result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL,
NULL );
        }

        sqlite3_finalize(pstatement);

        // detach the attached database to leave just the in memory
database
        cmdSize = sprintf( cmdBuffer, "DETACH DATABASE %s",
pdatabaseName );

        result = sqlite3_exec( pDataBase, cmdBuffer, NULL, NULL, NULL );

        sqlite_int64 memHigh = sqlite3_memory_highwater(0);
        sqlite_int64 memUsed = sqlite3_memory_used();

        printf("%s   Used: %d KB   High: %d KB\n", pfilename, 
(int)(memUsed/1024), (int)(memHigh/1024));

        sqlite3_close( pDataBase );

     system("PAUSE");
     return EXIT_SUCCESS;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users