RE: [sqlite] why i can not open the new database i created at AT91ARM9200?
Hi zhang jie, I don’t think there was a problem on the creation and updating of the database. You see, I log every sql statement execute from database creation and updates, and I didn’t see any sql error or any error in that case. Also, I open the database using command line in linux and it works fine. -Original Message- From: ½Ü ÕÅ [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 7:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] why i can not open the new database i created at AT91ARM9200? Hi ryan bel brillo, First of all,thank you for your reply. The problem is not a compatibility with database created in linux and windows . I downloaded source code sqlite-2.8.17.tar.gz through your website under windows OS. But I gunziped and cross-compiled it for AT91ARM9200(development board) under linux OS. I run the binary file "sqlite" at AT91ARM9200. #./sqlite test.sqlite //Am I creating a new database"test.sqlite"? sqlite>CREATE TABLE test(name varchar(80); SQL erro:unable to open database test.sqlite # #./sqlite test.db //Am I creating a new database"test.db"? sqlite>CREATE TABLE test(name varchar(80); SQL erro:unable to open database test.db # # vi test.sqlite # chmod 777 test.sqlite # ./sqlite test.sqlite sqlite>CREATE TABLE test(name varchar(80)); SQL erro:can not write to test.sqlite,it is readonly file. sqlite> # vi test.db # chmod 777 test.db # ./sqlite test.db sqlite>CREATE TABLE test(name varchar(80)，num smallint); SQL erro:can not write to test.db,it is readonly file. sqlite> So why i can not open the new database i created at AT91ARM9200? I guess i didn't create database correctly,aren't you? sincerely zhang jie ryan bel brillo <[EMAIL PROTECTED]> 写道： Hi all, Im new in this list and with sqlite. Im not sure if this topic have been discussed before. Anyway, I have this problem regarding sqlite. I created a c program that would create a new sqlite database from some sql file then update it every five minutes using sql statement stored in a postgresql database. Im using a version 3.3.2 sqlite database and sqlite3 library in c. The sqlite database and c program was executed in a linux OS. When I tried to open my created database in a SQLite database browser in windows It shows nothing but in linux it shows the correct tables and data. Also, I create a sqlite database in windows then open it in linux it works fine. Also, I created a simple sqlite database manually but it still cant be read in windows. Is there a compatibility problem with database created in linux and windows? I remember this problem on occurs on sqlite version 2.6.2 or earlier.. Or is there a problem in the way I create the database? The sqlite browser im using can read sqlite database version 3.x. Thanks - 雅虎1G免费邮箱百分百防垃圾信
RE: [sqlite] Database created in linux cant be read in windows
Im using a software ipswitch WS_FTP to transfer the file from linux to windows. Im not sure before if I configured it to binary transfer but I tried it again and make sure to configure it and set it to binary transfer, still im getting the same result. Currently, Im trying to create the database in windows then use the created database as my base db in linux. Then I'll just have to update the base db in linux using the different sql statement in postgresql and transfer it back to windows if the base db can still be read with the updates. On trying to read the file on sqlite3 command line program in windows; I'll try it later. -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 6:10 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Database created in linux cant be read in windows ryan bel brillo wrote: > Is there a compatibility problem with database created in linux and > windows? I remember this problem on occurs on sqlite version 2.6.2 or > earlier.. Or is there a problem in the way I create the database? How are you getting the file from Linux to Windows? If you used FTP it's possible that you forgot to configure your FTP client for a binary transfer. In that case certain characters would have been added. If you didn't transfer the files via FTP then I really can't say, and would suspect a corrupt file. One thing you might try though is reading the file on Windows with the sqlite3 command line program. That would be the authoritative source. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management
[sqlite] why i can not open the new database i created at AT91ARM9200?
Hi ryan bel brillo, First of all,thank you for your reply. The problem is not a compatibility with database created in linux and windows . I downloaded source code sqlite-2.8.17.tar.gz through your website under windows OS. But I gunziped and cross-compiled it for AT91ARM9200(development board) under linux OS. I run the binary file "sqlite" at AT91ARM9200. #./sqlite test.sqlite //Am I creating a new database"test.sqlite"? sqlite>CREATE TABLE test(name varchar(80); SQL erro:unable to open database test.sqlite # #./sqlite test.db //Am I creating a new database"test.db"? sqlite>CREATE TABLE test(name varchar(80); SQL erro:unable to open database test.db # # vi test.sqlite # chmod 777 test.sqlite # ./sqlite test.sqlite sqlite>CREATE TABLE test(name varchar(80)); SQL erro:can not write to test.sqlite,it is readonly file. sqlite> # vi test.db # chmod 777 test.db # ./sqlite test.db sqlite>CREATE TABLE test(name varchar(80)，num smallint); SQL erro:can not write to test.db,it is readonly file. sqlite> So why i can not open the new database i created at AT91ARM9200? I guess i didn't create database correctly,aren't you? sincerely zhang jie ryan bel brillo <[EMAIL PROTECTED]> 写道： Hi all, Im new in this list and with sqlite. Im not sure if this topic have been discussed before. Anyway, I have this problem regarding sqlite. I created a c program that would create a new sqlite database from some sql file then update it every five minutes using sql statement stored in a postgresql database. Im using a version 3.3.2 sqlite database and sqlite3 library in c. The sqlite database and c program was executed in a linux OS. When I tried to open my created database in a SQLite database browser in windows It shows nothing but in linux it shows the correct tables and data. Also, I create a sqlite database in windows then open it in linux it works fine. Also, I created a simple sqlite database manually but it still cant be read in windows. Is there a compatibility problem with database created in linux and windows? I remember this problem on occurs on sqlite version 2.6.2 or earlier.. Or is there a problem in the way I create the database? The sqlite browser im using can read sqlite database version 3.x. Thanks - 雅虎1G免费邮箱百分百防垃圾信
Re: [sqlite] Sorting a text field as if it were integer/float
"Randy J. Ray" <[EMAIL PROTECTED]> wrote: > This may be a basic SQL question, but I can't find the answer in the > SQL-related documents on the site, so... > > I have a field in a table that is typed as text, though it is 99% of the time > numerical. (It's used to track issue numbers of magazines, which for some > esoteric publications may be alphanumeric.) > > I'd like to sort a query by this field, but when I do so "2" sorts after "10", > as is the age-old comp-sci problem with treating numbers as strings. Is there > a > way, maybe with some variant of "SELECT field AS", to get SQLite to treat this > data as numerical for the sake of sorting? > SQLite version 1 used to do that kind of sorting by default. But people hated it, so I took it out. Probably you can search through the CVS archives, pull out the old comparison function, and change it into a collating function. Register the collating function and then sort using it: SELECT ... ORDER BY catnum COLLATE string_as_number; -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Database created in linux cant be read in windows
ryan bel brillo wrote: > Is there a compatibility problem with database created in linux and > windows? I remember this problem on occurs on sqlite version 2.6.2 or > earlier.. Or is there a problem in the way I create the database? How are you getting the file from Linux to Windows? If you used FTP it's possible that you forgot to configure your FTP client for a binary transfer. In that case certain characters would have been added. If you didn't transfer the files via FTP then I really can't say, and would suspect a corrupt file. One thing you might try though is reading the file on Windows with the sqlite3 command line program. That would be the authoritative source. Clay Dowling -- CeaMuS http://www.ceamus.com Simple Content Management
[sqlite] Database created in linux cant be read in windows
Hi all, Im new in this list and with sqlite. Im not sure if this topic have been discussed before. Anyway, I have this problem regarding sqlite. I created a c program that would create a new sqlite database from some sql file then update it every five minutes using sql statement stored in a postgresql database. Im using a version 3.3.2 sqlite database and sqlite3 library in c. The sqlite database and c program was executed in a linux OS. When I tried to open my created database in a SQLite database browser in windows It shows nothing but in linux it shows the correct tables and data. Also, I create a sqlite database in windows then open it in linux it works fine. Also, I created a simple sqlite database manually but it still cant be read in windows. Is there a compatibility problem with database created in linux and windows? I remember this problem on occurs on sqlite version 2.6.2 or earlier.. Or is there a problem in the way I create the database? The sqlite browser im using can read sqlite database version 3.x. Thanks
[sqlite] Sorting a text field as if it were integer/float
This may be a basic SQL question, but I can't find the answer in the SQL-related documents on the site, so... I have a field in a table that is typed as text, though it is 99% of the time numerical. (It's used to track issue numbers of magazines, which for some esoteric publications may be alphanumeric.) I'd like to sort a query by this field, but when I do so "2" sorts after "10", as is the age-old comp-sci problem with treating numbers as strings. Is there a way, maybe with some variant of "SELECT field AS", to get SQLite to treat this data as numerical for the sake of sorting? Randy -- """ Randy J. RayCampbell, CAhttp://www.rjray.org [EMAIL PROTECTED] Silicon Valley Scale Modelers: http://www.svsm.org
Re: [sqlite] sqlite performance with sizeable tables
- Original Message > From: Joe Wilson <[EMAIL PROTECTED]> > The disk read/write reordering optimization only works if the > database file is contiguous on the disk and not fragmented. > --- [EMAIL PROTECTED] wrote: > > Basically, the db file is accessed with seek + read/write operations. > > Given a set of such operations, it can be very beneficial to reorder those > > operations so that > > the file is accessed going from the begining to the end of the file (and > > not pure random). > > This is a problem mostly for reads (writes are reordered when doing a > > combination of async > > writes + flush at the end). This was my first idea on trying to speed things up, after that I realized that there had to be a better and more reliable way to do async i/o. In fact, I found that the aio API does exactly that See http://mia.ece.uic.edu/~papers/WWW/books/posix4/DOCU_008.HTM The aio_read/aio_write + aio_suspend calls are POSIX and available on linux, bsd, solaris (and Windows event api is very similar to it). This api, when used with aio_suspend will not rely on signals like SIG_IO, but only on low level kernel signals (invisible to the application), making this thread safe and pretty much without any side effects on the rest of the application (that uses sqlite). After finding out about this api, I found out that at least mysql and postgresql use it, so I am guessing that changing the sql engine to generate batches of read/writes is possible. My guess is that using this api will increase performance a lot as the hard drive heads won't have to go back and forth, seeking at random places on the disk (thus reducing the impact of having small caches). Nicolas
Re: [sqlite] Re: Re: Multithreaded SQLITE_BUSY not handled?
Yeah if I put BEGIN IMMEDIATE in thread1 as well, then it works, but as you say I don't see why I need to do this. Perhaps the implicit transaction which is created in my 1-line INSERT statement isn't an immediate transaction?? Seems like there should be a flag or pragma or something to force all transactions to be immediate. -Dave On 3/13/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > > Dave Brown <[EMAIL PROTECTED]> wrote: > > Igor - ok I tried this, and now I am getting SQLITE_BUSY returned > > when I try > > to sqlite3_exec my "BEGIN IMMEDIATE" statement. So I then put that > > in a > > do-while( rc == SQLITE_BUSY) loop, and now my first thread is getting > > SQLITE_BUSY returned when it tries to execute the 1-line INSERT > > statement > > (without transaction). > > It should run the busy handler in this situation as far as I can tell - > I have no idea why it might not. > > Does anything change if you use BEGIN IMMEDIATE for DB1 too? > > Note that when you don't explicitly start a transaction, one is started > for you anyway as soon as you try to execute a statement, and is > automatically committed when the statement completes. So it is incorrect > to way that you run a statement without transaction. > > Igor Tandetnik > >
[sqlite] Re: Re: Multithreaded SQLITE_BUSY not handled?
Dave Brown <[EMAIL PROTECTED]> wrote: Igor - ok I tried this, and now I am getting SQLITE_BUSY returned when I try to sqlite3_exec my "BEGIN IMMEDIATE" statement. So I then put that in a do-while( rc == SQLITE_BUSY) loop, and now my first thread is getting SQLITE_BUSY returned when it tries to execute the 1-line INSERT statement (without transaction). It should run the busy handler in this situation as far as I can tell - I have no idea why it might not. Does anything change if you use BEGIN IMMEDIATE for DB1 too? Note that when you don't explicitly start a transaction, one is started for you anyway as soon as you try to execute a statement, and is automatically committed when the statement completes. So it is incorrect to way that you run a statement without transaction. Igor Tandetnik
Re: [sqlite] Re: Multithreaded SQLITE_BUSY not handled?
Igor - ok I tried this, and now I am getting SQLITE_BUSY returned when I try to sqlite3_exec my "BEGIN IMMEDIATE" statement. So I then put that in a do-while( rc == SQLITE_BUSY) loop, and now my first thread is getting SQLITE_BUSY returned when it tries to execute the 1-line INSERT statement (without transaction). So I can wrap all these calls in a do-while loop to check for SQLITE_BUSY, but isn't this the point of declaring a busy_handler ?? Why wouldn't the database use the busy-handler in the case where it is trying to execute my 1-line INSERT statement? -Dave On 3/13/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > > Dave Brown <[EMAIL PROTECTED]> wrote: > > Hi all - I am seeing a strange problem where I have multiple threads > > trying to both do writes to the database, and one thread gets back an > > immediate SQLITE_BUSY even though *both* have set the busy handler to > > 10 seconds, and the write operation is much much faster than this (on > > the order of milliseconds). > > This happens when the two threads get into a deadlock. A situation is > possible when two threads both start a transaction, then both try to > write, and neither can proceed since the other holds a transaction open. > In this case, waiting is pointless and will only make the situation > worse. The only way out is for one thread to roll back its transaction > and retry from the beginning. > > Open your writer transactions with BEGIN IMMEDIATE to avoid getting into > this situation. > > Igor Tandetnik > >
[sqlite] Re: Multithreaded SQLITE_BUSY not handled?
Dave Brown <[EMAIL PROTECTED]> wrote: Hi all - I am seeing a strange problem where I have multiple threads trying to both do writes to the database, and one thread gets back an immediate SQLITE_BUSY even though *both* have set the busy handler to 10 seconds, and the write operation is much much faster than this (on the order of milliseconds). This happens when the two threads get into a deadlock. A situation is possible when two threads both start a transaction, then both try to write, and neither can proceed since the other holds a transaction open. In this case, waiting is pointless and will only make the situation worse. The only way out is for one thread to roll back its transaction and retry from the beginning. Open your writer transactions with BEGIN IMMEDIATE to avoid getting into this situation. Igor Tandetnik
[sqlite] Multithreaded SQLITE_BUSY not handled?
Hi all - I am seeing a strange problem where I have multiple threads trying to both do writes to the database, and one thread gets back an immediate SQLITE_BUSY even though *both* have set the busy handler to 10 seconds, and the write operation is much much faster than this (on the order of milliseconds). Both threads open handles to the same database, *both* set the busy_handler to 10 seconds. In my test program, they do the following: DB1 -> INSERT OR REPLACE INTO ... (single row replace, in a table of just 1 row). DB2 -> BEGIN TRANSACTION DB2 -> DELETE FROM ... (the table it operates on is actually empty) DB2 -> END TRANSACTION Often times DB2 will return a SQLITE_BUSY when trying to step() the DELETE statement. I understand that the first thread, DB1, is writing the database at this time, but what I don't get is why the busy-handler isn't being invoked here?? Shouldn't the busy handler take care of the fact that DB1 is writing, so after it's done, DB2 will be allowed to finish its write transaction? Why do I get back an immediate SQLITE_BUSY in this case? Thanks for any help, Dave
Re: [sqlite] Multithread access to DB
On 3/13/06, Rafal Rusin <[EMAIL PROTECTED]> wrote: > > Maybe you want to call this: > > > > " int sqlite3_busy_timeout(sqlite3*, int ms); > > > > This routine sets a busy handler that sleeps for a while when a table > > is locked. The handler will sleep multiple times until at least "ms" > > milliseconds of sleeping have been done. After "ms" milliseconds of > > sleeping, the handler returns 0 which causes sqlite3_exec() to return > > SQLITE_BUSY. > > > > Calling this routine with an argument less than or equal to zero turns > > off all busy handlers. " > > > > Like I said, I tried to use it without success. There was a deadlock > when I tried to use it with python pysqlite2 with 10 threads > simultanously accessing DB. If your code has a deadlock that waiting won't fix have one of the threads rollback it's transaction and retry it from the beginning. Alternatively use "begin immediate" around the code that's deadlocking. That will serialize access to the resources.
Re: [sqlite] Multithread access to DB
I had major problems with multithreaded access to a sqlite database file on an NFS mount. While not exactly your scenerio there might be some similarities. I had to come up with a crazy scheme to allow only one thread to access the sqlite database at a time. See below. I came up with a scheme where only one process would handle updating the database directly. All other processes locally or remotely would update the database through a file hand shaking protocol. Here is an example Database Updater Process (Server) Database Client Process (Client) Server defines two directories (queries and responses). Client wants to insert, update or delete data from a database. 1. client creates a file with the necessary information 2. client moves file into queries directory 3. server sees new file in queries directory 4. server parses file 5. server inserts, updates or deletes data from database. Client wants to select data from a database. 1. client creates a file with the appropriate sql statement 2. client moves file into queries directory 3. server sees new file in queries directory 4. server parses file 5. server preforms select statement 6. server creates response file 7. server moves response file into response directory 8. client sees new response file in response directory 9. client parses file 10. client obtains data This scheme is preferred over sockets because if the database updater process dies you won't lose information. All inserts, updates and deletes will be sitting in the queries directory waiting for the database updater process to start again. This is just one solution to work around the NFS problem I was having. If you find NFS does not work for you I would try either some sort of sockets implementation or some sort of file hand shaking protocol. While I thought this was originally an NFS only problem I'm not 100% convinced it is. Anyway just thought I would let you know a work around scheme that completely avoids locks. Rafal Rusin wrote: Maybe you want to call this: " int sqlite3_busy_timeout(sqlite3*, int ms); This routine sets a busy handler that sleeps for a while when a table is locked. The handler will sleep multiple times until at least "ms" milliseconds of sleeping have been done. After "ms" milliseconds of sleeping, the handler returns 0 which causes sqlite3_exec() to return SQLITE_BUSY. Calling this routine with an argument less than or equal to zero turns off all busy handlers. " Like I said, I tried to use it without success. There was a deadlock when I tried to use it with python pysqlite2 with 10 threads simultanously accessing DB. Best Regards, Rafal Rusin TouK Company (www.touk.pl) -- Software Engineer [EMAIL PROTECTED] 301.286.7951
Re: [sqlite] Multithread access to DB
> Maybe you want to call this: > > " int sqlite3_busy_timeout(sqlite3*, int ms); > > This routine sets a busy handler that sleeps for a while when a table > is locked. The handler will sleep multiple times until at least "ms" > milliseconds of sleeping have been done. After "ms" milliseconds of > sleeping, the handler returns 0 which causes sqlite3_exec() to return > SQLITE_BUSY. > > Calling this routine with an argument less than or equal to zero turns > off all busy handlers. " > Like I said, I tried to use it without success. There was a deadlock when I tried to use it with python pysqlite2 with 10 threads simultanously accessing DB. Best Regards, Rafal Rusin TouK Company (www.touk.pl)
[sqlite] Re: Re: it there a way to execute a query generated bySELECT
[EMAIL PROTECTED] wrote: BTW, yes, the schema is 'as is' and i have to convert it, but how to converts rows to columns and add to other columns? ;-) I'm not sure I understand this question. Without knowing any details about your problem, I'd suggest something like this. Change tbl from this: excelid | fld1 | fld2 | ... | fld_n 1 | a | b | ... | n to this: excelid | fldname | value 1 | fld1 | a 1 | fld2 | b ... 1 | fld_n | n Then your query becomes: insert into plan (personid, curseid, statid) select tbl.excelid, curses.id, status.id from tbl join curses on (curses.fldname = tbl.fldname) join status on (tbl.value = status.name); Igor Tandetnik
Re: [sqlite] Multithread access to DB
On 3/13/06, Rafal Rusin <[EMAIL PROTECTED]> wrote: > Hello, > > I'm having problems with famous multithread bug > "Database is locked". > I tried to work around that with sqlite3_busy_timeout > or busy_handler, but with no success. I had deadlocks. > I was using version 3.3.4 on Debian Linux. > Could You give me some links to discussion on that > topic? > Maybe there are some future plans to change > implementation so it won't return SQLITE_BUSY > error, but wait? It's quite annoying and should be replaced > with some kind of quality multithread solution. > Maybe with some semaphores or fifos on linux > and similiar things on windows. Maybe you want to call this: " int sqlite3_busy_timeout(sqlite3*, int ms); This routine sets a busy handler that sleeps for a while when a table is locked. The handler will sleep multiple times until at least "ms" milliseconds of sleeping have been done. After "ms" milliseconds of sleeping, the handler returns 0 which causes sqlite3_exec() to return SQLITE_BUSY. Calling this routine with an argument less than or equal to zero turns off all busy handlers. "
Re: [sqlite] it there a way to execute a query generated by SELECT
[EMAIL PROTECTED] wrote: > hi all, > > it there a way to execute a SQL query generated by SELECT ? > for eg. : > select 'insert into plan (personid, curseid, statid) select > tbl.excelid, ' || id || ',status.id from tbl, status where > tbl.' || fldname || ' = status.name ;' from curses; > A planned enhancement is to provide a built-in sqlite_exec() SQL function that evalutes its argument as SQL. The intended use is to implement stored procedures. But the function is not yet available. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Multithread access to DB
Hello, I'm having problems with famous multithread bug "Database is locked". I tried to work around that with sqlite3_busy_timeout or busy_handler, but with no success. I had deadlocks. I was using version 3.3.4 on Debian Linux. Could You give me some links to discussion on that topic? Maybe there are some future plans to change implementation so it won't return SQLITE_BUSY error, but wait? It's quite annoying and should be replaced with some kind of quality multithread solution. Maybe with some semaphores or fifos on linux and similiar things on windows. Best Regards, Rafal Rusin TouK Company (www.touk.pl)
Re: [sqlite] Re: it there a way to execute a query generated by SELECT
On 3/13/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Igor, > > thank you very much! > > BTW, yes, the schema is 'as is' and i have to convert it, > but how to converts rows to columns and add to other > columns? ;-) You return the result to a string variable in your program and then execute that...
Re: [sqlite] sqlite performance with sizeable tables
On Fri, 10 Mar 2006 [EMAIL PROTECTED] wrote: >- Original Message >> From: Christian Smith <[EMAIL PROTECTED]> > >> But SQLite depends on the OS caching abilities for much of it's >> performance. Removing it is like saying SQLite is rubbish on Intel >> processors after testing on a i486. > >yes and no: while it's nice to be able to rely somehow on the OS cache, >the OS can not guess what the usage pattern of the app is going to be. >Most of the time, the os will guess wrong too: a typical case is random >access of a file, the os does readahead for example that is not >beneficial in that case (and actually can slow things down). I always try >to give the best hints possible to the underlying subsystem (like never >rely 100% on the optimizer to know what to do with a bunch of code, >because it won't). > >When dealing with big databases (that don't typically fit in memory), the >OS will most likely cache the wrong kind of data, where as the lib could >cache important information such as some key offsets in the file (the >cache would contain some elements from the btree so that we can resume >directly from there). 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. [snip] >> General observations/questions: >> - What did you expect from SQLite? Can you give indication of performance >> expectations? >> - What did you use previously? How does SQLite compare on performance? >I was expecting much better performance that what I am getting right now: >before I was using the filesystem as a DB (basically, folders + >filenames->data) Namely reiserfs performs pretty well for doing this kind >of thing (millions of record). But that was wasting a lot of disk space >and hitting all sorts of limits in the various OSes. > >Tried BDB a few years ago as a replacement, but "wedged" databases and >licensing issues kept us away from it. then I red about sqlite giving >performance of the order of 25000 inserts/second. I thought that I could >probably get around 1 inserts/second on bigger databases. Do you need the high insert rate sustained? > >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. >* 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. > >> - If you're not batching, or can't batch updates, then SQLite may very > well not be performant enough due to inherent restrictions of it's > architecture. >You are not the first person to mention batching, what does that mean >exactly? I have been using inserts in transactions, isn't that it? Yes. Transactions have a fixed synchronous IO overhead, that can be amortized over a large number of inserts/updates. > >> - An idea of what your app does in general would be of assistance in >> helping you find your bottleneck. A specific test case is good if this >> is indeed the issue, but it doesn't sound like it is. >I am focusing right now on the time it takes to run a simple import from >foreign format kind of thing: One thread only, doing simple selects >(retrieve users profiles), and replace (to write back the same profiles). Again, are imports the main stay of the application? if not, then is it worth optimising for? What does your application generally do most of the time? > 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
Re: [sqlite] Re: it there a way to execute a query generated by SELECT
Igor, thank you very much! BTW, yes, the schema is 'as is' and i have to convert it, but how to converts rows to columns and add to other columns? ;-) Regards, e. Цитат на писмо от Igor Tandetnik <[EMAIL PROTECTED]>: > [EMAIL PROTECTED] wrote: > > it there a way to execute a SQL query generated by > SELECT ? > > for eg. : > > select 'insert into plan (personid, curseid, statid) > select > > tbl.excelid, ' || id || ',status.id from tbl, status > where > > tbl.' || fldname || ' = status.name ;' from curses; > > No, not really. You have a rather unusual database > schema, what with one > table storing field names from another table. I'd > reconsider this > design. > > Assuming you insist on this structure, you can do > something like this: > > insert into plan (personid, curseid, statid) > select tbl.excelid, curses.id, status.id > from tbl, status, curses > where > (case curses.fldname > when 'fld1' then tbl.fld1 > when 'fld2' then tbl.fld2 > when 'fld3' then tbl.fld3 > ... > end) = status.name; > > Igor Tandetnik > > >
[sqlite] Re: it there a way to execute a query generated by SELECT
[EMAIL PROTECTED] wrote: it there a way to execute a SQL query generated by SELECT ? for eg. : select 'insert into plan (personid, curseid, statid) select tbl.excelid, ' || id || ',status.id from tbl, status where tbl.' || fldname || ' = status.name ;' from curses; No, not really. You have a rather unusual database schema, what with one table storing field names from another table. I'd reconsider this design. Assuming you insist on this structure, you can do something like this: insert into plan (personid, curseid, statid) select tbl.excelid, curses.id, status.id from tbl, status, curses where (case curses.fldname when 'fld1' then tbl.fld1 when 'fld2' then tbl.fld2 when 'fld3' then tbl.fld3 ... end) = status.name; Igor Tandetnik
Re: [sqlite] updating SQLite to implement The Third Manifesto
Darren Duncan <[EMAIL PROTECTED]> wrote: > At 7:05 PM -0500 3/12/06, [EMAIL PROTECTED] wrote: > >Let me amplify this by pointing out that I have zero interest > >in taking SQLite in the direction of The Third Manifesto. Those > >who want to do so are welcomed - encouraged even - to fork the > >tree and go off in their own direction. Just do not expect me > >to follow along, please. > > Richard, let me ask you this, though, to clarify your position. > > Would you be willing to build a distinctly new, and different-named > product, reusing what you can of the SQLite code base (that you know > so well) in the process, mainly the virtual machine and b-tree etc, > which implements a set of specifications I provide, if you are paid > your standard custom work rates for the time spent? > Certainly. But please know that my standard custom work rate is pretty high. The work you are talking about can easily come it at 6 figures with a 6-month lead time. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] it there a way to execute a query generated by SELECT
hi all, it there a way to execute a SQL query generated by SELECT ? for eg. : select 'insert into plan (personid, curseid, statid) select tbl.excelid, ' || id || ',status.id from tbl, status where tbl.' || fldname || ' = status.name ;' from curses; regards emily - Slon.bg ™ Симпатичният магазин за книги, DVD, игри и музика http://www.slon.bg
Re: [sqlite] Academic journals for SQLite
OK thank you. John On 13/03/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > "john.newby" <[EMAIL PROTECTED]> wrote: > > Hi, I sent this request yesterday from my other address but wasn't sure > if > > anyone had received it as my other email address didn't receive it but > both > > address are still receiving other peoples questions. > > > > I was wondering if anyone knew of any journals I could use for my > University > > dissertation on SQLite. > > > > Many thanks. > > Academic journals are generally only interested in new ideas. > SQLite deliberately uses only ideas that are 20 years old or > older in order to avoid patent problems. Hence > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
