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 50000 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 10000: 2 wallclock secs ( 1.19 usr + 0.50 sys = 1.69 CPU) @
5927.68/s (n=10000)
timethis 50000: 8 wallclock secs ( 5.13 usr + 2.88 sys = 8.00 CPU) @
6250.00/s (n=50000)
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...