Howdy,
"Jay Macaulay" <[EMAIL PROTECTED]>
23/10/2003 05:55 AM
To: <[EMAIL PROTECTED]>
cc:
Subject: [sqlite] Problem with LIMIT and transactions? Or is it just me
> I am running into a real odd problem that I hope someone can explain. I
> have a simple query:
> BEGIN;
> SELECT * FROM users WHERE uid >= 1 LIMIT 10;
> COMMIT;
> Simple enough. No reason to use a transaction, but I use them for
another
> reason, actually. The problem I run into is: If I execute the above
> statement 3 times, it works fine. It takes 70 ms to execute the select
> statement. On the fourth time of execution the select statement takes
over
> 5 seconds to execute!! I can execute the statement again after the 5
> seconds, and it's back to a 70 ms execution. Then after other time I
> execute this statement it takes 5 seconds.
This does seem difficult to explain. Is it possible that another process
or thread is accessing the database occasionally (thus locking this
"writer" out for a period?). Apart from that, you probably have to work
through a process of elimination. Does it still happen without the LIMIT
clause? Does it still happen if you take the transaction away? Is there
anything else going on on the machine at the time? How are you running
your test? How are you obtaining your timings? What's the schema of the
database? Is there an index?
Hopefully the answers to some of these questions will bring you closer to
a conclusion.
Benjamin.
