Re: [sqlite] Loading a existing database 100% into memory
Ok I've moved the test database into the same directory as my C++ test application (I'm on windows) to remove the directory reference, my queries now look like: "ATTACH DATABASE 'Test01.db' AS Test01" - returns SQLITE_OK "SELECT tbl_name FROM Test01.sqlite_master WHERE type = 'table'" - returns SQLITE_ERROR I'm still getting the same error message as before 'no such table Test01.sqlite_master', I've opened the test database in SQLite Analyzer and it seems valid. If I take the ' away in the first query I get SQLITE_ERROR with the message 'No such column: Test01.db' if that helps? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Thursday, August 14, 2008 4:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory Sorry I should have said, I'm using these calls in C++ on windows. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, August 14, 2008 1:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >> "SQL error: no such table: 'test.sqlite_master'; >> >> I am running the following queries: >> 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed >> (no >> error code). >> 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - >> this one is failing. >> > > Works when I try it. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not > quote backslashes in SQL strings as you do in C. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not Backslashes are illegal in a Windows filenames, except as folder delimiters, so "data\\test.db" is not a valid Windows filename. Hence, if this is on Windows, the ATTACH must be failing. It must be 'data\test.db'. Chris ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
Sorry I should have said, I'm using these calls in C++ on windows. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, August 14, 2008 1:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >> "SQL error: no such table: 'test.sqlite_master'; >> >> I am running the following queries: >> 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed >> (no >> error code). >> 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - >> this one is failing. >> > > Works when I try it. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not > quote backslashes in SQL strings as you do in C. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not Backslashes are illegal in a Windows filenames, except as folder delimiters, so "data\\test.db" is not a valid Windows filename. Hence, if this is on Windows, the ATTACH must be failing. It must be 'data\test.db'. Chris ___ 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] Loading a existing database 100% into memory
On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >> "SQL error: no such table: 'test.sqlite_master'; >> >> I am running the following queries: >> 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed >> (no >> error code). >> 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - >> this one is failing. >> > > Works when I try it. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not > quote backslashes in SQL strings as you do in C. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not Backslashes are illegal in a Windows filenames, except as folder delimiters, so "data\\test.db" is not a valid Windows filename. Hence, if this is on Windows, the ATTACH must be failing. It must be 'data\test.db'. Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > Hello Stefan, > > I'm trying to use the code snippet you suggested but when I try to > query > the master table of the attached database I get and error with the > following message: > "SQL error: no such table: 'test.sqlite_master'; > > I am running the following queries: > 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed > (no > error code). > 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - > this one is failing. > Works when I try it. But I'm not running on windows. Do you really need two backslashes in the filename? Should there be just a single backslash? You do not quote backslashes in SQL strings as you do in C. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
Hello Stefan, I'm trying to use the code snippet you suggested but when I try to query the master table of the attached database I get and error with the following message: "SQL error: no such table: 'test.sqlite_master'; I am running the following queries: 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed (no error code). 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - this one is failing. Do you have any ideas? I tried looking at the docs but there doesn't seem to be much about it, from what I've read the queries appear to be correct. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Szomraky, Stefan Sent: Thursday, August 07, 2008 12:02 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory What do you mean by loading it into memory? If you want to dump the on-disk tables into memory and also want to check the memory footprint used for caching try this: Open the :memory: database and attach the on-disk database with ATTACH filename.db AS filename 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.) 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'". Best regards, Stefan. ___ 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] Loading a existing database 100% into memory
What I am wanting to work out is how much memory SQLite uses if we have
the entire database in RAM only, the reason I'd like to find this out is
that there is some concern here about SQLite paging to disc at
inopportune moments (we're on an embedded system with a lot of other
random disc access going on) so ideally I'd like the option to be able
to run completely from RAM after the initial load from disc, so that if
paging proves too costly we can sacrifice RAM to escape the paging
issue.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephen
Oberholtzer
Sent: Wednesday, August 06, 2008 5:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Loading a existing database 100% into memory
On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
> Good afternoon list,
>
> I would like to load my current database file completely into memory,
> mostly as an experiment to check SQLite's maximum memory footprint,
> however searching through the documentation I can only find references
> about how to create new databases that are completely memory resident.
> Is there a way to do this? I'm currently using the SQlite console
> application for my testing if that makes a difference.
What, exactly, is it you're after? I can load a SQLite database 100%
into
memory quite quickly:
int fd = open("sqlitedb.dat");
struct stat info;
fstat(fd, &info);
char *buf = malloc(info.st_size);
read(fd, buf, info.st_size);
I find it extremely unlikely that this is what you want (although it
might
be an interesting academic exercise to make a VFS port of SQLite that
uses
memory arrays for read/write ops.)
At the other end of the spectrum, you could just dump the entire
database on
disk and then insert all the data into a :memory: database. However,
this
doesn't seem like it would be very useful, either.
This sounds like an XY problem. What are you really trying to
accomplish?
What constraints are preventing you from simply using an on-disk
database?
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
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] Loading a existing database 100% into memory
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel > Sent: Thursday, August 07, 2008 12:27 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Loading a existing database 100% into memory > > Good afternoon list, > > I would like to load my current database file completely into > memory, mostly as an experiment to check SQLite's maximum > memory footprint, however searching through the documentation > I can only find references about how to create new databases > that are completely memory resident. > Is there a way to do this? I'm currently using the SQlite > console application for my testing if that makes a difference. > What do you mean by loading it into memory? If you want to dump the on-disk tables into memory and also want to check the memory footprint used for caching try this: Open the :memory: database and attach the on-disk database with ATTACH filename.db AS filename 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.) 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'". Best regards, Stefan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
Hi Stephen Woodbridge,
> (although it might
> > be an interesting academic exercise to make a VFS port of SQLite
> that uses
> > memory arrays for read/write ops.)
Do u have any ref impl of this kind?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Stephen Woodbridge <[EMAIL PROTECTED]>
Date: Thursday, August 7, 2008 7:24 am
Subject: Re: [sqlite] Loading a existing database 100% into memory
> Stephen Oberholtzer wrote:
> > On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]>
> wrote:>
> >> Good afternoon list,
> >>
> >> I would like to load my current database file completely into
> memory,>> mostly as an experiment to check SQLite's maximum memory
> footprint,>> however searching through the documentation I can
> only find references
> >> about how to create new databases that are completely memory
> resident.>> Is there a way to do this? I'm currently using the
> SQlite console
> >> application for my testing if that makes a difference.
> >
> >
> > What, exactly, is it you're after? I can load a SQLite database
> 100% into
> > memory quite quickly:
> >
> > int fd = open("sqlitedb.dat");
> > struct stat info;
> > fstat(fd, &info);
> > char *buf = malloc(info.st_size);
> > read(fd, buf, info.st_size);
> >
> > I find it extremely unlikely that this is what you want
> (although it might
> > be an interesting academic exercise to make a VFS port of SQLite
> that uses
> > memory arrays for read/write ops.)
> >
> > At the other end of the spectrum, you could just dump the entire
> database on
> > disk and then insert all the data into a :memory: database.
> However, this
> > doesn't seem like it would be very useful, either.
> >
> > This sounds like an XY problem. What are you really trying to
> accomplish?> What constraints are preventing you from simply using
> an on-disk database?
> >
>
> Another interesting option might be to mmap the DB file so you use
> the
> OS virtual memory paging to map the file to memory as you need
> access to
> it. But this probably has the downside that writes are not sync'd
> to
> disk so in the event of a crash you out of luck, but that is the
> case
> with any memory DB. The upside is that when you shutdown your DB
> is
> sync'd to disk and the OS paging is pretty efficient.
>
> -Steve W
> ___
> 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] Loading a existing database 100% into memory
On Wed, Aug 06, 2008 at 03:26:30PM -0700, Brown, Daniel scratched on the wall: > Good afternoon list, > > I would like to load my current database file completely into memory, > mostly as an experiment to check SQLite's maximum memory footprint, > however searching through the documentation I can only find references > about how to create new databases that are completely memory resident. > Is there a way to do this? I'm currently using the SQlite console > application for my testing if that makes a difference. If the database is fairly static in size, just figure out how many pages the database file takes up (default page size is 1K). Make the page cache a bit larger than this via PRAGMA commands. As you use the database, pages will be loaded into memory and generally stay there. Adjust the write policy (sync, etc.) as desired, if you're looking for performance comparisons. Pages in the cache have an overhead of roughly 0.5K per page. That should give you some idea of your memory footprint. See the website for more details. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
Stephen Oberholtzer wrote:
> On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
>
>> Good afternoon list,
>>
>> I would like to load my current database file completely into memory,
>> mostly as an experiment to check SQLite's maximum memory footprint,
>> however searching through the documentation I can only find references
>> about how to create new databases that are completely memory resident.
>> Is there a way to do this? I'm currently using the SQlite console
>> application for my testing if that makes a difference.
>
>
> What, exactly, is it you're after? I can load a SQLite database 100% into
> memory quite quickly:
>
> int fd = open("sqlitedb.dat");
> struct stat info;
> fstat(fd, &info);
> char *buf = malloc(info.st_size);
> read(fd, buf, info.st_size);
>
> I find it extremely unlikely that this is what you want (although it might
> be an interesting academic exercise to make a VFS port of SQLite that uses
> memory arrays for read/write ops.)
>
> At the other end of the spectrum, you could just dump the entire database on
> disk and then insert all the data into a :memory: database. However, this
> doesn't seem like it would be very useful, either.
>
> This sounds like an XY problem. What are you really trying to accomplish?
> What constraints are preventing you from simply using an on-disk database?
>
Another interesting option might be to mmap the DB file so you use the
OS virtual memory paging to map the file to memory as you need access to
it. But this probably has the downside that writes are not sync'd to
disk so in the event of a crash you out of luck, but that is the case
with any memory DB. The upside is that when you shutdown your DB is
sync'd to disk and the OS paging is pretty efficient.
-Steve W
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:
> Good afternoon list,
>
> I would like to load my current database file completely into memory,
> mostly as an experiment to check SQLite's maximum memory footprint,
> however searching through the documentation I can only find references
> about how to create new databases that are completely memory resident.
> Is there a way to do this? I'm currently using the SQlite console
> application for my testing if that makes a difference.
What, exactly, is it you're after? I can load a SQLite database 100% into
memory quite quickly:
int fd = open("sqlitedb.dat");
struct stat info;
fstat(fd, &info);
char *buf = malloc(info.st_size);
read(fd, buf, info.st_size);
I find it extremely unlikely that this is what you want (although it might
be an interesting academic exercise to make a VFS port of SQLite that uses
memory arrays for read/write ops.)
At the other end of the spectrum, you could just dump the entire database on
disk and then insert all the data into a :memory: database. However, this
doesn't seem like it would be very useful, either.
This sounds like an XY problem. What are you really trying to accomplish?
What constraints are preventing you from simply using an on-disk database?
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Loading a existing database 100% into memory
Good afternoon list, I would like to load my current database file completely into memory, mostly as an experiment to check SQLite's maximum memory footprint, however searching through the documentation I can only find references about how to create new databases that are completely memory resident. Is there a way to do this? I'm currently using the SQlite console application for my testing if that makes a difference. Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
