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

[Brown, Daniel]

sqlite3_memory_highwater() ~ 25673060
sqlite3_memory_used() ~ 23222709

I'm doing the following in C++ (I test all return codes but removed the tests 
to save bandwidth):
        
        const char* ptail = NULL;
        sqlite3_stmt* pstatement = NULL;
        int result = -1;
        int cmdSize = 0;
        const int cmdBufferSize = 1024;
        char cmdBuffer[cmdBufferSize];

        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_s( 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 %d KB High %d KB", pfilename, (memUsed/1024), 
(memHigh/1024));

        sqlite3_close( pDataBase );

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Wednesday, August 20, 2008 11:00 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)


On Aug 20, 2008, at 1:53 PM, Brown, Daniel wrote:

> Looking in process explorer on XP after the disc database detached
> should a memory size change of 28 MB of RAM in the test application, I
> assumed this was the size of the database in memory.


That would be the peak memory usage by the application.  It is not at  
all clear to me that SQLite was using all 28 MB.  What does  
sqlite3_memory_highwater() tell you?


D. Richard Hipp
[EMAIL PROTECTED]



_______________________________________________
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