RE: [sqlite] sqlite with eVC++
=== On 2003-11-21, Clark, Chris wrote === .. >Check out http://sqlite-wince.sourceforge.net/ its not been updated in a while so its >the 2.80 version. > >Good luck, > >Chris Just a small correction, the sqlite-wince release uses the last stable sqlite version (version 2.8.6). What needs to be done is to put the cvs in sync with the current sqlite cvs. Regards, ~Nuno Lucas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQLite file IO speed
Hello, I've ported SQLite to a system where it is important for speed that when writing memory to a file that the memory blocks be aligned to 128 or even 256 bytes and that the size of the blocks be as large as possible, preferably a power of two. After looking through the code it seems like making this change would be rather involved. Does anyone have any thought on how I could modify the code to achieve one or both of these constraints? It seems like these changes could easily improve the speed of SQLite for other platforms as well. Thanks in advance, Ben Nason - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Improving concurrency in SQLite
Hello, I read your paper and it was interesting. I am an advocate for improving the speed of SQLite where ever possible and after reading your paper and applying the experiences of using MS SQL server these are my thoughts. 1. All transactions should be Read Only (no locking) with an option to turn them into write transaction 2. Writes are not applied until the COMMIT and then only after a lock is obtained 3. Refine the locking down to the table level 1. If you change all transactions to be READ Only by default this would cut down the number of locks. The process that parses the query text could then decide if a transaction is write or read-only. So this BEGIN; SELECT * FROM t1; SELECT * FROM t2; COMMIT; Would always be a read-only transaction but this BEGIN; SELECT * FROM t1; -- Some processing occurs here UPDATE t1 SET ... WHERE ...; COMMIT; Would be a write transaction and require a lock. It should add much time to the parser process to determine this up front. There could be an option to override the parser and turn the transaction into a write lock like this BEGIN WRITE; SELECT * FROM t1; SELECT * FROM t2; COMMIT; Would be a write transaction and need to obtain a write lock I would see this as something that would cut down the number of write lock required 2. In MS SQL server we have two types of READs . A normal read and a dirty READ. A clean read is data that is not in the process of changing (i.e in someone else's transaction and therefore locked) A dirty READ may contain data in a transaction and may not be valid. If the transaction rolls back then the read may contain data that is not valid or doesn't exist. Please see the attached Concurrency_Problems.PDF document. If you only apply the changes to the base tables at the commit stage then a write lock is only needed at that time. What the process could be is some thing like this Write go to a temp table in the SQLite DB file. So the write happen as they occur but just not in the base table. Then a write lock is obtained. Some sort of priority locking may be required to force a write lock could be used Then the once the lock is obtained the base table is updated. The temp table is removed and the lock releases The benefits of this is that READs can still occur while writes are taking place (in the temp table) The updating of the base table should be very quick because it would use the PK (ROWID) and not contain complex where statements. 3. Refining the locking from file to table could see more locking take place while reads are going on. I would think that option 1 and 2 are pretty simple option to be put in place (simple in comparing them against table locking) . One premise that I have always held on to with databases are that they are read far more than written too. Allowing read to occur more often and without delay give the impression of a faster database. Kind regards Greg O -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, 23 November 2003 1:09 AM To: [EMAIL PROTECTED] Subject: [sqlite] Improving concurrency in SQLite Feedback is requested on the paper found at: http://www.sqlite.org/concurrency.html Your analysis and ideas will help to make SQLite a better database engine. Thanks. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Concurrency_Problem.zip Description: Zip compressed data - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] double email
Come on fellas! Why not just setup a forum on the sqlite.org website or something, and kill this whole list server thing. Maybe Linux is just too complex for some people to handle list servers with! : - ) Thanks, Allan -Original Message- From: Bernie Cosell [mailto:[EMAIL PROTECTED] Sent: Saturday, November 22, 2003 4:07 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] double email On 22 Nov 2003 at 22:23, Bert Verhees wrote: > > It seems to me that all the people send theiur ewmail double, and > > that it is a failure in the list software configuration. > > > > This email has only one TO: sqlite-users <[EMAIL PROTECTED]> > > And no CC or BCC, thus, if this is posted twice, it is the list > > software > > > > Only one conclusion, the list software is configured wrong. It is > annoying because I am reading from small bandwith and high price for > this moment Wrong conclusion! I only received *ONE* copy of the message, so whatever is happening is specific to you. It is possible that *YOUR* appearance on the list is configured incorrectly, or that you are on the list twice or who knows what, but it is *NOT* a global problem [or if it is, it is somehow not affecting me]. Can you copy out the headers [*all* of them] from the two copies you received? Rather than bothering the entire list, you can just email them to me privately, if you'd prefer, and if there's something of list- interest in the resolution I'll email back the conclusion. /Bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] double email
On 22 Nov 2003 at 22:23, Bert Verhees wrote: > > It seems to me that all the people send theiur ewmail double, and that it > > is a failure in the list software configuration. > > > > This email has only one TO: sqlite-users <[EMAIL PROTECTED]> > > And no CC or BCC, thus, if this is posted twice, it is the list software > > > > Only one conclusion, the list software is configured wrong. It is annoying > because I am reading from small bandwith and high price for this moment Wrong conclusion! I only received *ONE* copy of the message, so whatever is happening is specific to you. It is possible that *YOUR* appearance on the list is configured incorrectly, or that you are on the list twice or who knows what, but it is *NOT* a global problem [or if it is, it is somehow not affecting me]. Can you copy out the headers [*all* of them] from the two copies you received? Rather than bothering the entire list, you can just email them to me privately, if you'd prefer, and if there's something of list- interest in the resolution I'll email back the conclusion. /Bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] double email
> It seems to me that all the people send theiur ewmail double, and that it > is a failure in the list software configuration. > > This email has only one TO: sqlite-users <[EMAIL PROTECTED]> > And no CC or BCC, thus, if this is posted twice, it is the list software Ever thought that it might be something at your end, or maybe you're subscribed twice? Nobody else is complaining about this, and I sure don't have any issues with duplicate posts. -- Richard Heyes http://www.v-webmail.co.uk - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] double email
> It seems to me that all the people send theiur ewmail double, and that it > is a failure in the list software configuration. > > This email has only one TO: sqlite-users <[EMAIL PROTECTED]> > And no CC or BCC, thus, if this is posted twice, it is the list software > > kind regards > Bert Verhees > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > Only one conclusion, the list software is configured wrong. It is annoying because I am reading from small bandwith and high price for this moment Kind regards Bert Verhees - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] double email
It seems to me that all the people send theiur ewmail double, and that it is a failure in the list software configuration. This email has only one TO: sqlite-users <[EMAIL PROTECTED]> And no CC or BCC, thus, if this is posted twice, it is the list software kind regards Bert Verhees - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] PHP, queries between databases and network
Hi, I've got few questions: 1/ I've got sqlite like PHP module (under windows). I tried this: a)execute script 1 (selecting from some table), it tooks 2 minutes b)execute script 2 , it tooks 3 minutes c)execute them both at the same time (from different browser windows), both of them stopped also at the same time and it tooks 5 minutes I've tried another questions and both of the scripts allways stopped at the same time with duration of he sum of the time fo each process separated. This is not, of course, the behaviour I would expect... Is this some feature of sqlite or an I doing something wrong?? I've also tried the same with MySql and the result should be like 1/ first script alone - 2 minutes 2/ second script alone - 3 minutes 3/ both of them at the same time - first took 3 minutes, second took 4 minutes... 2/ Can I make queries processing more databases? Like select users.address.city_name from users.address where countries.country.city_id=users.address.city_id... That's stupid examlpe but I have to join 3 tables in 3 different databases (I cannot put the tables into one db, it's customer's request). 3/ What are you expiriences about using sqlite.dll like database server on "server computer" that is accessing from more "client computers"??? Brona - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Improving concurrency in SQLite
D. Richard Hipp wrote: Feedback is requested on the paper found at: Richard, SQLite is one of my favorite examples of good open-source s/w. I read (with great interest) your paper, and would not have a problem with any of the proposals being implemented. All would have some benefits. However, as I see it, SQLite has one very limited resource--your time. My contribution to this thread is that I would prefer you to spend your time adding SQL constructs from later standards (nothing specific in mind). That might expand the user base more than any of the suggested improvements. I know this is kinda orthogonal to your request for input, but it is being sent with the best of intensions--optimizing an already very good program. Thank you for a great product, and great support. Gerry - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Improving concurrency in SQLite
My uses of SQLite are multithreaded (mostly) single process. So, an approach that would appeal to me is "client-server" where the server is a thread in my process, and the clients are other threads. Adding a lock manager to the server thread would permit fine grained locking with ACID properties. I used the phrase "client-server" above because it is the closest proposed model to what I'd like to see... but in a single process no server is really necessary as long as all the client threads are using a lock manager service appropriately. This approach could be combined with any of the interprocess approaches described on the "Improving Concurrency In SQLite" web page, or with the present coarse grained locking for interprocess access. In other words, run multi-threaded fine grain locking and access within each process, and coordinate between the processes using coarse grained file locks. In fact, given a multi-threaded fine grain locking SQLite, one could choose between using the present coarse locking between processes, or creating an SQLite server process which spawns threads on behalf of incoming inter-process clients. Leave the decision to use (and implementation of) interprocess communication and a server process to users (or someday an optional layer on top of SQLite). As to the semantics of BEGIN, I am strongly in favor of "4.3 Defer write locks by default." This gives the best concurrency for the least effort on the part of the SQL programmer. There is typically little need for early write locking, and as you point out in section 4.3 there is a workaround when it is needed. Some database have a syntax "SELECT FOR UPDATE..." or something like that to grab a write lock early. One approach not mentioned is row (or page or table) versioning. This provides multiple versions of a row to exist simultaneously so writers never block readers and visa versa. It is an elegant approach, but suffers the same drawbacks as Mrs. Brisby's suggestion to hold all updates until transaction COMMIT (optimistic concurrency control). e - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Improving concurrency in SQLite
Mrs. Brisby wrote: Instead of keeping an "undo" journal- consider using a "changes" journal-- each writer could use it's own journal then only at commit would each worker actually start making changes to the database. > ... > Note that these changes will contain not only the values they are storing, but some kind of hash of the original value. This is used to detect whether or not two processes will UPDATE the same value. To make this simple, COMMIT should be allowed to fail (with no changes made). It is more complicated than that. In order to maintain isolation (the "I" in "ACID") the second commit must fail if at any time during the transaction it read a value that was written by the first commit, or if the first commit read a value that was written by the second commit. Figuring out what the first commit has read or written is likely to be difficult since the first commit has already occurred and (presumably) its journal has already been deleted. Very tricky to pull off without a server process to coordinate things. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] please help about utf-8
hi: thank for any reply! I'm using tomcat4 and post html form data with utf-8 encoding. I has set the jvm argument like : -DSQLite.encoding=utf-8, but i can't insert the utf-8 string to the table. I do some test and I found that I must transform string to iso8859-1 firstly. Would you tell me how to use sqlite jdbc driver to handle utf-8 string? forgive me, my english is poor. thanks in advance!
Re: [sqlite] Improving concurrency in SQLite
This is more rambling than anything else. I hope I'm reasonably coherent before coffee... Instead of keeping an "undo" journal- consider using a "changes" journal-- each writer could use it's own journal then only at commit would each worker actually start making changes to the database. DEFER_WRITES isn't necessary; the changes journal could have information in it diverting certain tables/rows/pages for THIS WRITER ONLY so that selects can get information about earlier inserts/deletes/updates. No other writer needs access to this information until committing. Note that these changes will contain not only the values they are storing, but some kind of hash of the original value. This is used to detect whether or not two processes will UPDATE the same value. To make this simple, COMMIT should be allowed to fail (with no changes made). ROWID needs special assistance- There wasn't any mention of this on concurrency.html but what happens if two writers INSERT at the same time? Would they get the same ROWID? What if the user calls last_insert_rowid() or whatever? Ideally, ROWID should be allocated as soon as we SEE the INSERT. It shouldn't matter much if we just lost a ROWID-- they're relatively cheap on 32-bit platforms... If the database format changes, they're even cheaper on 64-bit platforms... On Sat, 2003-11-22 at 09:09, D. Richard Hipp wrote: > Feedback is requested on the paper found at: > > http://www.sqlite.org/concurrency.html > > Your analysis and ideas will help to make SQLite a > better database engine. Thanks. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Improving concurrency in SQLite
> Feedback is requested on the paper found at: > > http://www.sqlite.org/concurrency.html > > Your analysis and ideas will help to make SQLite a > better database engine. Thanks. In my somehwhat limited knowledge of database design, I'd advocate all the options in section 4 (ie transaction options), and also optional (eg. :dir:) directory based databases. Cheers! -- Richard Heyes - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Improving concurrency in SQLite
Feedback is requested on the paper found at: http://www.sqlite.org/concurrency.html Your analysis and ideas will help to make SQLite a better database engine. Thanks. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
