Re: [sqlite] DBD::SQLite
just a $dbh->do("insert into mytable(varint) values ('01234')";
It's not working correctly with SQLite but no problem with Oracle.
Chris Werner a écrit :
Can you give a code example? I have just tried, and can load string values
with a leading 0 and m/^\d+$/
I suspect the problem is in your treatment of perl...
Christian Werner
-Original Message-
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 16, 2006 2:56 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] DBD::SQLite
Hi,
I use DBD::SQLite for accessing a SQLite database, but there's an issue
when I tyr to insert a number starting with a 0. In fact, DBD::SQLite
seems to trim the starting 0 of the number. So, when I insert 0234 in a
table I find 234 instead.
Anyone has encoutered and resolved this bug ?
Re: [sqlite] sqlite performance with sizeable tables
Sorry it took me some time to get back to this thread. - Original Message From: Christian Smith <[EMAIL PROTECTED]> > When your database does not fit in memory, yes, you're right, the OS may > well get caching wrong, and in the worst way possible. Two things though: > - SQLite does have known scaling issues when using multi-gigabyte > databases. > - Memory is cheap. If it don't fit, spend that few hundred dollars a few > days of your time is worth and buy another few gigs or RAM. The machine has 2 GB of RAM and the table that seems to cause the problems is less than 700 megs (proven by the fact that if I precache this database, things get zippy). To me it seems like the problem is related to the way the reads are done, but I can be wrong: to me it seems that caches never really get "hot" (and with nothing else running on the machine, the OS is pretty much caching all reads done by sqlite). > > >Right now, sqlite shows performance that is on par with a simple > >filesystem structure (except it's much faster to backup because > >traversing a multimilion file structure takes several hours). I was > >expecting a few things by moving to sqlite: > >* getting a better ramp up (cold cache) performance than a dump > > filesystem structure. > Nothing will speed up the physical disks. I agree that disk i/o is the bottleneck, but what can be worked around is the latencies needed to seek in the file and the way operations are send out to the disk to help the OS cache more useful information and/or have a better caching mechanism. > >* having a [much] higher throughput (so that I can combine multiple > > servers into one), as the records are small and there is no > > fragmentation of the folders here. > >* having consistant latencies (filesystems tend to vary a lot). > > > >> - Have you tested other databases? What sort of performance did you get > > from those? > >I am in the process of setting up mysql with our app, I will keep you > >posted on the result. > > Prepare to be disappointed, IMO. The most important thing when dealing > with big fast databases is disk IO, and anything (MySQL, PGSQL, Oracle, > SQLite) will find disks a bottleneck. The reason it took me a while to reply to this thread was that I setup a MySQL server (5.0.19, run on the same machine) and adapted my application to run with it. The results on the exact same operations that I did before: * importing of the data is faster with MySQL, and performance doesn't degrade exponentially with the number or rows (insertions at the beginning and at the end of the import operation are of similar speed). sqlite and MySQL started at around the same speed, but after a few million inserts, sqlite becomes slow. * cached cleared, on my typical run test (read&write combination), MySQL ran 2 minutes faster than sqlite (6 minutes vs 8 minutes), getting nice latencies after about 3 minutes (and that's where the difference is, I think). I think that after 3 minutes, MySQL manage to have most critical data cached in RAM. * with precached dbs (file sizes are very similar for sqlite and MySQL, for the difference that MySQL separates the data and index into 2 files), MySQL is faster too (1m30 vs 2 minutes). * latencies stay low (as in, rarely blocks for > 4 seconds, sqlite would block for > 10 seconds especially for the early queries when there is no cache). Maybe the reason MySQL performs better is simply because they use 2 files instead of 1. It does make sense: if the index/offsets are all together on disk, then the OS can very efficiently cache this information for us. Was splitting the file into 2 ever tried for sqlite? I mean, there is already the -journal file, so why not having a -index file as well? > > To accomodate a lot of users, I have one main DB that holds all users -> > > unique ID The other dbs are a partitioned db really, so that later > > threads conflict only when accessing users within the same range and the > > dbs don't get out of proportion (because blobs are used I thought a lot > > of records could become a performance bottleneck). If I precache the big > > table name -> uid, the import of 650,000 records takes about 2 minutes. > >If I don't precache it, it takes about 8 minutes. I start/stop > >transactions every X seconds (right now, I have it set at 10 seconds). > > Are these name->uid lookups used in a read-only fashion after import? If > so, then multiple threads can indeed read the table in parallel, and you > should have good performance. A lot of the time this table is accessed read only, yes. I tried to have several threads access the table at the same time before, and the performance was a bit lower (maybe because transactions occur at the same time, and block the readers?), but also, and this was why I rolled back this change, the busy handler that does a simple "usleep", causes starvation and thus potential high latencies. > > For now, I'd say: > - If the import/update case is
Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Doug Nebeker wrote: It sounds to me that he isn't saying it is leaking--it simply isn't releasing memory after a SELECT statement finishes (is finalized). This might be by design. I would expect the data to be released if the database connection is closed, but not necessarily after each SELECT. It would be nice to have an API to force any cached pages to be released (maybe it already exists and I don't know about it?). -Original Message- From: Robert Simpson [mailto:[EMAIL PROTECTED] Sent: Thursday, March 16, 2006 12:13 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE - Original Message - From: <[EMAIL PROTECTED]> I have run your program on the CE emulator (Pocket PC 2003) and i got the same memory leak. I have inserted 2 buttons on a MFC dialog application. The first button executes your code and the second button closes the application. If you examine the memory you will discover that the program only free the memory once you exit from the apllication, meanwhile it reserves memory as its needed (on demand, but see details below). the memory behaviour of SQLite is quite strange, an example: lets say that a select sentence reserves 1000kb of memory, once this local process has finished memory keeps reserved for the program (it should be freed), if another process executes a select sentence that needs 200kb SQLite will not reserve 200k more, it will use 200k of the previous 1000k reserved. if a 3rd process executes a select sentence that needs 1300k SQlite will reserve 300kb more and those 1300kb will not be freed until the main dialog application closes (even if the 3 process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. The problem is that if a select sentence consume most of the memory it will not be freed and the program will execute very slow until you exit from the application because there will be so little memory left for other not SQLite process that the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing "PRAGMA cache_size=8" or some really low number and tell me if its still "leaking". Robert To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd. He uses the table API call to get the output. By necessity this must allocate dynamic memory to accommodate the result set. We can guess that he then runs through the table and looks at the result rows. If he were to use an sqlite3_step and process each row as it is retrieved that dynamic memory requirement would be eliminated. Not only would memory usage be better controlled, but the application would run faster by eliminating the delay while te table is created. Some thought on how malloc and free work would help in conceptualising the application. The best that can usually be achieved is that over a period of time the application hits a memory "high water mark" and does not go beyond that. Minimizing dynamic memory allocation limits that high water level. Free cannot be guaranteed to ensure that malloc'd memory is totally reused. That requires garbage collection.
Re: [sqlite] Re: concers about database size
Jay Sprenkle wrote: On 3/16/06, Daniel Franke <[EMAIL PROTECTED]> wrote: The original idea was to get rid of thousands of files to store their data in one single container. Those (ASCII) files add up to approx 5GB ... If so, are you trying to use a blender to stir the ocean? You might reevaluate if you're using the right tool for the job. That's my question: IS sqlite the right tool here? =) Then I guess the right question is what are your goals? To make maintenance easier? Why were the thousands of files a problem? It looks as if this is a problem of storing large amounts of simple data. Is an SQL RDBMS the best way to do that? Would a simple, compact index file be more appropriate? The key to selecting the optimal solution is to know how the information is to be retrieved. Are the data archived? That could affect whether using rotating databases would be efficient.
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi,
Thank you very much for your help, i have modify the line, it compiles, it
doesn´t fire any exception, but memory is not freed at all (works the same way)
Anyone has another idea?, i tought that SQLite was working since a long time
ago on Windows CE devices, it seems quite strange that no one detected this
memory problem before?.
Thank you,
Eduardo
---Mensaje original---On March 16, 2006 12:28 pm, Jose Da Silva wrote:
I forgot a line:
char x[12] = "Hello world\0";
char *ptr;
ptr = x;
while (*ptr) {printf("%c",*ptr); ptr;};
> If you figure out the right sizeof(???) value to use, then I think it
> could be submitted as a bug-fix for \src\table.c
You want to move the pointer from azResult[1] to azResult[0] but you
want to say it without doing it like this "azResult--;" on line 192,
but saying it like this looks sort of ugly although it might work:
azResult -= ( &azResult[1] - &azResult[0] );
..so if someone has a simpler solution, thanks :-)
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
Re: [sqlite] Re: concers about database size
Daniel > The most common scenarios: > - get a single marker from subset of individuals > - get a subset of markers from a single individual > - get a subset of markers from a subset of individuals Sounds like this might define your database. Each individual has 500,000 markers, but maybe they are able to be grouped into 500 groupings of 1000 markers each. I'm kindof guessing what your application does, but I'm guessing the genes that define eye-colour might be in a different group to the genes that define liver placement. That way you could have 500 tables (in the one database file). You still need roughly the same amount of disk space (because quite simply that's how much data you have), but you may not need to be searching all of the data all of the time. Each table would contain the same subset of information for all individuals. Regards, Carl.
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert, I was talking about 3 selects satements using the same connections. Anyway, thank you very much for your advice of using "PRAGMA cache size=8", that solved all the problems related to sqlite3_exec memory problems with a select statement, baut the memory problems are not solved at all, stilll i get a memory leak when using sqlite3_get_table, even if i use sqlite3_free_table not all the memory is freed. Someone pointed that this might be an error on the source code becuse the ARM processor architecture is quite different from the X86 processor. I have been takin a look at the table.c file but i am not that good programer, to see if anything fails, Do you see anything on the table.c that might be generating the problem?. Thank you very much, Eduardo ---Mensaje original Original Message - From: > I have run your program on the CE emulator (Pocket PC 2003) > and i got the same memory leak. > I have inserted 2 buttons on a MFC dialog application. > The first button executes your code and the second button > closes the application. > If you examine the memory you will discover that the program > only free the memory once you exit from the apllication, > meanwhile it reserves memory as its needed (on demand, but > see details below). > the memory behaviour of SQLite is quite strange, an example: > lets say that a select sentence reserves 1000kb of memory, > once this local process has finished memory keeps reserved > for the program (it should be freed), if another process > executes a select sentence that needs 200kb SQLite will not > reserve 200k more, it will use 200k of the previous 1000k > reserved. if a 3rd process executes a select sentence that > needs 1300k SQlite will reserve 300kb more and those 1300kb > will not be freed until the main dialog application closes > (even if the 3 process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. > The problem is that if a select sentence consume most of the > memory it will not be freed and the program will execute very > slow until you exit from the application because there will > be so little memory left for other not SQLite process that > the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing "PRAGMA cache_size=8" or some really low number and tell me if its still "leaking". Robert El día del padre está cerca... ¿Ya tienes el regalo? http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Robert, I was talking about 3 selects satements using the same connections. Anyway, thank you very much for your advice of using "PRAGMA cache size=8", that solved all the problems related to sqlite3_exec memory problems with a select statement, baut the memory problems are not solved at all, stilll i get a memory leak when using sqlite3_get_table, even if i use sqlite3_free_table not all the memory is freed. Someone pointed that this might be an error on the source code becuse the ARM processor architecture is quite different from the X86 processor. I have been takin a look at the table.c file but i am not that good programer, to see if anything fails, Do you see anything on the table.c that might be generating the problem?. Thank you very much, Eduardo ---Mensaje original Original Message - From: > I have run your program on the CE emulator (Pocket PC 2003) > and i got the same memory leak. > I have inserted 2 buttons on a MFC dialog application. > The first button executes your code and the second button > closes the application. > If you examine the memory you will discover that the program > only free the memory once you exit from the apllication, > meanwhile it reserves memory as its needed (on demand, but > see details below). > the memory behaviour of SQLite is quite strange, an example: > lets say that a select sentence reserves 1000kb of memory, > once this local process has finished memory keeps reserved > for the program (it should be freed), if another process > executes a select sentence that needs 200kb SQLite will not > reserve 200k more, it will use 200k of the previous 1000k > reserved. if a 3rd process executes a select sentence that > needs 1300k SQlite will reserve 300kb more and those 1300kb > will not be freed until the main dialog application closes > (even if the 3 process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. > The problem is that if a select sentence consume most of the > memory it will not be freed and the program will execute very > slow until you exit from the application because there will > be so little memory left for other not SQLite process that > the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing "PRAGMA cache_size=8" or some really low number and tell me if its still "leaking". Robert El día del padre está cerca... ¿Ya tienes el regalo? http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
[sqlite] RE:Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi,
I guess that you are right. Robert gave me the hint to change chae size using
the PRAGMA command, and that did solve all the problems i hadt with the
sqlite3_exec command, but it did not solved the problems related to
sqlite3_get_table.
I have been taking a look at table.c code and i am not a great programmer too
and i reviwed the code buti do not see anything strange that might be
generating this problem, i hope someone with more experience could solve it?.
Thank you,
Eduardo
---Mensaje original---On March 16, 2006 08:49 am, [EMAIL PROTECTED] wrote:
> Hi Again Robert,
> I have run your program on the CE emulator (Pocket PC 2003) and i got
> the same memory leak. I have inserted 2 buttons on a MFC dialog
> application.
> The first button executes your code and the second button closes the
> application. If you examine the memory you will discover that the
> program only free the memory once you exit from the apllication,
> meanwhile it reserves memory as its needed (on demand, but see
> details below). the memory behaviour of SQLite is quite strange, an
> example: lets say that a select sentence reserves 1000kb of memory,
> once this local process has finished memory keeps reserved for the
> program (it should be freed), if another process executes a select
> sentence that needs 200kb SQLite will not reserve 200k more, it will
> use 200k of the previous 1000k reserved. if a 3rd process executes a
> select sentence that needs 1300k SQlite will reserve 300kb more and
> those 1300kb will not be freed until the main dialog application
> closes (even if the 3 process where local methods or functions). The
> problem is that if a select sentence consume most of the memory it
> will not be freed and the program will execute very slow until you
> exit from the application because there will be so little memory left
> for other not SQLite process that the program might be unusable.
>
> Any ideas,
Hi again, I think the problem lies with the type of processor being used
and the way that sqlite (or other programs for that matter) are
written. I think your problem is also related to another thread:
"[sqlite] SQLITE_CORRUPT problem in Mac OS X"
The reason I suggest that is because the Mac uses a power PC I believe,
and in your case it uses an ARM processor.
The Intel, x86, and several processors tend to be able to pack bytes,
words, longs, etc, next to each other. The ARM, the power PC, Sun, and
other risc processors tend to align them on boundaries.
So while, it is easy to point at a string and then use a basic increment
function to look up-n-down a string on an x86, it tends to cause
problems with processors that pack things differently. example:
Please realize I did not test this code, so it may not run as typed.
char x[12] = "Hello world\0";
char *ptr;
while (*ptr) {printf("%c",*ptr); ptr;};
The code above should work okay on an x86 or other CISC processor, but
may have problems on some RISC processors due to alignment issues,
especially considering the fact the string was defined using [] but it
is getting accessed using a *ptr and being incremented usingor --.
With a RISC processor, you need to take alignment into consideration,
and therefore the distance may not necessarily beor --, but some
other distance.
When I looked at src\table.c I noticed that malloc uses a formula like
(sizeof(char*)*stringsize 1) which is why I suggested trying to modify
line 192 using "result -=(char*);" instead of "result--;"
I hope this makes sense, so although I guessed that -=sizeof(char*);
perhaps it should be another value inserted there, but I'm not that
great a programmer to have guessed the correct value;
Going back to table.c you have this:
--
void sqlite3_free_table(char **azResult){
if( azResult ){ <--this is pointing to azResult[1] if you look at
the previous "sqlite3_get_table()" routines.
int i, n;
azResult--; <--I think this is wrong for ARM processor.
and should be a distance of azResult[1] to azResult[0] which I think is
another value than --
if( azResult==0 ) return; <--probably pointing to wrong spot now!
n = (int)azResult[0];
for(i=1; i(the line above is accessing info correctly for an ARM but can be
coded
quicker using *azResult if an x86 type processor was used, but
unfortunately wouldn't work then)
free(azResult);
}
}
--
I hope the above makes sense, so although I though it was
-=sizeof(char*);
It probably has to be another value, hopefully someone else know.
If you figure out the right sizeof(???) value to use, then I think it
could be submitted as a bug-fix for \src\table.c
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
On March 16, 2006 12:28 pm, Jose Da Silva wrote:
I forgot a line:
char x[12] = "Hello world\0";
char *ptr;
ptr = x;
while (*ptr) {printf("%c",*ptr); ++ptr;};
> If you figure out the right sizeof(???) value to use, then I think it
> could be submitted as a bug-fix for \src\table.c
You want to move the pointer from azResult[1] to azResult[0] but you
want to say it without doing it like this "azResult--;" on line 192,
but saying it like this looks sort of ugly although it might work:
azResult -= ( &azResult[1] - &azResult[0] );
...so if someone has a simpler solution, thanks :-)
RE: [sqlite] DBD::SQLite
Can you give a code example? I have just tried, and can load string values with a leading 0 and m/^\d+$/ I suspect the problem is in your treatment of perl... Christian Werner -Original Message- From: Cyril Scetbon [mailto:[EMAIL PROTECTED] Sent: Thursday, March 16, 2006 2:56 PM To: sqlite-users@sqlite.org Subject: [sqlite] DBD::SQLite Hi, I use DBD::SQLite for accessing a SQLite database, but there's an issue when I tyr to insert a number starting with a 0. In fact, DBD::SQLite seems to trim the starting 0 of the number. So, when I insert 0234 in a table I find 234 instead. Anyone has encoutered and resolved this bug ?
Re: [sqlite] Re: concers about database size
> > This may take a while, about 20 hours maybe. The partition has approx > > 10GB, I can't afford more. Let's hope that this is sufficient. > > Import data without indexes, then add then when the import is complete. > It's much faster. Thanks. I can not do this with the real data, unfortunatelly. The index has a uniqueness constraint. If there is a file that violates this restriction, I have to reject the file completely. Each file is translated into a set of statements like these: BEGIN transaction; INSERT OR ROLLBACK ... ... COMMIT; I still have to check the sqlite3_bind_* functions instead ...
[sqlite] DBD::SQLite
Hi, I use DBD::SQLite for accessing a SQLite database, but there's an issue when I tyr to insert a number starting with a 0. In fact, DBD::SQLite seems to trim the starting 0 of the number. So, when I insert 0234 in a table I find 234 instead. Anyone has encoutered and resolved this bug ?
Re: [sqlite] Re: concers about database size
On 3/16/06, Daniel Franke <[EMAIL PROTECTED]> wrote: > > > That would be an excellent question to add to the FAQ: > > "How do I estimate the resource requirements for a database?" > > I spent some time to create 3GB of sample data (just zeros, about half the > size of the actual data set I have to deal with). I'm currently importing it > into the database. As far as I already can tell: the main index will be > approx 4GB in size. AFAIK, 32bit systems never grant more than 2GB RAM per > process ... > > This may take a while, about 20 hours maybe. The partition has approx 10GB, I > can't afford more. Let's hope that this is sufficient. Import data without indexes, then add then when the import is complete. It's much faster. > > Since there's so much interest in this, I'll submit a couple of timings as > soon as possible =) Thanks!
Re: [sqlite] Re: concers about database size
> That would be an excellent question to add to the FAQ: > "How do I estimate the resource requirements for a database?" I spent some time to create 3GB of sample data (just zeros, about half the size of the actual data set I have to deal with). I'm currently importing it into the database. As far as I already can tell: the main index will be approx 4GB in size. AFAIK, 32bit systems never grant more than 2GB RAM per process ... This may take a while, about 20 hours maybe. The partition has approx 10GB, I can't afford more. Let's hope that this is sufficient. Since there's so much interest in this, I'll submit a couple of timings as soon as possible =) Regards Daniel
Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
On March 16, 2006 08:49 am, [EMAIL PROTECTED] wrote:
> Hi Again Robert,
> I have run your program on the CE emulator (Pocket PC 2003) and i got
> the same memory leak. I have inserted 2 buttons on a MFC dialog
> application.
> The first button executes your code and the second button closes the
> application. If you examine the memory you will discover that the
> program only free the memory once you exit from the apllication,
> meanwhile it reserves memory as its needed (on demand, but see
> details below). the memory behaviour of SQLite is quite strange, an
> example: lets say that a select sentence reserves 1000kb of memory,
> once this local process has finished memory keeps reserved for the
> program (it should be freed), if another process executes a select
> sentence that needs 200kb SQLite will not reserve 200k more, it will
> use 200k of the previous 1000k reserved. if a 3rd process executes a
> select sentence that needs 1300k SQlite will reserve 300kb more and
> those 1300kb will not be freed until the main dialog application
> closes (even if the 3 process where local methods or functions). The
> problem is that if a select sentence consume most of the memory it
> will not be freed and the program will execute very slow until you
> exit from the application because there will be so little memory left
> for other not SQLite process that the program might be unusable.
>
> Any ideas,
Hi again, I think the problem lies with the type of processor being used
and the way that sqlite (or other programs for that matter) are
written. I think your problem is also related to another thread:
"[sqlite] SQLITE_CORRUPT problem in Mac OS X"
The reason I suggest that is because the Mac uses a power PC I believe,
and in your case it uses an ARM processor.
The Intel, x86, and several processors tend to be able to pack bytes,
words, longs, etc, next to each other. The ARM, the power PC, Sun, and
other risc processors tend to align them on boundaries.
So while, it is easy to point at a string and then use a basic increment
function to look up-n-down a string on an x86, it tends to cause
problems with processors that pack things differently. example:
Please realize I did not test this code, so it may not run as typed.
char x[12] = "Hello world\0";
char *ptr;
while (*ptr) {printf("%c",*ptr); ++ptr;};
The code above should work okay on an x86 or other CISC processor, but
may have problems on some RISC processors due to alignment issues,
especially considering the fact the string was defined using [] but it
is getting accessed using a *ptr and being incremented using ++ or --.
With a RISC processor, you need to take alignment into consideration,
and therefore the distance may not necessarily be ++ or --, but some
other distance.
When I looked at src\table.c I noticed that malloc uses a formula like
(sizeof(char*)*stringsize+1) which is why I suggested trying to modify
line 192 using "result -=(char*);" instead of "result--;"
I hope this makes sense, so although I guessed that -=sizeof(char*);
perhaps it should be another value inserted there, but I'm not that
great a programmer to have guessed the correct value;
Going back to table.c you have this:
--
void sqlite3_free_table(char **azResult){
if( azResult ){ <--this is pointing to azResult[1] if you look at
the previous "sqlite3_get_table()" routines.
int i, n;
azResult--; <--I think this is wrong for ARM processor.
and should be a distance of azResult[1] to azResult[0] which I think is
another value than --
if( azResult==0 ) return; <--probably pointing to wrong spot now!
n = (int)azResult[0];
for(i=1; i
Re: [sqlite] Re: concers about database size
> Why would it require a lot of RAM? I ask not because I doubt you, but > because my intuition says that a BTree based database should scale > pretty well. While certainly it would run faster if you can fit the > whole thing in RAM, if the index can be made to fit in RAM it seems > like the data can be just about any size. I think you're probably right but I haven't done any research on it. I don't know if it needs to load the entire index into RAM. If it does, then one entry per unique key + some overhead for btree structure information will get fairly large. Since you'll want reasonable performance you'll probably want to put in as much RAM as you can reasonably afford. That would be an excellent question to add to the FAQ: "How do I estimate the resource requirements for a database?" > > > I've used much larger databases than this on all of the big > > commercial database engines (sqlserver, db2, informix, oracle). Any > > of them will certainly work for you. > > Again, my intuition would be tha for single-user usage patterns SQLite > should have less rather than more drop-off in performance than those. > Is there something about the way that SQLite handles large files that > would cause it to degrade faster than a commercial database? Are you > saying he could get by with less hardware using a heavier weight database? Sorry to mislead you there. I intended to say that if you decided against sqlite any of the other commercial offerings should work for you.
Re: [sqlite] Re: concers about database size
On Thu, Mar 16, 2006 at 11:34:31AM -0600, Jay Sprenkle wrote: > > > You may legitimately need one really large table but most applications > > > don't. > > Too bad. My guess is that you're doing the right thing trying to consolidate. > It's going to take expensive hardware no matter what you end up doing. > Your design is larger than any I've ever heard of using sqlite. My guess is > that it will work if you have enough RAM. Why would it require a lot of RAM? I ask not because I doubt you, but because my intuition says that a BTree based database should scale pretty well. While certainly it would run faster if you can fit the whole thing in RAM, if the index can be made to fit in RAM it seems like the data can be just about any size. > I've used much larger databases than this on all of the big > commercial database engines (sqlserver, db2, informix, oracle). Any > of them will certainly work for you. Again, my intuition would be tha for single-user usage patterns SQLite should have less rather than more drop-off in performance than those. Is there something about the way that SQLite handles large files that would cause it to degrade faster than a commercial database? Are you saying he could get by with less hardware using a heavier weight database? --nate
RE: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
It sounds to me that he isn't saying it is leaking--it simply isn't releasing memory after a SELECT statement finishes (is finalized). This might be by design. I would expect the data to be released if the database connection is closed, but not necessarily after each SELECT. It would be nice to have an API to force any cached pages to be released (maybe it already exists and I don't know about it?). -Original Message- From: Robert Simpson [mailto:[EMAIL PROTECTED] Sent: Thursday, March 16, 2006 12:13 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE - Original Message - From: <[EMAIL PROTECTED]> > I have run your program on the CE emulator (Pocket PC 2003) and i got > the same memory leak. > I have inserted 2 buttons on a MFC dialog application. > The first button executes your code and the second button closes the > application. > If you examine the memory you will discover that the program only free > the memory once you exit from the apllication, meanwhile it reserves > memory as its needed (on demand, but see details below). > the memory behaviour of SQLite is quite strange, an example: > lets say that a select sentence reserves 1000kb of memory, once this > local process has finished memory keeps reserved for the program (it > should be freed), if another process executes a select sentence that > needs 200kb SQLite will not reserve 200k more, it will use 200k of the > previous 1000k reserved. if a 3rd process executes a select sentence > that needs 1300k SQlite will reserve 300kb more and those 1300kb will > not be freed until the main dialog application closes (even if the 3 > process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. > The problem is that if a select sentence consume most of the memory it > will not be freed and the program will execute very slow until you > exit from the application because there will be so little memory left > for other not SQLite process that the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing "PRAGMA cache_size=8" or some really low number and tell me if its still "leaking". Robert To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd.
Re: [sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
- Original Message - From: <[EMAIL PROTECTED]> I have run your program on the CE emulator (Pocket PC 2003) and i got the same memory leak. I have inserted 2 buttons on a MFC dialog application. The first button executes your code and the second button closes the application. If you examine the memory you will discover that the program only free the memory once you exit from the apllication, meanwhile it reserves memory as its needed (on demand, but see details below). the memory behaviour of SQLite is quite strange, an example: lets say that a select sentence reserves 1000kb of memory, once this local process has finished memory keeps reserved for the program (it should be freed), if another process executes a select sentence that needs 200kb SQLite will not reserve 200k more, it will use 200k of the previous 1000k reserved. if a 3rd process executes a select sentence that needs 1300k SQlite will reserve 300kb more and those 1300kb will not be freed until the main dialog application closes (even if the 3 process where local methods or functions). Ok this is where you lost me. 3 processes? Is your program running 3 times on the CE platform? If CE is running 3 instances of your program, then they definitely won't be sharing any memory and yes you'll definitely run out. Also if I recall correctly, CE 5.0 will not let you run multiple instances of the same program. If you're talking about 3 SELECT statements in the same program using the same connection instance, then that's another story. The problem is that if a select sentence consume most of the memory it will not be freed and the program will execute very slow until you exit from the application because there will be so little memory left for other not SQLite process that the program might be unusable. SQLite's default cache size is about 3mb. After opening a connection, try executing "PRAGMA cache_size=8" or some really low number and tell me if its still "leaking". Robert
RE: [sqlite] Re: concers about database size
> -Original Message- > From: Jay Sprenkle [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 16, 2006 10:15 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Re: concers about database size > > > > Does sound like an awful lot of data. I think the question might be > > reworded to ask is there any manageable logical groups of data that > > might lend themselves to simple segmentation into separate > > tables/databases? > > I thought about asking that as well, but wondered if there > wasn't a simpler > solution that would make his existing setup work. > One might want to consider many things other than actual data bulk. With that much data, there must be many interested parties wanting to visualize, massage, and manipulate. Given only exposure to the general type and volume of data, it is hard to estimate the "number of seats" that could/will be interacting. But, I think I smell a possible "Client Server" environment. Even simple query runs most likely won't be "snap your fingers" kind'a deals. Fred ...
Re: [sqlite] Re: concers about database size
> > You may legitimately need one really large table but most applications > > don't. > > The most common scenarios: > - get a single marker from subset of individuals > - get a subset of markers from a single individual > - get a subset of markers from a subset of individuals > > There is no "old" data one could get rid of (as in your example from sprint). > Too bad. My guess is that you're doing the right thing trying to consolidate. It's going to take expensive hardware no matter what you end up doing. Your design is larger than any I've ever heard of using sqlite. My guess is that it will work if you have enough RAM. I've used much larger databases than this on all of the big commercial database engines (sqlserver, db2, informix, oracle). Any of them will certainly work for you.
Re: [sqlite] concers about database size
Hello Daniel, Daniel Franke wrote: Was sqlite designed for those numbers? The docs state that sqlite supports "databases up to 2 terabytes in size". OTOH, "supports" is not the same as "works-well-with"?! Any suggestions whether my descision to use sqlite was appropiate for this table design? http://www.sqlite.org/faq.html#q10 From this page... A database is limited in size to 2 tibibytes (241 bytes). That is a theoretical limitation. In practice, you should try to keep your SQLite databases below 100 gigabytes to avoid performance problems. If you need to store 100 gigabytes or more in a database, consider using an enterprise database engine which is designed for that purpose. Best Regards, Kervin
RE: [sqlite] File locking additions
Hi, > I definitely like all the things you've proposed. We currently use > SQLite over NFS (to a NetApp) and while it mostly works, we do > occasionally get locking issues that I'd love to be able to > get rid of. Do you run Solaris or Linux? Cheers, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT 135 Bishopsgate LONDON EC2M 3TP Tel: +44 (0) 207 085 1088 *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
Re: [sqlite] SQLite_Busy from DBD-SQLite
I'm using Perl 5.8.7 on Windows. When the database is locked, the statement execution returns a FALSE value like it does for any other error. On 3/16/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > On 3/16/06, Sripathi Raj <[EMAIL PROTECTED]> wrote: > > Hi, > > > > How do I know that a statement failed to execute because the database > was > > locked? > > > > Raj > > > > Raj, what language are you using? >
Re: [sqlite] Re: concers about database size
> How do you use your data? Do you really need to compare all the information > about any and all individuals? > [...] > You may legitimately need one really large table but most applications > don't. The most common scenarios: - get a single marker from subset of individuals - get a subset of markers from a single individual - get a subset of markers from a subset of individuals There is no "old" data one could get rid of (as in your example from sprint).
Re: [sqlite] File locking additions
On 7-Mar-06, at 7:06 PM, Adam Swift wrote: In order to provide locking support for database files mounted from remote file systems (NFS, SMB, AFP, etc) as well as provide compatible locking support between database clients both local and remote, I would like to propose some additions to the existing database file locking behavior. I definitely like all the things you've proposed. We currently use SQLite over NFS (to a NetApp) and while it mostly works, we do occasionally get locking issues that I'd love to be able to get rid of. To those who have said "create a server" this simply isn't possible on an appliance NFS server. URIs will allow us to do something like ?mkdir=1 to specify that if the directory didn't exist to make it first. Very handy. Matt.
Re: [sqlite] Re: concers about database size
> The table in question: > CREATE TABLE genotypes (markerid integer NOT NULL REFERENCES marker(id), > individualid integer NOT NULL REFERENCES > individuals(id), > genA integer, > genB integer); > > I don't see how to segment this. I get 1.000+ individuals and up to > 500.000 markers. In a full cross of both, I get 500.000.000+ rows. It > should be possible to use a table per marker or a table per individual > -- but this would strike me odd. Even if this table is split up per > individual (within the same dbfile), say, I fail to identify any gain The way to segment data is by usage. At Sprint they grouped cell phone calling records by billing cycle. Their system generates LOTS of raw data. Once a billing cycle was completed they had no need to query data from multiple billing cycles. The previous billing cycle data would be summarized and that summary info kept longer term. The detailed records could then be archived and not kept in active storage. How do you use your data? Do you really need to compare all the information about any and all individuals? > Jay wrote > > In sqlite you can attach two databases and query across them. > > Have you considered leaving it as many databases for each genomic > > region and writing a tool that attaches what it needs to perform it's query? > I'm not sure whether I understand you correctly. You suggest to employ > as many small > databases as there are flat files? To attach and detach them as needed? Group data in whatever is a logical 'lump' for your particular usage. Say it's by "region". If you need to compare two regions attach the two region databases and you can do your queries. You may legitimately need one really large table but most applications don't.
[sqlite] Re: concers about database size
Note: this is a combined reply to answers sent by Fred and Jay. Fred wrote: > > > If so, are you trying to use a blender to stir the ocean? > > > You might reevaluate if you're using the right tool for the job. > > > > That's my question: IS sqlite the right tool here? =) > > And I believe he is asking, "Is this the right problem, here." :-) > > Does sound like an awful lot of data. I think the question might be > reworded to ask is there any manageable logical groups of data that > might lend themselves to simple segmentation into separate > tables/databases? The table in question: CREATE TABLE genotypes (markerid integer NOT NULL REFERENCES marker(id), individualid integer NOT NULL REFERENCES individuals(id), genA integer, genB integer); I don't see how to segment this. I get 1.000+ individuals and up to 500.000 markers. In a full cross of both, I get 500.000.000+ rows. It should be possible to use a table per marker or a table per individual -- but this would strike me odd. Even if this table is split up per individual (within the same dbfile), say, I fail to identify any gain ...?! Jay wrote > In sqlite you can attach two databases and query across them. > Have you considered leaving it as many databases for each genomic > region and writing a tool that attaches what it needs to perform it's query? I'm not sure whether I understand you correctly. You suggest to employ as many small databases as there are flat files? To attach and detach them as needed? To put together both suggestions as I understand them: it's most appealing to have a database for each individual and a master database that knows about all the others ... that's an approach I have to think about ...
[sqlite] RE:Re: [sqlite] RE: SQLite memory leak on Windows CE
Hi Again Robert,
I have run your program on the CE emulator (Pocket PC 2003) and i got the same
memory leak.
I have inserted 2 buttons on a MFC dialog application.
The first button executes your code and the second button closes the
application.
If you examine the memory you will discover that the program only free the
memory once you exit from the apllication, meanwhile it reserves memory as its
needed (on demand, but see details below).
the memory behaviour of SQLite is quite strange, an example:
lets say that a select sentence reserves 1000kb of memory, once this local
process has finished memory keeps reserved for the program (it should be
freed), if another process executes a select sentence that needs 200kb SQLite
will not reserve 200k more, it will use 200k of the previous 1000k reserved. if
a 3rd process executes a select sentence that needs 1300k SQlite will reserve
300kb more and those 1300kb will not be freed until the main dialog application
closes (even if the 3 process where local methods or functions).
The problem is that if a select sentence consume most of the memory it will not
be freed and the program will execute very slow until you exit from the
application because there will be so little memory left for other not SQLite
process that the program might be unusable.
Any ideas,
Thank you in advance
Eduardo
---Mensaje original---Ok, here's what I did ...
On the desktop I created a SQLite database with one table and inserted
120,000 rows into it. I then copied it over to the CE emulator.
Then I ran the following code on the CE (Pocket PC 2003 SE) emulator:
#include
#include
#include "sqlite3.h"
int WINAPI _tWinMain(HINSTANCE hInst, HINSTANCE hPrev, LPTSTR pszCmdLine,
int nCmdShow)
{
sqlite3 *pdb;
int rc;
for (int n = 0; n < 1; n )
{
rc = sqlite3_open("\\test.db3", &pdb);
if (rc) break;
rc = sqlite3_exec(pdb, "select * from testcase", 0, 0, 0);
if (rc) break;
rc = sqlite3_close(pdb);
if (rc) break;
}
return 0;
}
On the first call to sqlite3_exec(), available program memory dropped from
an initial 10.5mb to 8.45mb. However, once that was done, available memory
remained rock solid for the duration of the loop at a constant 8.45mb and no
non-zero error codes were ever returned.
Robert
El día del padre está cerca... ¿Ya tienes el regalo?
http://buscawanadoo.es/search?type=pref&destino=web&origen=homespot&buscar=Regalos%20Día%20del%20Padre
Re: [sqlite] Re: concers about database size
On 3/16/06, Daniel Franke <[EMAIL PROTECTED]> wrote: > Each single file contains detailed genotypic information of many > individuals at a given genomic region. We have to implement _loads_ of > quality control measures to ensure the maximum possible data > correctness. Earlier, we did this manually. We can't do this any > longer. Handling that many files is nightmare, especially if a couple > of different individuals are involved. For example, it is horrible to > extract subsets from that mess (a few markers from a couple of > individuals is a major problem with many, many files, but easily > solved in a relational db). > In sqlite you can attach two databases and query across them. Have you considered leaving it as many databases for each genomic region and writing a tool that attaches what it needs to perform it's query? I would bet sqlite could handle the 20 gig file, but it will take a big machine with a lot of RAM and disk space.
Re: [sqlite] Re: concers about database size
> Does sound like an awful lot of data. I think the question might be > reworded to ask is there any manageable logical groups of data that > might lend themselves to simple segmentation into separate > tables/databases? I thought about asking that as well, but wondered if there wasn't a simpler solution that would make his existing setup work. > > Commercial databases often range into terabytes but I doubt many > accomplish those numbers with a single table. None that I have worked > on ever have, including "never ever forget", (spool or delete) SAP:-) Yeah, I just noticed some of them have support for transparently querying across multiple tables to handle this sort of thing without so much pain!
[sqlite] Re: concers about database size
> > > You might reevaluate if you're using the right tool for the job. > > That's my question: IS sqlite the right tool here? =) > Then I guess the right question is what are your goals? To make > maintenance easier? > Why were the thousands of files a problem? Short answer: I want to improve - data handling - data consistency and correctness - data extraction Each single file contains detailed genotypic information of many individuals at a given genomic region. We have to implement _loads_ of quality control measures to ensure the maximum possible data correctness. Earlier, we did this manually. We can't do this any longer. Handling that many files is nightmare, especially if a couple of different individuals are involved. For example, it is horrible to extract subsets from that mess (a few markers from a couple of individuals is a major problem with many, many files, but easily solved in a relational db).
RE: [sqlite] Re: concers about database size
> -Original Message- > From: Daniel Franke [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 16, 2006 9:32 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Re: concers about database size > > > > > But now, there's another thing.I figured out how large my database > > > will become and I'm scared of its size: up to 20GB and > more! A single > > > table, 4 columns, each holding an integer (32 bit) will have > > > approximately 750 million rows. This mounts up to ~11GB. Adding an > > > unique two-column index, I get another 10GB worth of > data, that's an > > > awful lot. > > > > Do you really need 10 gig of data in the same database? > > At the Sprint data warehouse they kept really large amounts of data > > (call records for all the cell phone usage), but in a separate > > file/database for each billing cycle > > The original idea was to get rid of thousands of files to > store their data > in one single container. Those (ASCII) files add up to approx 5GB ... > > > If so, are you trying to use a blender to stir the ocean? > > You might reevaluate if you're using the right tool for the job. > > That's my question: IS sqlite the right tool here? =) And I believe he is asking, "Is this the right problem, here." :-) Does sound like an awful lot of data. I think the question might be reworded to ask is there any manageable logical groups of data that might lend themselves to simple segmentation into separate tables/databases? Commercial databases often range into terabytes but I doubt many accomplish those numbers with a single table. None that I have worked on ever have, including "never ever forget", (spool or delete) SAP:-) Fred
RE: [sqlite] DBD::SQLite SQLite Ver 3.2.7
Title: RE: [sqlite] DBD::SQLite SQLite Ver 3.2.7 Steve, Jarl, Thanks for the replies. Eric thanks for the info. So this needs to be a patch in the DBD-SQLite-1.11 module? Something like the attached patch I assume? Matt Seargeant, How do we get this into the CPAN archives? Thank you, Christian Werner From: Jarl Friis [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 16, 2006 1:42 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DBD::SQLite SQLite Ver 3.2.7 Eric Bohlman earlier wrote this: -- You'll need to go into dbdimp.c and change the two calls to sqlite3_prepare() so that the third argument is -1 rather than zero. This is due to the change in check-in 3047. -- Jarl -Original Message- From: Steve Green [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 15, 2006 6:29 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] DBD::SQLite SQLite Ver 3.2.7 In order to get this to work, modify DBD::SQLite dbdimp.c so that the 3rd parameter of each call to sqlite3_prepare() is -1 instead of 0. Steve Chris Werner wrote: > Hello, > > The current release of SQLite, 3.3.4 does not seem to be compatible with the > perl DBD driver. The embedded SQLite package works quite well, But I would > like to have the full SQLite package installed [so I can use sqlite3]. I do > not seem to be able to locate the source archives, I would be looking for > Ver 3.2.7: sqlite-3.2.7.tar.gz > > I understand that the problem is a know bug... any feedback on whether a > change should be made in SQLite or the CPAN DBD package? > > Thanks, > Christian Werner > -- Steve Green SAVVIS Transforming Information Technology SM This message contains information which may be confidential and/or privileged. Unless you are the intended recipient (or authorized to receive for the intended recipient), you may not read, use, copy or disclose to anyone the message or any information contained in the message. If you have received the message in error, please advise the sender by reply e-mail at [EMAIL PROTECTED] and delete the message and any attachment(s) thereto without retaining any copies.
Re: [sqlite] Re: concers about database size
On 3/16/06, Daniel Franke <[EMAIL PROTECTED]> wrote: > The original idea was to get rid of thousands of files to store their data > in one single container. Those (ASCII) files add up to approx 5GB ... > > > If so, are you trying to use a blender to stir the ocean? > > You might reevaluate if you're using the right tool for the job. > > That's my question: IS sqlite the right tool here? =) Then I guess the right question is what are your goals? To make maintenance easier? Why were the thousands of files a problem?
[sqlite] Re: concers about database size
> > But now, there's another thing.I figured out how large my database > > will become and I'm scared of its size: up to 20GB and more! A single > > table, 4 columns, each holding an integer (32 bit) will have > > approximately 750 million rows. This mounts up to ~11GB. Adding an > > unique two-column index, I get another 10GB worth of data, that's an > > awful lot. > > Do you really need 10 gig of data in the same database? > At the Sprint data warehouse they kept really large amounts of data > (call records for all the cell phone usage), but in a separate > file/database for each billing cycle The original idea was to get rid of thousands of files to store their data in one single container. Those (ASCII) files add up to approx 5GB ... > If so, are you trying to use a blender to stir the ocean? > You might reevaluate if you're using the right tool for the job. That's my question: IS sqlite the right tool here? =)
Re: [sqlite] concers about database size
On 3/16/06, Daniel Franke <[EMAIL PROTECTED]> wrote: > Hi all ... > a couple of days back, I had a question about the performance of large > tables. You really got me going there, thanks again! > > But now, there's another thing.I figured out how large my database > will become and I'm scared of its size: up to 20GB and more! A single > table, 4 columns, each holding an integer (32 bit) will have > approximately 750 million rows. This mounts up to ~11GB. Adding an > unique two-column index, I get another 10GB worth of data, that's an > awful lot. Do you really need 10 gig of data in the same database? At the Sprint data warehouse they kept really large amounts of data (call records for all the cell phone usage), but in a separate file/database for each billing cycle If so, are you trying to use a blender to stir the ocean? You might reevaluate if you're using the right tool for the job.
[sqlite] concers about database size
Hi all ... a couple of days back, I had a question about the performance of large tables. You really got me going there, thanks again! But now, there's another thing.I figured out how large my database will become and I'm scared of its size: up to 20GB and more! A single table, 4 columns, each holding an integer (32 bit) will have approximately 750 million rows. This mounts up to ~11GB. Adding an unique two-column index, I get another 10GB worth of data, that's an awful lot. Was sqlite designed for those numbers? The docs state that sqlite supports "databases up to 2 terabytes in size". OTOH, "supports" is not the same as "works-well-with"?! Any suggestions whether my descision to use sqlite was appropiate for this table design? Another question: is there any way to specify a comparison function for blobs? I thought about packing those 128 bits. it should be possible to get rid of some overhead to reduce them to 64 (using a bitset). This bitset could then be stored in a single column as blob. Nevertheless, to ensure uniqueness in two of those four columns, I have to specify my own comparison function. Any chance to do this? Thanks for your time! With kind regards Daniel
Re: [sqlite] SQLITE_CORRUPT problem in Mac OS X
"Srinivasan Thandapani" <[EMAIL PROTECTED]> wrote: > Hi, > > In my Mac OS X, I am linking sqlite(3.2.8) library statically to my C++ > program. > > I have defined macros > -DNO_TCL -DHAVE_USLEEP -DTEMP_STORE=3 > -DSQLITE_MAX_PAGE_SIZE=32768 -DTHREADSAFE=1 > in Makefile which creates the sqlite library. > > But often I am getting SQLITE_CORRUT error. > > My program works well in Windows & linux and also in FreeBSD 5.2.1 & 5.4. > But only in Mac, I am having this problem. Is there any option I should set > for Mac?? > > How can I rectify this? Any help! > There are no special options you need to set for Mac. Database corruption is not necessary the fault of SQLite. (In fact, it usually is not.) Some other program might be corrupting the file. Or perhaps a stray pointer in your program is overwriting some of SQLites data and causing curruption that way. Without additional information it is difficult to diagnose your problem. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] SQLite_Busy from DBD-SQLite
On 3/16/06, Sripathi Raj <[EMAIL PROTECTED]> wrote: > Hi, > > How do I know that a statement failed to execute because the database was > locked? > > Raj > Raj, what language are you using?
[sqlite] SQLITE_CORRUPT problem in Mac OS X
Hi, In my Mac OS X, I am linking sqlite(3.2.8) library statically to my C++ program. I have defined macros -DNO_TCL -DHAVE_USLEEP -DTEMP_STORE=3 -DSQLITE_MAX_PAGE_SIZE=32768 -DTHREADSAFE=1 in Makefile which creates the sqlite library. But often I am getting SQLITE_CORRUT error. My program works well in Windows & linux and also in FreeBSD 5.2.1 & 5.4. But only in Mac, I am having this problem. Is there any option I should set for Mac?? How can I rectify this? Any help! Regards, Srini
RE: [sqlite] Help needed to diagnose "NOT IN" query
Hello Ulrich, > I don't know wether it's faster, but try > > select a,b,c from d where c in (select c from d except select > c from e) Sorry to say but this made no difference :-( I'll try my method next... Thanks for your help, David Carter-Hitchin. -- Royal Bank of Scotland Interest Rate Derivatives IT 135 Bishopsgate LONDON EC2M 3TP *** The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. Authorized and regulated by the Financial Services Authority This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate. Visit our websites at: http://www.rbos.com http://www.rbsmarkets.com
[sqlite] SQLite_Busy from DBD-SQLite
Hi, How do I know that a statement failed to execute because the database was locked? Raj
