Re: [sqlite] Very large database
Thanks you solved my problem. I was passing null instead of 0 to open_v2. I had
looked at the source before but it did not click. Also I had 3 routines in my
app each opening the database, doing some work then closing. The first routine
using sqlite3_exec the second sqlite_open/prep/step the third routine did
sqlite3_open_v2/prep_v2/step. I think mixing these in a single c executable
contributed to the problem.
Sincerely,
Severin Swensen
Data Engineering Dept.
Epsilon, Inc.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Thursday, March 13, 2008 10:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Very large database
[EMAIL PROTECTED] wrote:
> Is there any reason why a database of >20GiB would have problems
> being opened with sqlite3_open_v2 or have issues with sqlite3_prepare_v2.
No, not really. They oth call the same function to do the work. The
following is from the SQLite source:
/*
** Open a new database handle.
*/
int sqlite3_open(
const char *zFilename,
sqlite3 **ppDb
){
return openDatabase(zFilename, ppDb,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);
}
int sqlite3_open_v2(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb, /* OUT: SQLite db handle */
int flags, /* Flags */
const char *zVfs/* Name of VFS module to use */
){
return openDatabase(filename, ppDb, flags, zVfs);
}
The only difference may be the flags the user provides to the
sqlite3_open_v2() call. If you use the same flags as used by
sqlite3_open() it would be identical.
Similarly , the sqlite3_prepare() and the v2 function use the same
subroutine to do the work.
int sqlite3_prepare(
sqlite3 *db, /* Database handle. */
const char *zSql, /* UTF-8 encoded SQL statement. */
int nBytes, /* Length of zSql in bytes. */
sqlite3_stmt **ppStmt,/* OUT: A pointer to the prepared statement */
const char **pzTail /* OUT: End of parsed string */
){
int rc;
rc = sqlite3LockAndPrepare(db,zSql,nBytes,0,ppStmt,pzTail);
assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 ); /* VERIFY: F13021 */
return rc;
}
int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle. */
const char *zSql, /* UTF-8 encoded SQL statement. */
int nBytes, /* Length of zSql in bytes. */
sqlite3_stmt **ppStmt,/* OUT: A pointer to the prepared statement */
const char **pzTail /* OUT: End of parsed string */
){
int rc;
rc = sqlite3LockAndPrepare(db,zSql,nBytes,1,ppStmt,pzTail);
assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 ); /* VERIFY: F13021 */
return rc;
}
In this case the fourth argument tells the function to save the SQL so
it can be recompiled later if need be.
What kind of problems are you having? Can you be more specific.
HTH
Dennis Cote
___
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
Re: [sqlite] Very large database
[EMAIL PROTECTED] wrote:
> Is there any reason why a database of >20GiB would have problems
> being opened with sqlite3_open_v2 or have issues with sqlite3_prepare_v2.
No, not really. They oth call the same function to do the work. The
following is from the SQLite source:
/*
** Open a new database handle.
*/
int sqlite3_open(
const char *zFilename,
sqlite3 **ppDb
){
return openDatabase(zFilename, ppDb,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);
}
int sqlite3_open_v2(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb, /* OUT: SQLite db handle */
int flags, /* Flags */
const char *zVfs/* Name of VFS module to use */
){
return openDatabase(filename, ppDb, flags, zVfs);
}
The only difference may be the flags the user provides to the
sqlite3_open_v2() call. If you use the same flags as used by
sqlite3_open() it would be identical.
Similarly , the sqlite3_prepare() and the v2 function use the same
subroutine to do the work.
int sqlite3_prepare(
sqlite3 *db, /* Database handle. */
const char *zSql, /* UTF-8 encoded SQL statement. */
int nBytes, /* Length of zSql in bytes. */
sqlite3_stmt **ppStmt,/* OUT: A pointer to the prepared statement */
const char **pzTail /* OUT: End of parsed string */
){
int rc;
rc = sqlite3LockAndPrepare(db,zSql,nBytes,0,ppStmt,pzTail);
assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 ); /* VERIFY: F13021 */
return rc;
}
int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle. */
const char *zSql, /* UTF-8 encoded SQL statement. */
int nBytes, /* Length of zSql in bytes. */
sqlite3_stmt **ppStmt,/* OUT: A pointer to the prepared statement */
const char **pzTail /* OUT: End of parsed string */
){
int rc;
rc = sqlite3LockAndPrepare(db,zSql,nBytes,1,ppStmt,pzTail);
assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 ); /* VERIFY: F13021 */
return rc;
}
In this case the fourth argument tells the function to save the SQL so
it can be recompiled later if need be.
What kind of problems are you having? Can you be more specific.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Very large database
Is there any reason why a database of >20GiB would have problems being opened with sqlite3_open_v2 or have issues with sqlite3_prepare_v2. I am running on a RHEL 5 x86_64 server. Accessing the database via the legacy open and prepare functions works fine as well as the exec function. The database contains a single table with 4 integer keys and a 1300 byte string column. Thanks, Severin Swensen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] very large database file
On Thu, 22 Jul 2004, Will Leshner wrote: >Ok. I think I know what is going on. Each of the rows in my database >is "too big" to fit on the main page (about 320 bytes for each row). >So each one is forcing an overlow page. I'm assuming that each >overflow page only stores one row of data. In other words, the >overflow pages don't share rows as the main pages do. So since my rows >are just a little too big to fit on the main page, most of each >overflow page is unused. And since just about every one of my 80,000 >rows is creating a 1k overflow page that is largely unused, I'm ending >up with a very large database file that is made up mostly of unused >space. > >Does that sound about right? That'll be right. You might want to try SQLite 3, as 3.0.3 has variable page size enabled, allowing you to use bigger pages which can store larger rows without overflowing[0]. To do the same with SQLite 2 would require a recompile and incompatible file with default SQLite 2 files. Christian [0] Each btree page can hold at least 4 cells. Increase page size, and cell size goes up. -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] very large database file
Ok. I think I know what is going on. Each of the rows in my database is "too big" to fit on the main page (about 320 bytes for each row). So each one is forcing an overlow page. I'm assuming that each overflow page only stores one row of data. In other words, the overflow pages don't share rows as the main pages do. So since my rows are just a little too big to fit on the main page, most of each overflow page is unused. And since just about every one of my 80,000 rows is creating a 1k overflow page that is largely unused, I'm ending up with a very large database file that is made up mostly of unused space. Does that sound about right?
Re: [sqlite] very large database file
Will Leshner wrote: I have a database that's been created with 2.8.14 and it has grown to 100M. When I zip it, however, I end up with a file that's about 1M. So I'd kind of like to try to figure out where all the space is going. I'd heard there was a tool for analyzing SQLite databases, but I can't seem to track it down on the website. Is there such a tool and will it help me figure out what might be going on with my database file? http://www.sqlite.org/sqlite_analyzer-2.8.14.bin.gz (linux) http://www.sqlite.org/sqlite_analyzer-2_8_14.zip (windows) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] very large database file
I have a database that's been created with 2.8.14 and it has grown to 100M. When I zip it, however, I end up with a file that's about 1M. So I'd kind of like to try to figure out where all the space is going. I'd heard there was a tool for analyzing SQLite databases, but I can't seem to track it down on the website. Is there such a tool and will it help me figure out what might be going on with my database file? Thanks.
