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

[Dennis Cote]

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