Re: [sqlite] Exclusive transaction severly slows down the queries increasingly in time
Slawomir Nowaczyk wrote: On Wed, 17 Aug 2005 17:27:17 -0500 Puneet Kishor <[EMAIL PROTECTED]> wrote: #> Maintaining the integrity of state is important when modifying. #> While reading, why would you want to read something that is #> possibly stale. I don't think "stale" is a good word. SELECT amount as a1 FROM money WHERE name = me; SELECT amount as a2 FROM money WHERE name = you; Now, how much money do we have together? a1 + a2? Yes, but only if the above statements were wrapped in a transaction... otherwise, somebody might have executed "BEGIN; UPDATE amount = amount + 1000 WHERE name = me; UPDATE amount = amount - 1000 WHERE name = you; COMMIT" in-between those two selects. Integrity can be important :) I actually got your point the first time ;-). And I certainly don't want to make this into a long drawn out thread a la "are threads safe or not" ;-) However, the very example above shows why integrity is important. Without the transaction, I will see exactly how much money you and I have at any given moment on doing a SELECT. With the transaction, the latest UPDATE will not be reflected unless it gets done before my SELECT transaction started. A transaction is important, just not on a statement that doesn't modify data, unless the application design specifically asks for consistent, but possibly stale (hence, possibly inaccurate) data. On the other hand, a transaction for the UPDATE statements is definitely essential, else you will be richer by a 1000 and I will be poorer. Not good. -- Puneet Kishor
Re: [sqlite] Exclusive transaction severly slows down the queries increasingly in time
On Wed, 17 Aug 2005 17:27:17 -0500 Puneet Kishor <[EMAIL PROTECTED]> wrote: #> Maintaining the integrity of state is important when modifying. #> While reading, why would you want to read something that is #> possibly stale. I don't think "stale" is a good word. SELECT amount as a1 FROM money WHERE name = me; SELECT amount as a2 FROM money WHERE name = you; Now, how much money do we have together? a1 + a2? Yes, but only if the above statements were wrapped in a transaction... otherwise, somebody might have executed "BEGIN; UPDATE amount = amount + 1000 WHERE name = me; UPDATE amount = amount - 1000 WHERE name = you; COMMIT" in-between those two selects. Integrity can be important :) -- Best wishes, Slawomir Nowaczyk ( [EMAIL PROTECTED] ) User Friendly: Supplied with a full color manual.
Re: [sqlite] Exclusive transaction severly slows down the queries increasingly in time
Slawomir Nowaczyk wrote: On Wed, 17 Aug 2005 08:35:08 -0500 Puneet Kishor <[EMAIL PROTECTED]> wrote: #> Why are you wrapping a SELECT statement inside a transaction? What #> purpose does that serve? Transactions are meant for statements that #> modify the table, Not true. Of course, wrapping a single SQL statement in a transaction is a bit pointless, but wrapping multiple selects is *not*. It prevents you from seeing inconsistent state of the database. For example, if you read data from table1, somebody else modifies table1 *and* table2, then you read data from table2, you can be in trouble. Transaction prevents this. well, it is a matter of perspective. While the Transaction prevents you from reading the modified data, be aware that you are no longer reading "fresh" data. Maintaining the integrity of state is important when modifying. While reading, why would you want to read something that is possibly stale. Also, it usually best to avoid updating a production database and instead, doing a batch update, so that "born on date" is guaranteed. Anyway... #> > If I use simple beginTransation, the for cycle ends in 8 seconds #> > which is a good time. #> Even 8 seconds is way too much for a simple SELECT. In fact, 8 #> seconds is way too much even for an INSERT. There is something else #> going on here. I believe he meant 8 seconds for a 1000 selects... which sounds reasonable to me. Doesn't seem reasonable to me. A simple test on a table with more than 230,000 records selecting rows randomly (with the help of Perl DBI, DBD::SQLite, and Benchmark), I had to do 5 random selects to reach 8 seconds -- timethis 1000: 2 wallclock secs ( 0.13 usr + 0.06 sys = 0.19 CPU) @ 5319.15/s (n=1000) (warning: too few iterations for a reliable count) timethis 1: 2 wallclock secs ( 1.19 usr + 0.50 sys = 1.69 CPU) @ 5927.68/s (n=1) timethis 5: 8 wallclock secs ( 5.13 usr + 2.88 sys = 8.00 CPU) @ 6250.00/s (n=5) Of course, unless we see the OP's complete SELECT statement, and whether or not it is using INDEXes properly, it is not a correct comparision. Still, 8 seconds...
Re: [sqlite] Exclusive transaction severly slows down the queries increasingly in time
On Wed, 17 Aug 2005 08:35:08 -0500 Puneet Kishor <[EMAIL PROTECTED]> wrote: #> Why are you wrapping a SELECT statement inside a transaction? What #> purpose does that serve? Transactions are meant for statements that #> modify the table, Not true. Of course, wrapping a single SQL statement in a transaction is a bit pointless, but wrapping multiple selects is *not*. It prevents you from seeing inconsistent state of the database. For example, if you read data from table1, somebody else modifies table1 *and* table2, then you read data from table2, you can be in trouble. Transaction prevents this. #> > If I use simple beginTransation, the for cycle ends in 8 seconds #> > which is a good time. #> Even 8 seconds is way too much for a simple SELECT. In fact, 8 #> seconds is way too much even for an INSERT. There is something else #> going on here. I believe he meant 8 seconds for a 1000 selects... which sounds reasonable to me. -- Best wishes, Slawomir Nowaczyk ( [EMAIL PROTECTED] ) Do not meddle in the affairs of Unix, for it is subtle and quick to core dump.
Re: [sqlite] Exclusive transaction severly slows down the queries increasingly in time
Others more knowledgeable will probably offer better advice. I have a comment and a question instead -- On Aug 17, 2005, at 4:13 AM, Fabian Matyas wrote: Hi, I use SQLite 3.0.8 with Quicklite 1.5.0 as wrapper for it on Mac OS X 10.3. I made a simple test which creates a table with 1 rows. The table contains an indexed column named 'Path'. This is the query I ran: for i=0 to 1000 do begin begin exclusive/immediate transaction select where Path= end transaction end Why are you wrapping a SELECT statement inside a transaction? What purpose does that serve? Transactions are meant for statements that modify the table, so that, in case of an inconsistency, you can roll back to the previous consistent state. However, you are not modifying anything at all. Why not just do a SELECT columns FROM table WHERE path = and be done. If I use simple beginTransation, the for cycle ends in 8 seconds which is a good time. Even 8 seconds is way too much for a simple SELECT. In fact, 8 seconds is way too much even for an INSERT. There is something else going on here. -- Puneet Kishor
[sqlite] Exclusive transaction severly slows down the queries increasingly in time
Hi, I use SQLite 3.0.8 with Quicklite 1.5.0 as wrapper for it on Mac OS X 10.3. I made a simple test which creates a table with 1 rows. The table contains an indexed column named 'Path'. This is the query I ran: for i=0 to 1000 do begin begin exclusive/immediate transaction select where Path= end transaction end If I use simple beginTransation, the for cycle ends in 8 seconds which is a good time. The problem: If I use beginImmediateTransaction or beginExclusiveTransaction the for cycle ends in 9 minutes. The first 10-20 queries run ok, but after that, the beginExclusive/ImmediateTransaction instructions eat more and more time increasingly. At the end of the , one single select lasts between 1/2 and 1 second which IS VERY MUCH. If you'll argue that I should include the whole cycle in a transation, the reason why I don't do that is that my application executes some queries on the table from time to time. Please help, Fabian __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
