Re: [sqlite] Concurrency, MVCC

2004-04-14 Thread Mark D. Anderson
Wednesday, April 14, 2004, 1:16:54 AM, Andrew Piskorski wrote: > as far as I can tell, it seems to be describing a system with > the usual Oracle/PostgreSQL MVCC semantics, EXCEPT of course that > Currie proposes that each Write transaction must take a lock on the > database as a whole. Well, i

Re: [sqlite] Concurrency Proposal

2004-04-14 Thread Mark D. Anderson
On Wed, 31 Mar 2004 12:15:36 +1000, [EMAIL PROTECTED] said: > G'day, > [snip of Ben's pseudo-code] Just to check my understanding: the suggestion here is to reduce reader-writer conflict windows by buffering of writes. The writer acquires a read lock at the start of the transaction, and

Re: [sqlite] differences between 2.8.11 and 2.8.12

2004-04-14 Thread Lloyd thomas
Ok, I just tried the query using the sqlite command tool 2.8.12 and I get no results, whereas if I use PHP5 with sqlite 2.8.11 I get the expected results. Can some one tell me why this is or what I am doing wrong? - Original Message - I have a query which successfully runs on

Re: [sqlite] FreeBSD and SQLite

2004-04-14 Thread Greg Miller
Al Rider wrote: Is there anyone successfully running SQLite on a FreeBSD machine? If so, would you email me and give me some help with it. I've never used it on anything else. The instructions assume that make is actually GNU make, which is almost certainly not the case on a non-Linux machine.

[sqlite] FreeBSD and SQLite

2004-04-14 Thread Markus Hoenicka
Al Rider writes: > > I tried to compile and install SQLite without any success. > You might want to be a tad more specific at this point. What kind of error messages did you get? > Is there anyone successfully running SQLite on a FreeBSD machine? If so, > would you email me and give me

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread D. Richard Hipp
Puneet Kishor wrote: To Richard -- So in my test, SQLite is a little faster. Perhaps the difference might be in a bad implementation of the SQLite bindings for Perl, or perhaps the "mysql" command-line shell is less than optimal. You perhaps meant "PHP" instead of "Perl" as that is what hannes

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread Puneet Kishor
Hannes Roth wrote: Hi. I don't want to publish that table I used to make that benchmark. So I created some random data: http://dl.magiccards.info/speedtest.tar.bz2 $db = sqlite_open("speedtest.sqlite"); $result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE '%a%'");

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread D. Richard Hipp
rich coco wrote: i am curious as to the discrepancies in 'sys' time between sQLite and mySQL (9.12s .vs 1.96s as reported below). SQLite lacks a persistent server, so it has to flush its cache and reread every page of the database for each of the 100 queries. This takes time. MySQL, on the other

RE: [sqlite] MySQL / SQLite

2004-04-14 Thread basil . thomas
I totally agree... Seems like when users say SQLite is slower than "xyz...", they are using a high level driver based interface instead of using a "c" based driver program to really test what SQLite is doing. I have written tests at the "c" level for both MySQL and SQLite and SQLite is generally

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread Jimmy Lantz
At 19:32 2004-04-14, you wrote: Yes, but are mysql_query and sqlite_query really doing the same thing? It is quite possible that mysql_query doesn't actually perform the complete query, while sqlite_query does. I think a better test would be to do the query and then step through all of the

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread D. Richard Hipp
Hannes Roth wrote: I don't want to publish that table I used to make that benchmark. So I created some random data: http://dl.magiccards.info/speedtest.tar.bz2 $db = sqlite_open("speedtest.sqlite"); $result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE '%a%'");

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread Will Leshner
Hannes Roth wrote: $db = sqlite_open("speedtest.sqlite"); $result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE '%a%'"); include("MySQL.php"); $erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'"); MySQL: 0.13727307319641 SQLite: 0.17734694480896 Yes, but are

[sqlite] FreeBSD and SQLite

2004-04-14 Thread Al Rider
I do website design for clubs, etc., with a lot of custom, CM php scripts. SQLite is ideally suited for many of my scripts; but, unfortunately one of the sites is hosted on a FreeBSD based server. Most of my designs are Linux; but, I want to keep the designs portable. I tried to compile and

Re: [sqlite] Adding SQL commands

2004-04-14 Thread D. Richard Hipp
[EMAIL PROTECTED] wrote: Great...can you tell me if there will be any increased technical specs??? Ver 3.0 will have a 64-bit ROWID, what about: - page size 512 to 64K selectable at compile-time. Default: 1K - max table rows 2^64 - max database size 2^32 pages. The current

RE: [sqlite] Adding SQL commands

2004-04-14 Thread basil . thomas
Great...can you tell me if there will be any increased technical specs??? Ver 3.0 will have a 64-bit ROWID, what about: - page size - max table rows - max database size - max blob size - any other tech limitations info... -Original Message- From:

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread D. Richard Hipp
Hannes Roth wrote: Ok: -18 fields, all strings or numbers. -No indices. -The query I used is "SELECT * FROM table WHERE field1 LIKE '%foo%'. This is similar to Test-5 at http://www.sqlite.org/speed.html In Test-5, SQLite is 30% faster than MySQL. I do not know what the difference might be from

Re: [sqlite] Adding SQL commands

2004-04-14 Thread D. Richard Hipp
[EMAIL PROTECTED] wrote: > [I]f version 3.0 is a foundation move to implement enhanced language > functionality later,... That is a correct assessment. SQLite version 3.0.0 is an enhancement to the foundation. SQL Langauge enhancements can be made later and in a backwards-compatible way. The

Re: [sqlite] Concurrency, MVCC

2004-04-14 Thread Doug Currie
> D. Richard Hipp wrote: >> >> My thoughts on BlueSky have been added to the wiki page: >>http://www.sqlite.org/cvstrac/wiki?p=BlueSky I added some responses; I do not agree with Richard's concerns about Shadow Paging, and I corrected some mistaken conclusions. I apologize if my paper was

RE: [sqlite] Adding SQL commands

2004-04-14 Thread basil . thomas
Yes...It would be great if SQLite had control-flow statements and variables just like Transact-SQL(MS/Sybase) as it would allow one to put all the data manipulation into one script and run it like a stored procedure... I do like the fine control that SQLite gives my application but I also think

RE: [sqlite] Effectiveness of PRAGMA integrity_check;

2004-04-14 Thread Liz Steel
Hello! I am trying to do a similar sort of thing with my database. The only way I've found to fairly reliably create a corrupt database file is to pull the battery out of my laptop whilst my application is accessing the database. I haven't used the "PRAGMA integrity_check;" command, but I will

Re: [sqlite] Concurrency, MVCC

2004-04-14 Thread D. Richard Hipp
D. Richard Hipp wrote: My thoughts on BlueSky have been added to the wiki page: http://www.sqlite.org/wiki?p=BlueSky That URL should have been: http://www.sqlite.org/cvstrac/wiki?p=BlueSky Left out the "cvstrac". Sorry for the confusion. -- D. Richard Hipp -- [EMAIL PROTECTED] --

Re: [sqlite] Effectiveness of PRAGMA integrity_check;

2004-04-14 Thread D. Richard Hipp
[EMAIL PROTECTED] wrote: I'm trying to write some defensive code that is able to recover from database corruption. The idea is that if a disk fails and a database becomes corrupt it can be detected and synchronised from a backup copy. To this end, I've just been trying to write a function that

Re: [sqlite] Concurrency, MVCC

2004-04-14 Thread D. Richard Hipp
Andrew Piskorski wrote: How feasible would it be to add support for higher concurrency to SQLite, especially via MVCC? My thoughts on BlueSky have been added to the wiki page: http://www.sqlite.org/wiki?p=BlueSky The current plan for version 3.0 is as follows: * Support for a modification

RE: [sqlite] MySQL / SQLite

2004-04-14 Thread KL Chin
Dear D. Richard Hipp, I had some strange result on some PCs, a) On my notebook (P3 899, 256Mbytes, WinXP) when do insert a records to a blank table, it > 100% slower as compare to other. (< 3000 rows). b) On one of PC (Celeron 1.7G, 256MBytes WinXP), when do search. it

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread D. Richard Hipp
Hannes Roth wrote: 2. Why is SQLite twice as fast when using a small database (<3000 rows) and twice as slow when using a large database (>8000 rows)? The speed comparison at http://www.sqlite.org/speed.html uses tables with 25000 rows and is twice as fast as MySQL. I don't know why yours is

Re: [sqlite] MySQL / SQLite

2004-04-14 Thread Elmar Haneke
Hannes Roth schrieb: 1. Why is the SQLite database much much bigger than the MySQL table and the raw csv file? I didn't use any index and vacuumed all the time ;) SQLite does store everything as string, therefore the DB cannot be significantly small than the CVS-file. MySQL does store

[sqlite] Ticket 575

2004-04-14 Thread Paul Smith
Is there any progress on this ticket (temporary file storage method problem)? If not, can anyone suggest any workarounds? I've just discovered here that it looks like we're getting really bad performance hits on a Windows machine with temporary files when people use certain virus scanners.