Re: [sqlite] Sqlite Endianness
Thanks, I saw that, but it doesn't *really* specify which endianness the file uses, or even if it depends on what type of machine the database was created on. We know we can create it on the PC and load it on the Xbox360, but I wonder if we pay a performance penalty for doing so. I'm going to run a little experiment now and create the same database on both machines and binary compare the two... Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies Sent: Friday, July 25, 2008 8:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite Endianness 2008/7/25 Dave Gierok <[EMAIL PROTECTED]>: > Does anyone know what endianness a Sqlite database is stored in? Or does > that depend on the endianness of the machine it was created on? I am > wondering because we ship a game that runs on Xbox 360, which uses the > PowerPC architecture and has a different endianness than the PC. We run PC > tools to create our database for the game. I wonder if we are paying a > performance overhead on the Xbox if the database essentially needs to be > 'converted' runtime. > > Thanks, > Dave see heading "Stable Cross-Platform Database File" on http://www.sqlite.org/different.html Rgds, Simon ___ 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
[sqlite] Sqlite Endianness
Does anyone know what endianness a Sqlite database is stored in? Or does that depend on the endianness of the machine it was created on? I am wondering because we ship a game that runs on Xbox 360, which uses the PowerPC architecture and has a different endianness than the PC. We run PC tools to create our database for the game. I wonder if we are paying a performance overhead on the Xbox if the database essentially needs to be 'converted' runtime. Thanks, Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RE: Crash in prepSelectStmt
Ahh, sorry about that stack. It's not actually a trashed call stack. The
variables are not accurate here because it is an optimized build's call stack.
The reason I included it was merely to show the function-call-stack, which is
accurate.
____
From: Dave Gierok
Sent: Thursday, March 29, 2007 10:18 AM
To: 'sqlite-users@sqlite.org'
Subject: Crash in prepSelectStmt
I'm getting a very occational crash in prepSelectStmt when trying to run the
following statement:
CREATE TABLE tmpdb.[Profile0_Career_UnlockedCars] AS SELECT * FROM
[Profile0_Career_UnlockedCars];
Where another tmpdb has been attached to the main db via ATTACH.
My call stack is:
* forza2_ply.dll!prepSelectStmt(Parse * pParse=0x6a99bbd0, Select *
p=0x) line 1326 + 0x18 bytes C
* forza2_ply.dll!sqlite3SelectResolve(Parse * pParse=0x,
Select * p=0x, NameContext * pOuterNC=0x) line 2524 + 0xC
bytesC
* forza2_ply.dll!sqlite3Select(Parse * pParse=0x, Select *
p=0x, int eDest=0x, int iParm=0x, Select *
pParent=0x, int parentTab=0x, int * pParentAgg=0x, char
* aff=0x) line 2811 + 0x10 bytes C
* forza2_ply.dll!sqlite3EndTable(Parse * pParse=0x, Token *
pCons=0x, Token * pEnd=0x, Select * pSelect=0x) line
1464C
* forza2_ply.dll!yy_reduce(yyParser * yypParser=0x, int
yyruleno=0x) line 156 + 0x18 bytes C
* forza2_ply.dll!sqlite3Parser(void * yyp=0x, int
yymajor=0x, Token yyminor={...}, Parse * pParse=0x) line 3234
+ 0xC bytesC
* forza2_ply.dll!sqlite3RunParser(Parse * pParse=0x, const
char * zSql=0x, char * * pzErrMsg=0x) line 444 C
* forza2_ply.dll!sqlite3_prepare(sqlite3 * db=0x, const char *
zSql=0x, int nBytes=0x, sqlite3_stmt * * ppStmt=0x,
const char * * pzTail=0x) line 492 C
It is crashing (Access violation reading invalid pointer) on the line:
setToken(&pRight->token, zName);
Is there a known bug? Could we be setting up something wrong? Anyone have any
advice on how to debug this? I'd hate to ship our game with this but we're
getting down to the wire and it looks like we may need to do just that.
Thanks,
Dave
[sqlite] Crash in prepSelectStmt
I'm getting a very occational crash in prepSelectStmt when trying to run the
following statement:
CREATE TABLE tmpdb.[Profile0_Career_UnlockedCars] AS SELECT * FROM
[Profile0_Career_UnlockedCars];
Where another tmpdb has been attached to the main db via ATTACH.
My call stack is:
* forza2_ply.dll!prepSelectStmt(Parse * pParse=0x6a99bbd0, Select *
p=0x) line 1326 + 0x18 bytes C
* forza2_ply.dll!sqlite3SelectResolve(Parse * pParse=0x,
Select * p=0x, NameContext * pOuterNC=0x) line 2524 + 0xC
bytesC
* forza2_ply.dll!sqlite3Select(Parse * pParse=0x, Select *
p=0x, int eDest=0x, int iParm=0x, Select *
pParent=0x, int parentTab=0x, int * pParentAgg=0x, char
* aff=0x) line 2811 + 0x10 bytes C
* forza2_ply.dll!sqlite3EndTable(Parse * pParse=0x, Token *
pCons=0x, Token * pEnd=0x, Select * pSelect=0x) line
1464C
* forza2_ply.dll!yy_reduce(yyParser * yypParser=0x, int
yyruleno=0x) line 156 + 0x18 bytes C
* forza2_ply.dll!sqlite3Parser(void * yyp=0x, int
yymajor=0x, Token yyminor={...}, Parse * pParse=0x) line 3234
+ 0xC bytesC
* forza2_ply.dll!sqlite3RunParser(Parse * pParse=0x, const
char * zSql=0x, char * * pzErrMsg=0x) line 444 C
* forza2_ply.dll!sqlite3_prepare(sqlite3 * db=0x, const char *
zSql=0x, int nBytes=0x, sqlite3_stmt * * ppStmt=0x,
const char * * pzTail=0x) line 492 C
It is crashing (Access violation reading invalid pointer) on the line:
setToken(&pRight->token, zName);
Is there a known bug? Could we be setting up something wrong? Anyone have any
advice on how to debug this? I'd hate to ship our game with this but we're
getting down to the wire and it looks like we may need to do just that.
Thanks,
Dave
RE: [sqlite] ATTACH and :memory: databases
I have one :memory: database open and I want to make a copy of it into another :memory: database. When I open the 2nd :memory: database and call attach on the first one specifying ':memory:' as the file name, it allows me to do this. However when I loop through the tables copying them into the 2nd one, it is still empty after this has been done. I think the first :memory: database might be attaching to, uh, itself. Is there a way to specify the name of a memory database uniquely? Something like ':memory:2'? Nothing that I've tried works. -Dave Gierok -Original Message- From: Dan Kennedy [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 07, 2007 9:56 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] ATTACH and :memory: databases On Wed, 2007-02-07 at 09:37 -0800, Dave Gierok wrote: > I would like to attach a :memory: database to another :memory: database. How > is this possible since the ATTACH command requires the database's file name? > > ATTACH [DATABASE] database-filename AS database-name > > -Dave Gierok I don't think there are any special issues. Use ':memory:' as the filename. [EMAIL PROTECTED]:~> sqlite3 :memory: SQLite version 3.2.8 Enter ".help" for instructions sqlite> attach ':memory:' as db2; sqlite> attach ':memory:' as db3; sqlite> create table db2.abc(a, b, c); sqlite> select * from sqlite_master; sqlite> select * from db3.sqlite_master; sqlite> select * from db2.sqlite_master; table|abc|abc|2|CREATE TABLE abc(a, b, c) sqlite> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ATTACH and :memory: databases
I would like to attach a :memory: database to another :memory: database. How is this possible since the ATTACH command requires the database's file name? ATTACH [DATABASE] database-filename AS database-name -Dave Gierok
RE: RE: [sqlite] DROP INDEX not freeing up memory
Well, those extra 2MB sure would be nice when we go 'in-game'. We primarily use Sqlite while we're 'in-UI' and when we transition to going in-game, we try to free up as much memory as possible. Thus, our usage pattern of creating/dropping the same indices over and over. We might be able to find 2MB elsewhere, or cut down other in-game memory usage...but like I said, 2MB is like gold in the world of game development. Thanks, Dave -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Thursday, January 18, 2007 6:18 AM To: sqlite-users@sqlite.org Subject: RE: RE: [sqlite] DROP INDEX not freeing up memory Do you have to drop the index? Why not just keep it around, its obviously useful if you need to create it in the first place right? Dave Gierok <[EMAIL PROTECTED]> wrote: It does in fact look like the memory isn't being freed up entirely. I am properly tracking xMalloc, xRealloc, and xFree. I have a memory database and wrote some test code to loop a few times creating/dropping the same index. The results of that are (numbers are total bytes allocated): 7632746 Before 1st Create Index 7637587 After 1st Create Index 7637434 After 1st Drop Index (and Vacuum -- the vacuum doesn't change memory allocated though) 7637587 After 2nd Create Index 7637434 After 2nd Drop Index (and Vacuum) 7637587 After 3rd Create Index 7637434 After 3rd Drop Index (and Vacuum) Notice that the memory slightly decreases after the 1st Drop Index, but doesn't nearly drop to what it should (it should drop to 7632746 -- the same level as before the 1st Create Index). Also notice that after the 1st create/drop, the memory allocated is the same after each create and after each drop. So it implies there is not a leak -- but we can't get down to the original level before we created the first index. So what's the big deal you might ask -- this example shows 5K that can't be reclaimed. But in our game we create hundreds of indices that take up about 2MB -- 2MB is quite a bit of memory in our world. Any suggestions or explanations? Thanks, Dave -Original Message- From: Dave Gierok [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 9:06 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] DROP INDEX not freeing up memory OK, thanks, I'll do some more digging and let you know. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 8:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DROP INDEX not freeing up memory Dave Gierok wrote: > xTruncate is not being called because this is an in-memory database. Memor= > y databases should have memory freed instead I assume? > In-memory databases call sqliteFree() to release their memory. I checked, and this does appear to work. Perhaps the sqliteFree() call is somehow not making it down into your measurement layer. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: RE: [sqlite] DROP INDEX not freeing up memory
It does in fact look like the memory isn't being freed up entirely. I am properly tracking xMalloc, xRealloc, and xFree. I have a memory database and wrote some test code to loop a few times creating/dropping the same index. The results of that are (numbers are total bytes allocated): 7632746 Before 1st Create Index 7637587 After 1st Create Index 7637434 After 1st Drop Index (and Vacuum -- the vacuum doesn't change memory allocated though) 7637587 After 2nd Create Index 7637434 After 2nd Drop Index (and Vacuum) 7637587 After 3rd Create Index 7637434 After 3rd Drop Index (and Vacuum) Notice that the memory slightly decreases after the 1st Drop Index, but doesn't nearly drop to what it should (it should drop to 7632746 -- the same level as before the 1st Create Index). Also notice that after the 1st create/drop, the memory allocated is the same after each create and after each drop. So it implies there is not a leak -- but we can't get down to the original level before we created the first index. So what's the big deal you might ask -- this example shows 5K that can't be reclaimed. But in our game we create hundreds of indices that take up about 2MB -- 2MB is quite a bit of memory in our world. Any suggestions or explanations? Thanks, Dave -----Original Message- From: Dave Gierok [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 9:06 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] DROP INDEX not freeing up memory OK, thanks, I'll do some more digging and let you know. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 8:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DROP INDEX not freeing up memory Dave Gierok <[EMAIL PROTECTED]> wrote: > xTruncate is not being called because this is an in-memory database. Memor= > y databases should have memory freed instead I assume? > In-memory databases call sqliteFree() to release their memory. I checked, and this does appear to work. Perhaps the sqliteFree() call is somehow not making it down into your measurement layer. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DROP INDEX not freeing up memory
OK, thanks, I'll do some more digging and let you know. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 8:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DROP INDEX not freeing up memory Dave Gierok <[EMAIL PROTECTED]> wrote: > xTruncate is not being called because this is an in-memory database. Memor= > y databases should have memory freed instead I assume? > In-memory databases call sqliteFree() to release their memory. I checked, and this does appear to work. Perhaps the sqliteFree() call is somehow not making it down into your measurement layer. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DROP INDEX not freeing up memory
xTruncate is not being called because this is an in-memory database. Memory
databases should have memory freed instead I assume?
Thanks,
Dave
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 16, 2007 3:07 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DROP INDEX not freeing up memory
Dave Gierok <[EMAIL PROTECTED]> wrote:
>
> I am running an in-memory db and am using the functionality where
> we override all the OS calls such that we implement our own memory
> management and file IO. Therefore, I am able to easily track the
> amount of memory Sqlite is taking up. I have noticed that when I
> call CREATE INDEX and then the corresponding DROP INDEX, I do not
> see the memory drop to the same level as it was before the CREATE
> INDEX. I also call VACUUM after DROP INDEX, which makes no difference.
>
I tried this using the built-in database functionality and the
memory is being freed there. I did:
CREATE TABLE t1(x);
INSERT INTO t1 VALUES('hello');
CREATE INDEX i1 ON t1(x);
DROP INDEX i1;
VACUUM;
And then set a breakpoint at pager.c:1972 where the freeing of
memory occurs, and it does happen.
But I have no way to trouble-shoot your overridden OS calls.
The first place I would look would be in your implementation
of sqlite3OsTruncate(). Are you sure you are doing it right?
Does sqlite3OsTruncate() get called when you VACUUM?
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] DROP INDEX not freeing up memory
But the docs say VACUUM is supposed to free that memory which it doesn't look like it is doing. -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 16, 2007 3:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DROP INDEX not freeing up memory Sqlite is allocating cache memory and reusing it. That is not a bug, it is a beneficial feature. Dave Gierok wrote: > Yes, I did try that -- I should have mentioned it. When I create and drop > the index again, the amount of memory that Sqlite takes is effectively the > same. That is, to create the index once costs a lot of memory, and then > future deletions/creations does not change the amount of memory allocated. > > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 16, 2007 1:56 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] DROP INDEX not freeing up memory > > Dave Gierok wrote: > >>(I'm resending this, as I have not seen it posted the first time I sent - >>apologies if it gets posted twice) >> >>I am running an in-memory db and am using the functionality where we override >>all the OS calls such that we implement our own memory management and file >>IO. Therefore, I am able to easily track the amount of memory Sqlite is >>taking up. I have noticed that when I call CREATE INDEX and then the >>corresponding DROP INDEX, I do not see the memory drop to the same level as >>it was before the CREATE INDEX. I also call VACUUM after DROP INDEX, which >>makes no difference. >> >>Is this expected, or does it sound like a bug in Sqlite? >> >>Thanks, >>Dave >> >> > > What happens if you create and drop the index again? Do that to see if > you are just seeing an effect of assigning cache on the first operation. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DROP INDEX not freeing up memory
Yes, I did try that -- I should have mentioned it. When I create and drop the index again, the amount of memory that Sqlite takes is effectively the same. That is, to create the index once costs a lot of memory, and then future deletions/creations does not change the amount of memory allocated. -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 16, 2007 1:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DROP INDEX not freeing up memory Dave Gierok wrote: > (I'm resending this, as I have not seen it posted the first time I sent - > apologies if it gets posted twice) > > I am running an in-memory db and am using the functionality where we override > all the OS calls such that we implement our own memory management and file > IO. Therefore, I am able to easily track the amount of memory Sqlite is > taking up. I have noticed that when I call CREATE INDEX and then the > corresponding DROP INDEX, I do not see the memory drop to the same level as > it was before the CREATE INDEX. I also call VACUUM after DROP INDEX, which > makes no difference. > > Is this expected, or does it sound like a bug in Sqlite? > > Thanks, > Dave > > What happens if you create and drop the index again? Do that to see if you are just seeing an effect of assigning cache on the first operation. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DROP INDEX not freeing up memory
(I'm resending this, as I have not seen it posted the first time I sent - apologies if it gets posted twice) I am running an in-memory db and am using the functionality where we override all the OS calls such that we implement our own memory management and file IO. Therefore, I am able to easily track the amount of memory Sqlite is taking up. I have noticed that when I call CREATE INDEX and then the corresponding DROP INDEX, I do not see the memory drop to the same level as it was before the CREATE INDEX. I also call VACUUM after DROP INDEX, which makes no difference. Is this expected, or does it sound like a bug in Sqlite? Thanks, Dave
[sqlite] DROP INDEX not freeing up memory
Hello, I am running an in-memory db and am using the functionality where we override all the OS calls such that we implement our own memory management and file IO. Therefore, I am able to easily track the amount of memory Sqlite is taking up. I have noticed that when I call CREATE INDEX and then the corresponding DROP INDEX, I do not see the memory drop to the same level as it was before the CREATE INDEX. I also call VACUUM after DROP INDEX, which makes no difference. Is this expected, or does it sound like a bug in Sqlite? Thanks, Dave
RE: [sqlite] Is there a way to turn off -journal files?
Yes, that's exactly what we do: use the in-memory db until it comes time to save, and then we open a file db, attach, and copy it to disk. At that time, the journal files are created. If there is a nice way to serialize an in-memory db to disk without having to open/attach to a file db, I'm all ears. Anyone know if this is possible? Thanks, Dave -Original Message- From: Will Leshner [mailto:[EMAIL PROTECTED] Sent: Monday, January 15, 2007 12:42 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there a way to turn off -journal files? On 1/15/07, Dave Gierok <[EMAIL PROTECTED]> wrote: > I'm building the Sqlite lib using the preprocessor definition TEMP_STORE=3, > but I still notice that temporary '-journal' files are being created/deleted > when running queries that will modify the DB. I thought that the > TEMP_STORE=3 was supposed to disable these temporary files that get created. -journal files are an integral part of SQLite's ACID-ity, and as such, they can't be turned off or otherwise avoided. > How can I build/use Sqlite such that these temporary files do not get > created? I am running on Xbox360 and perf/storage are critical because the > DB can be stored to a memory card (not much storage). I wonder if this might work: keep the database on disk, but when using it, always use it from an in-memory version. You could open an in-memory database, attach the on-disk version, and then copy all the data from the on-disk version to the in-memory version. Hmm. Unfortunately, I'm not sure how to copy the data back out to the on-disk version without the -journal file getting created, so maybe this isn't such a great idea after all. If it were possible to serialize the in-memory database as save it as a raw binary stream, then it might work. But I don't think there is a way to do that. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is there a way to turn off -journal files?
I'm building the Sqlite lib using the preprocessor definition TEMP_STORE=3, but I still notice that temporary '-journal' files are being created/deleted when running queries that will modify the DB. I thought that the TEMP_STORE=3 was supposed to disable these temporary files that get created. How can I build/use Sqlite such that these temporary files do not get created? I am running on Xbox360 and perf/storage are critical because the DB can be stored to a memory card (not much storage). Thanks, Dave Gierok
RE: [sqlite] Sqlite DB file sizes
How difficult would it be to change my version of Sqlite such that it uses 32-bit floats instead of doubles for REAL values in :memory: databases (or even all databases)? Could you point me in the right direction? We are in a real shortage for RAM in our game. I appologize if this is the second time this was sent out. It appears as though the last time I sent this it did not make it out -- at least I never received the mail. Thank you very much, Dave Gierok -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 5:12 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Sqlite DB file sizes Dave Gierok <[EMAIL PROTECTED]> wrote: > So, how is data represented in a :memory: db? Are :memory: db's different = > in the way they store their data in memory vs. the way a file db stores its= > data to file? Or is it as simple as instead of writing to file, it writes= > to memory? If they are the same, are :memory: db's still cached? > A :memory: db is just the cache with the write-to-disk function disabled. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Sqlite DB file sizes
How difficult would it be to change my version of Sqlite such that it uses 32-bit floats instead of doubles for REAL values in memory? Could you point me in the right direction? We are in a real shortage for RAM in our game. Thank you very much, Dave Gierok -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 5:12 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Sqlite DB file sizes Dave Gierok <[EMAIL PROTECTED]> wrote: > So, how is data represented in a :memory: db? Are :memory: db's different = > in the way they store their data in memory vs. the way a file db stores its= > data to file? Or is it as simple as instead of writing to file, it writes= > to memory? If they are the same, are :memory: db's still cached? > A :memory: db is just the cache with the write-to-disk function disabled. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Sqlite DB file sizes
So, how is data represented in a :memory: db? Are :memory: db's different in the way they store their data in memory vs. the way a file db stores its data to file? Or is it as simple as instead of writing to file, it writes to memory? If they are the same, are :memory: db's still cached? Thanks, Dave Gierok -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 4:27 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Sqlite DB file sizes Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > Dave Gierok <[EMAIL PROTECTED]> wrote: > > > >> It looks like the size of a Sqlite DB ends up being much larger > >> (more than 2x) than size that I calculate for its data set. > >> > >> A simple test shows that when creating one table with one integer > >> column and filling it with 1 rows, I get a DB size of 92KB > >> instead of what I'd expect to be around 40KB plus some small > >> overhead for the table definition. This seems to scale linearly > >> as I increase the amount of data in the DB. > >> > > > > SQLite stores 64-bit integers, not 32-bit as you suppose. And > > each row also stores a 64-bit integer rowid in addition to the > > data. So that it fits in 92KB instead of the (naively expected) > > 160KB suggests that SQLite is actually doing a reasonable job of > > compressing the data. > > > I hate to disagree with the author, but that description is not quite > accurate. :-) > > SQLite uses variable length integer storage... No. I'm going to stand by what I said. SQLite works with 64-bit integer values. When writing those values to the disk, various compression techniques are used to avoid having to take up 8 bytes of disk space in the common case where most of those bytes are going to be zero. Various encodings are used. All of them are Huffman codes over a fixed probability distribution. Dennis calls these "variable length integers". I call them integers that are compressed using a Huffman code. That's the same thing in practice. But the nomenclature is important because I can point to Huffman's PhD thesis in 1952 to prove that the on-disk representation of integers in SQLite is not patentable. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Sqlite DB file sizes
Thanks for the quick answer. I'm working on a game where memory is a precious commodity, and we use a :memory: db. Is there a relatively easy way to do any of: - Make Sqlite use 32-bit floats instead of 64-bit doubles. Our DB contains a lot of floating point data. - Make Sqlite use 32-bit ints instead of 64-bit ints. - Anything else that would reduce the size in memory? I previously asked about setting the cache size to do this but it appears that doesn't do anything for :memory: databases (which makes sense). Thanks, Dave Gierok -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:38 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Sqlite DB file sizes Dave Gierok <[EMAIL PROTECTED]> wrote: > It looks like the size of a Sqlite DB ends up being much larger > (more than 2x) than size that I calculate for its data set. > > A simple test shows that when creating one table with one integer > column and filling it with 1 rows, I get a DB size of 92KB > instead of what I'd expect to be around 40KB plus some small > overhead for the table definition. This seems to scale linearly > as I increase the amount of data in the DB. SQLite stores 64-bit integers, not 32-bit as you suppose. And each row also stores a 64-bit integer rowid in addition to the data. So that it fits in 92KB instead of the (naively expected) 160KB suggests that SQLite is actually doing a reasonable job of compressing the data. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite DB file sizes
It looks like the size of a Sqlite DB ends up being much larger (more than 2x) than size that I calculate for its data set. A simple test shows that when creating one table with one integer column and filling it with 1 rows, I get a DB size of 92KB instead of what I'd expect to be around 40KB plus some small overhead for the table definition. This seems to scale linearly as I increase the amount of data in the DB. Does anyone know why this is? Does each column of each row store its type, or is it just stored in the table definition? I seem to remember reading that Sqlite supports flexible types where different rows can change their types. If that's true, is there a way to turn this off at compile time or something? Or are there any other tricks to reduce the data size of the DB? Thanks, Dave Gierok
[sqlite] :memory: db and cache size
Does anyone know if :memory: databases still use a cache? We are using Sqlite in a game and are very tight on memory. I'm wondering if it would be good for us to reduce the cache size to 0 (or something very small), and if there would be any performance penalty for doing so. It seems like there shouldn't be because the DB is already in RAM - it seems like the cache would only add extra memory copies and slow things down. But that is my naïve assumption. Anyone know? Thanks, Dave
RE: [sqlite] Memory DB: Load from file
Thank you for the help Andrew and D. Richard Hipp. But how would I do
this (page 24 & 25 of the slides) using the C/C++ interface?
{
sqlite3* pFileDB;
sqlite3* pMemoryDB;
sqlite3_open(fileName, &pFileDB);
sqlite3_open(":memory:", &pMemoryDB);
//
//WHAT DO I DO NOW?
//****
}
Thanks,
Dave Gierok
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, June 05, 2006 12:49 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory DB: Load from file
Andrew Piskorski <[EMAIL PROTECTED]> wrote:
> On Mon, Jun 05, 2006 at 12:05:52PM -0700, Dave Gierok wrote:
>
> > Is there a way to load a Sqlite file based DB and then specify we
want
> > that to go into memory? Or is there a more efficient way to create
our
> > memory DB?
>
> You could use attach to copy from an on-disk SQLite database:
>
> http://www.sqlite.org/google-talk-slides/page-024.html
> http://www.sqlite.org/google-talk-slides/page-025.html
>
Andrews suggestion above is better than the sqlite3_raw_write()
routine of my previous post because Andrew's idea is portable.
Use this idea if it is fast enough for you. The raw_write()
thing will be a little bit faster, but as I said, it is subject
to break without warning.
--
D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Memory DB: Load from file
I am developing a game on Xbox360 using Sqlite for a lot of our data manipulation. We use the ':memory:' functionality to create our DB in RAM, because we need our DB manipulations to be very fast - we can't afford for our queries to cause seeks/loads on the DVD. This works very well for us with one exception: It takes a long time to load the DB. Since we need the DB to be in memory, we create an empty ':memory:' DB, then load up a file which contains all the SQL (CREATE TABLE, INSERT INTO) we need to create our tables and execute them on the memory-DB. This process currently takes 10 seconds (all running the SQL statements to create the tables), which is not acceptable for our game. Is there a way to load a Sqlite file based DB and then specify we want that to go into memory? Or is there a more efficient way to create our memory DB? Thank you very much for your help, Dave Gierok
